How to use AI to write Excel and Google Sheets formulas that actually work

Writing a formula with several nested IFs, a VLOOKUP that won't return what you expect, or a SUMIFS with three conditions can eat up an afternoon. AI assistants are very good at turning a plain-English description into a formula — as long as you describe your data properly. This guide covers how to ask, how to avoid the classic regional-settings trap, and how to check the formula does what you think.

#Where to ask

• Any chatbot (ChatGPT, Gemini, Claude, Copilot): describe the data and it returns a formula to copy.
• AI built into the spreadsheet: Microsoft offers Copilot in Excel and Google offers Gemini in Sheets, depending on your plan. Their advantage is that they can see your actual columns.

#How to describe your data

The most common mistake is asking for "a formula to look up the price" without saying where anything is. Include:

1. Which columns and rows hold the data, with their headers.
2. What you want to get and in which cell.
3. An example with the expected result.
4. Which program: Excel, Google Sheets, and your version if relevant.
5. What to do with odd cases: blank cells or values not found.

For example:

I'm using Excel. On the "Orders" sheet: column A = product code, B = quantity. On the "Prices" sheet: column A = code, column C = unit price. In column C of "Orders" I want the line total (quantity × price). If the code isn't in "Prices", show "No price". Example: code P-102, quantity 3, price 4.50 → 13.5.

You should get something like:

=IFERROR(B2*XLOOKUP(A2,Prices!A:A,Prices!C:C),"No price")

#The regional settings trap

If you share spreadsheets internationally, this one catches people out:

• Function names are translated in non-English versions of Excel: IF becomes SI in Spanish, WENN in German.
• The argument separator changes too: in regions where the comma is the decimal separator, Excel uses semicolons (;) instead of commas.

If a colleague's formula throws an error on your machine — or yours on theirs — check the separator and language first. Tell the AI which version you're using if it isn't English with comma separators.

#How to test a formula

A formula that doesn't error isn't necessarily right. Before applying it to thousands of rows:

1. Test with known cases. Pick three or four rows whose answer you can work out by hand.
2. Test edge cases: blank cells, a missing code, negative values, or text where numbers should be.
3. Ask for an explanation, argument by argument: "Explain what each part of this formula does."
4. Check references: should they be absolute ($A$2) so they don't shift when you drag the formula?
5. Check full ranges: if you use A2:A100 but have 150 rows, data will be missed.

#Useful requests beyond a single formula

• Debugging: "This formula returns #N/A in some rows: [formula]. Here are sample rows: [...]. Why?"
• Simplifying: "This formula has five nested IFs. Is there a more readable way?" Functions such as IFS or XLOOKUP often simplify things a lot.
• Conditional formatting: "Highlight rows red when the due date has passed."
• Pivot tables: ask for steps to summarise sales by month and salesperson.
• Data cleaning: split first and last names, trim spaces, standardise date formats.

#Privacy

If your sheets contain customer data, salaries or other sensitive information, don't paste real rows into an unapproved chatbot. Describe the structure with made-up data — the formula will work the same on your real data.

#Common mistakes

#The bottom line

AI turns "I want to add up Lopez's sales for March" into a working formula in seconds — if you describe the columns, give an example and name your program. Then test it with known cases before trusting it. If spreadsheets are part of a repetitive routine, the next step is to automate it with AI.