Somewhere between a nested IF and a VLOOKUP that returns #N/A for no apparent reason, most of us have wished our spreadsheet would just read our minds. It cannot — but AI comes remarkably close. Once you learn how to use AI for Excel formulas (and Google Sheets), you can describe what you want in plain language and get a working formula back in seconds.
Whether you are budgeting in Berlin, analysing survey data in Lyon or tracking stock in Rotterdam, this guide shows you exactly how to do it: the right tools, copy-paste prompts, and the tricks that save hours every single week.
What AI can do for your spreadsheets
Today’s AI assistants handle four spreadsheet jobs brilliantly:
- Write formulas from scratch — describe the result you want, get the formula.
- Explain existing formulas — paste a cryptic formula and receive a plain-language breakdown.
- Debug errors — paste the broken formula plus the error message, get the fix.
- Translate between Excel and Google Sheets — convert syntax and suggest equivalents.
The common thread: you stop memorising syntax and start describing logic. That is a genuine superpower for anyone who uses spreadsheets regularly but never trained as an analyst.
The best ways to use AI for Excel and Google Sheets formulas
Chatbots: ChatGPT, Claude and Gemini
The most flexible option — and free to start. Describe your data layout, paste a few example rows, and ask for the formula. Because you can iterate conversationally (“now make it ignore blank cells”), chatbots beat one-shot generators for anything non-trivial.
Microsoft Copilot in Excel
If your employer or university provides Microsoft 365 with Copilot, you can request formulas, explanations and even full analyses directly inside Excel. It sees your actual data, so there is no copy-pasting — convenient, though it requires a paid plan.
Gemini in Google Sheets
Google’s “Help me organize” sidebar and Gemini side panel generate formulas, build tables and summarise data inside Sheets. A natural fit if your team already lives in Google Workspace.
Dedicated AI formula generators
Specialised tools such as Formula Bot focus on one job: turning plain-language requests into spreadsheet formulas. They are handy for quick one-off conversions, though a well-written chatbot prompt usually matches them.
Step by step: getting a working formula from AI
Step 1: Describe the goal, not the formula
Write what you want as if explaining it to a colleague: “For each row, if the order total is above 100 and the customer is in Germany, apply a 10% discount; otherwise show the full price.” Clear logic in, correct formula out.
Step 2: Show the AI your layout
Always include your column structure and a couple of example rows. “Column A: customer name, column B: country, column C: order total, starting row 2” prevents the AI from guessing wrong ranges — the most common source of broken formulas.
Step 3: Test before you trust
Paste the formula into a copy of your sheet and check it against rows where you already know the right answer. AI formulas are usually correct, but “usually” is not “always” — especially with dates and lookup ranges.
Step 4: Ask for an explanation
“Explain this formula step by step” turns every answer into a mini-lesson. Within weeks you will find yourself needing the AI less, because you genuinely understand what each function does.
Step 5: Save what works
Keep a personal formula cookbook — a note with your best prompts and the formulas they produced. Future you will be grateful. Our beginner’s guide to prompt engineering shows how small prompt tweaks produce reliably better answers from any AI tool.
Copy-paste prompt templates with examples
Adapt these to your own columns:
- Look up a value: “Write an Excel formula that finds the value in column A within the range D2:E100 and returns the matching value from column E. My data starts in row 2.”
- Multiple conditions: “Write a formula that returns ‘Priority’ if column B is ‘Urgent’ and column C is greater than 1000, ‘Standard’ if column B is ‘Normal’, and ‘Review’ otherwise.”
- Extract text: “Write a Google Sheets formula that extracts the domain from email addresses in column A (e.g. marie@example.fr becomes example.fr).”
- Work with dates: “Write a formula that calculates the number of working days between the dates in columns A and B, excluding weekends.”
- Sum conditionally: “Write a formula that sums column D where column B equals ‘France’ and column C is in Q1.”
- Debug this: “This formula returns #VALUE!: [paste formula]. My data: [describe layout]. What is wrong and how do I fix it?”
Excel vs Google Sheets: what to watch for
- Function names differ slightly. XLOOKUP exists in modern Excel but not in Google Sheets (use VLOOKUP or INDEX/MATCH there). Always tell the AI which app you use.
- Decimal and list separators. Many European locales use semicolons instead of commas in formulas. Mention your locale — “I use semicolons as separators” — and the AI will adapt.
- Array behaviour. Google Sheets often needs ARRAYFORMULA where modern Excel spills automatically. If a formula works in one app but not the other, ask the AI to convert it.
Debugging formulas with AI: a practical example
Say your VLOOKUP keeps returning #N/A even though the value clearly exists. Paste this into your chatbot: “My VLOOKUP in cell F2 returns #N/A. Formula: =VLOOKUP(E2,A2:D100,4,FALSE). Column A contains product codes stored as text; column E contains numbers. What is wrong?” The AI will likely spot the classic text-versus-number mismatch and suggest wrapping the lookup in TEXT() or VALUE() — a diagnosis that could otherwise cost you an hour of frustration.
The debugging prompt that works every time: formula + error message + data description + expected result. Give the AI all four and it rarely misses.
A word on privacy: keep real data out of public AI tools
Spreadsheets often hold the most sensitive data in a business: salaries, customer lists, financials. Under the GDPR, pasting personal data into a public chatbot can become a compliance problem. The safe workflow is simple: anonymise first — replace names with “Person A”, round the numbers, or use a few representative dummy rows. You will get exactly the same formula without exposing anything real. If your organisation offers an approved enterprise AI plan with proper data processing terms, use that for genuine data.
Frequently asked questions
Can AI write Excel formulas for free?
Yes. The free tiers of ChatGPT, Claude and Gemini all write and debug spreadsheet formulas well. Paid built-in assistants like Copilot add convenience rather than capability you cannot get for free.
Which is better for AI help: Excel or Google Sheets?
Both work well. Excel offers deeper AI integration through Copilot on paid plans; Google Sheets pairs naturally with Gemini. For chatbot-assisted formulas they are equally good — just tell the AI which one you use.
Why does the AI’s formula sometimes not work?
Usually one of three things: wrong cell ranges because the layout was not described, locale separators (commas versus semicolons), or function differences between Excel and Sheets. Paste the error back to the AI with details and it will fix it.
Is it safe to paste my spreadsheet into a chatbot?
Only if it contains no personal or confidential data. Anonymise names, emails and sensitive figures first — or use your organisation’s approved enterprise AI tool.
Will AI replace learning Excel?
No — it changes what is worth learning. Memorising syntax matters less; understanding logic, data structure and how to verify results matters more. The people who benefit most from AI formulas are those who understand enough to check the answers.
The bottom line: AI will not do your spreadsheet thinking for you, but it removes the most painful part: translating clear logic into cryptic syntax. Describe what you want, show the layout, test the result and keep your data anonymised. Do that consistently, and the hours you used to lose to formula wrestling turn into minutes — week after week.
2 thoughts on “How to Use AI for Excel and Google Sheets Formulas”