Using ChatGPT, Claude or Gemini for Excel and Google Sheets Formulas

ChatGPT, Claude and Gemini can all write spreadsheet formulas from a plain-English description. They know the syntax of common Excel and Google Sheets functions well, and they are good at explaining a formula someone else wrote.

What they cannot know is how your sheet is laid out. Most wrong AI formulas are correct formulas pointed at the wrong columns, or written for a function your version does not have. This guide shows the prompt pattern that avoids that, eight formulas we tested on a sample sheet, and a quick way to check any formula before you rely on it.

The prompt pattern

I use [Excel 365 / Excel 2019 / Google Sheets].
Sheet "Orders", headers in row 1, data in rows 2 to [LAST ROW]:
A = Invoice, B = Date, C = Region, D = Rep, E = Amount, F = Status (Paid/Unpaid)
In [CELL] I want [RESULT].
Edge cases: [blank rows / no match / text in number column].
Give one formula, then explain each argument in one line.

The app and version line matters most: Microsoft’s documentation says XLOOKUP is not available in Excel 2016 and Excel 2019 (Microsoft), so a formula that works in Excel 365 may fail for a colleague. You do not need to paste real data. Headers and a few made-up rows are enough, which also keeps customer details out of the chat. More templates like this are in our office prompt templates.

The sample data

Every formula below was entered into this sheet and recalculated (in LibreOffice 25.2, which supports the same functions). The results shown are what the sheet returned, and we checked each one by hand.

Eight formulas, explained and verified

1. Total with two conditions: paid sales in the North

=SUMIFS(E2:E9,C2:C9,"North",F2:F9,"Paid")

SUMIFS takes the range to add first, then pairs of (range, condition). Here it adds Amount where Region is “North” and Status is “Paid”. Result: 1940 (1200 + 430 + 310). Argument order is the same in Excel and Google Sheets. The older SUMIF puts the sum range last, a frequent source of AI mix-ups.

2. Look up an invoice amount, with a fallback

=XLOOKUP("INV-1004",A2:A9,E2:E9,"Not found")

Search for the invoice in column A and return the matching value from column E. The fourth argument is what to show if there is no match; without it you get #N/A. Result: 2100. With “INV-9999” instead, the result is “Not found”. Microsoft calls the fourth argument if_not_found and Google calls it missing_value (Google); the position is the same. For Excel 2019 or earlier, ask for an INDEX/MATCH version instead.

3. Count invoices in a month

=COUNTIFS(B2:B9,">="&DATE(2026,10,1),B2:B9,"<="&DATE(2026,10,31))

Two conditions on the same date column: on or after 1 October and on or before 31 October. The & joins the comparison sign to the date. Result: 6 (rows 3 to 8). If this returns 0 on your sheet, your dates are probably stored as text; check by changing a date cell’s format to Number.

4. Total for a date range

=SUMIFS(E2:E9,B2:B9,">="&DATE(2026,10,1),B2:B9,"<"&DATE(2026,11,1))

Same idea, adding amounts. Using “before 1 November” instead of “on or before 31 October” also catches times late on the 31st if your dates include times. Result: 5305 (850 + 430 + 2100 + 640 + 975 + 310).

5. Flag overdue invoices

=IF(AND(F3<>"Paid",DATE(2026,11,15)-B3>30),"Overdue","")

If the invoice is not paid and more than 30 days have passed, show “Overdue”; otherwise leave the cell blank. Subtracting dates gives days. Result for row 3 checked on 15 November: “Overdue” (44 days). Checked on 1 November it is blank, because 30 days is not more than 30. In real use, replace DATE(2026,11,15) with TODAY() and fill the formula down.

6. Clean up messy names

=PROPER(TRIM(D2))

TRIM removes extra spaces; PROPER capitalises each word. Result: “Ana Lopez” from ” ana LOPEZ ”. This makes names match for counting and lookups. PROPER will get names like “McDonald” wrong (“Mcdonald”), so scan the results.

7. Average for one region

=ROUND(AVERAGEIFS(E2:E9,C2:C9,"North"),2)

Average Amount where Region is North, rounded to two decimals. Result: 645 ((1200 + 430 + 640 + 310) ÷ 4).

8. Working days between two dates

=NETWORKDAYS(DATE(2026,11,2),DATE(2026,11,13))

Counts Monday-to-Friday days between two dates, including both ends. 2 November 2026 is a Monday and 13 November a Friday. Result: 10. An optional third argument takes a range of holiday dates to exclude (Microsoft).

How to check any AI formula

1. Pick two rows where you know the answer. Work them out by hand or with a filter, and compare.
2. Test a “no result” case. A region that does not exist should return 0 or your fallback text, not an error.
3. Test an edge case. A blank row, a date at the boundary, a name with extra spaces.
4. Ask for the explanation, then read it against your columns. If the AI says “column C is Amount” and it is not, the formula is wrong even if it returns a number.
5. Check after filling down. Ask whether ranges should be fixed with $ (for example $E$2:$E$9) so they do not shift when copied.

Excel or Sheets differences worth telling the AI

• Version. XLOOKUP needs Excel 2021 or later, Excel for Microsoft 365, or Google Sheets.
• Separators. In some regional settings, arguments are separated with semicolons rather than commas. If a pasted formula is rejected, that is the first thing to check.
• Sheets-only functions. Google Sheets has functions such as QUERY that Excel does not. If colleagues use Excel, ask for a formula that works in both.

Should you paste the spreadsheet itself?

Usually not. Headers and invented sample rows are enough for formula help. If you want the AI to analyse real figures, check what your plan does with uploaded files first: see Is it safe to put company data into ChatGPT, Claude or Gemini?.

If formula help saves you time every week, estimate what that is worth, after subtracting checking time, with the worked example in how much time AI really saves a small business.

Written by Editorial team · Last updated: 2026-11-01 · Facts last verified: 2026-11-01 · Editorial policy