How to Get Working Excel Formulas from ChatGPT
Describe the sheet so ChatGPT can answer, ask for the formula plus a breakdown, verify it on real rows, and fix the four things that go wrong.
On this page4 sections
ChatGPT is very good at spreadsheet formulas and very bad at guessing your spreadsheet. Almost every wrong answer comes from a prompt that described the goal without describing the data.
1. Describe the sheet
Four things: the app and version, the exact column letters and headers, two or three real rows, and the target cell.
Google Sheets. Sheet named "Orders".
A1:E1 headers = Date | Customer | Region | Amount | Status
Row 2: 2026-01-04 | Acme Ltd | EMEA | 1250.00 | Paid
Row 3: 2026-01-05 | Beta GmbH | EMEA | 890.50 | Refunded
Data runs A2:E5000.
In cell H2 I want: total Amount for EMEA rows where Status is "Paid",
only for dates in the current month.
Give me one formula. Tell me which functions need a specific version.
Everything a formula needs is in there: app, range, data types, target cell. “Sum my sales by region” is not.
2. Ask for the breakdown
Give me the formula, then explain each argument on its own line —
what it does and which cells it touches. Keep the explanation under 100 words.
The breakdown is how you catch a wrong range before you fill 5,000 rows. If an argument’s explanation does not match your sheet, the formula is wrong regardless of how confident it looks.
3. Verify
Paste it into one cell only. Test three rows you can check by hand, including one awkward case — a blank, a zero, a refund, a date on the month boundary. Cross-check the total with a second, dumber formula (a manual sum of a filtered view). Fill down only once the numbers agree.
When it errors, give the error back verbatim:
That returns #N/A in H2. Here is the formula I pasted: <paste it>
Column C values look like "EMEA " with a trailing space. Fix it and
tell me what caused the error.
Paste the real formula and the real error text — not “it doesn’t work”. The trailing space is exactly the kind of thing it will catch.
4. The four things that go wrong
- Wrong dialect.
ARRAYFORMULA,QUERYandIMPORTRANGEare Google Sheets only.XLOOKUPneeds Microsoft 365 or Excel 2021+ — older Excel needsINDEX/MATCH. - Argument separators. Locales that use a comma as the decimal separator need semicolons between arguments. ChatGPT emits commas; Excel answers with an unhelpful error.
- Ranges that shift. Formulas filled down need
$on anything that must stay fixed. Ask for absolute references explicitly. - Text that looks like numbers or dates. Leading zeros, trailing spaces and text-formatted dates break every lookup. The formula is fine; the data is not.
Rewrite that formula for Excel 2019 (no XLOOKUP, no dynamic arrays),
with absolute references so I can fill it down from H2 to H5000.
Converts an answer that assumed a newer version into one your machine can run.
Next: clean the data before you write formulas or analyze a CSV with ChatGPT.