You know what you want your spreadsheet to do, like “add up sales for each month” or “flag overdue invoices,” but the formula is a mystery. AI can write it for you in plain English, explain how it works and even fix errors.
This works for both Microsoft Excel and Google Sheets, using free AI tools.
The Simple Method (Works With Any Free AI)
Open a free AI assistant like ChatGPT, Claude or Gemini and follow these steps.
Step 1: Describe your sheet
Tell the AI which app you use and what’s in each column. For example:
“I’m using Google Sheets. Column A has dates, column B has customer names, column C has order amounts in dollars. Data is in rows 2 to 500.”
Step 2: Say what you want, in plain English
“Write a formula that adds up all orders from March 2026.”
Step 3: Ask for an explanation
“Explain what each part of this formula does, simply.” This helps you learn and spot mistakes.
Step 4: Test it
Paste the formula into an empty cell and check the result against a few rows you can calculate yourself.
Ready-to-Use Prompts
- Totals by category: “Sum column C only where column B says ‘Online’.”
- Lookups: “Find the price for the product ID in A2 from my ‘Prices’ sheet, where IDs are in column A and prices in column B.”
- Dates: “Show ‘Overdue’ if the due date in column D is before today and column E isn’t ‘Paid’.”
- Cleaning data: “Split full names in column A into first and last name.”
- Counting: “Count how many unique customers are in column B.”
- Percentages: “Calculate the percentage change between January sales in B2 and February sales in C2.”
Fix a Formula That Isn’t Working
Copy the formula and the error you see (like #N/A, #VALUE! or #REF!) and ask:
“This Excel formula returns #N/A: [paste formula]. Column A has product IDs and the lookup table is on Sheet2. What’s wrong and how do I fix it?”
AI is very good at spotting wrong ranges, missing quotes and mismatched data types.
Built-In AI Assistants
Both Microsoft and Google also offer AI assistants inside their spreadsheet apps:
- Copilot in Excel comes with certain paid Microsoft 365 plans.
- Gemini in Google Sheets comes with certain paid Google AI and Google Workspace plans.
These can analyze your actual data directly. If you don’t pay for them, the free chatbot method above works just as well for formulas.
Note: Features and policies may vary by plan, region and app version, and can change at any time. Check the official help center for the latest information.
Tips for Accurate Results
- Mention Excel or Google Sheets. Some functions differ between them.
- Give exact column letters and row ranges. Vague descriptions lead to wrong references.
- Share a few sample rows using fake data, so the AI understands your format.
- Don’t paste sensitive data like customer emails, salaries or account numbers into public AI tools. Use made-up examples instead.
- Always test the formula on a few rows before trusting it across thousands.
Frequently Asked Questions
Can ChatGPT write Excel formulas?
Yes. Describe your columns and what you want, and it will write the formula and explain it. It also helps with Google Sheets, which uses very similar functions.
Do I need a paid AI plan?
No. Free versions of ChatGPT, Claude and Gemini handle formulas well. Paid built-in assistants are mainly useful for analyzing whole sheets directly.
Can AI make mistakes with formulas?
Yes, especially with wrong cell ranges. That’s why testing on a few rows matters.
Can AI create charts and pivot tables too?
Free chatbots can give you step-by-step instructions. Built-in assistants like Copilot in Excel can create some of them for you directly.
The Bottom Line
Describe your columns, say what you want in plain English, ask for an explanation, and test the result. With free AI tools, you’ll never need to search for formula syntax again. Running a small business? See more time-savers in AI tools for small business owners.
Related Guides
- Google Apps Script: 25 Things You Can Automate for Free (Beginner’s Guide 2026)
- How to Use AI to Write Emails in Gmail (Step by Step)
- How to Plan a Trip With AI: Prompts, Tips and What to Double-Check
- Gamma AI: How to Make a Presentation in Minutes With AI (2026)
Explore more in our AI Productivity Tools hub.
Disclaimer: This article is for general information only and is based on details available at the time of writing. Prices, plans, features, release dates, policies and availability can change at any time and may vary by country. Please verify the latest information on the official website of the company or product mentioned before making any decision or purchase. Next DigiTech is an independent website and is not affiliated with or endorsed by the companies mentioned; all trademarks belong to their respective owners. We are not responsible for any loss or decisions made based on this content.
