How to Use AI to Write Excel and Google Sheets Formulas

You know what you want the spreadsheet to do. “Add up sales for the North region this year.” The hard part is turning that sentence into =SUMIFS(...) with the right ranges, quotes and brackets. This is one of the things AI does best, and it takes about a minute once you know how to ask.

Why plain-English requests go wrong

If you type “write a formula to total sales by region”, the AI has to guess where your data is, what the columns are called, and whether you use Excel or Google Sheets. It will guess, and the formula will look right but point at the wrong cells.

The fix: describe your sheet the way you’d describe it to a colleague over the phone.

The formula prompt template

I'm using [Excel 365 / Google Sheets].
My data is on a sheet called [Sales], rows 2 to 500, with headers in row 1:
A = Date, B = Region, C = Product, D = Amount.

I want a formula in [cell F2] that [plain-English goal].

Give me the formula, then explain each part in one line.

Two extra lines that help a lot:

• Paste 3–5 sample rows. Copy them straight from the sheet. The AI can then see whether dates are real dates or text, and whether amounts have currency symbols.
• Say what result you expect for one example. “For the sample rows, the answer should be 1,250.”

Worked examples

All examples below use the layout above: Date in A, Region in B, Product in C, Amount in D, rows 2 to 500.

Total with conditions: SUMIFS

Ask: “Total Amount for the North region for dates on or after 1 January 2026.”

=SUMIFS(D2:D500, B2:B500, "North", A2:A500, ">="&DATE(2026,1,1))

SUMIFS adds the numbers in D where every condition is true. The ">="&DATE(...) part joins the comparison sign to a real date, which avoids problems with how dates are written in different countries. This works the same in Excel and Google Sheets.

Look up a value: XLOOKUP

Ask: “On my Orders sheet, column A has product codes. Find each code on the Products sheet (codes in column A, prices in column C) and return the price. Show ‘Not found’ if it’s missing.”

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

XLOOKUP works in Excel 365, Excel 2021 and later, and Google Sheets. On older Excel versions, the AI should give you the INDEX and MATCH version instead, so always say which version you have.

Show only matching rows: FILTER

Ask: “Show every row where Amount is over 1,000.”

=FILTER(A2:D500, D2:D500>1000, "No results")

The results “spill” into the cells below and to the right, so leave that space empty. FILTER is available in Excel 365, Excel 2021 and later, and Google Sheets.

Summaries in Google Sheets: QUERY

Google Sheets has a powerful function Excel doesn’t: QUERY, which uses a SQL-like language.

Ask: “Total Amount by Region, largest first.”

=QUERY(A1:D500, "select B, sum(D) where B is not null group by B order by sum(D) desc", 1)

The final 1 tells Sheets the first row has headers. QUERY is easy to get slightly wrong by hand, so it’s a good one to ask AI for.

Clean up text

Ask (Excel 365): “Column E has email addresses. Return just the domain, after the @.”

=TEXTAFTER(E2, "@")

Ask (Google Sheets): same request.

=REGEXEXTRACT(E2, "@(.+)$")

The same task often has a different best answer in each app. This is exactly why your prompt should name the app.

When the formula breaks

Don’t start a new chat. Paste the problem back:

This formula returns #VALUE! in Excel 365:
=SUMIFS(D2:D500, B2:B500, "North", A2:A500, ">=2026-01-01")
Here are 3 sample rows: [paste]. What's wrong, and what's the fix?

Common causes the AI can usually spot:

• Dates stored as text. They look like dates but the sheet treats them as words.
• Ranges of different sizes. D2:D500 and B2:B400 won’t work together in SUMIFS.
• Commas vs semicolons. In many European locales, Excel uses ; between arguments. If every formula you paste fails, this is likely the reason.
• Missing functions. Older Excel versions don’t have XLOOKUP, FILTER or TEXTAFTER.

Check the formula before you trust it

A formula that returns a number isn’t necessarily returning the right number. Quick checks:

1. Test on a small case you can do by hand. Filter the sheet to North and 2026, select the Amount cells, and compare the total shown in the status bar with the formula.
2. Ask the AI to explain it. If the explanation doesn’t match what you wanted, the formula doesn’t either.
3. Check the edges. Blank rows, zero values, and the first and last rows of the range.

The same habit of checking AI output applies everywhere. See our five-minute fact-checking routine.

AI built into spreadsheets

Both Microsoft and Google have added AI assistants directly inside Excel and Google Sheets on some plans, which can suggest formulas without copying and pasting. They follow the same rule: the more precisely you describe the columns and the result you want, the better the answer. If you don’t have access to those, a regular chat assistant works fine with the template above.

Key takeaways

• Describe your sheet precisely: app and version, sheet name, column letters, row range.
• Paste a few sample rows and say what result you expect.
• Ask for an explanation of each part, so you learn as you go.
• When a formula breaks, paste the error and the formula back, rather than starting over.
• Always test the result against a case you can check by hand.

Next, try turning meeting notes into action items with AI, another quick win for everyday work.