You paste a messy column into ChatGPT, type "give me a formula to clean this", and get back something that returns #NAME?, or worse, a plausible-looking wrong number. The model isn't being careless. It was never told which app you use, what the cells really contain, or what should happen on the rows that don't fit the pattern.
Good ChatGPT Excel and Google Sheets formula prompts fix that by sending four things up front: the app, the layout, a few real-shaped rows, and the exact output you want. Then you test the formula on those rows before trusting it. The rest of this post is the template, formulas that behave the same in both apps, the ones that don't, and a short checking routine.
I haven't got a spreadsheet engine in my working environment, so the formulas below were checked against Microsoft's and Google's function documentation (on 2026-10-08) and the regex patterns were run separately in Python, not executed in Excel or Sheets. Paste them into a scratch sheet first. That habit is the point of the whole post.
What should a spreadsheet formula prompt include?
Four blocks. Skip one and you get a guess.
App: Google Sheets (or: Excel 365 / Excel 2019 / Excel for web)
Layout: Sheet "Orders", header in row 1, data from row 2.
A = Order ID (text), B = Region (text), C = Order date (text like 07/03/2026, dd/mm/yyyy),
D = Amount (text like "$1,284.50"), E = Customer email
Sample rows (invented):
INV-20417 | North | 07/03/2026 | $1,284.50 | dana@example.com
INV-20418 | north | 11/03/2026 | $90.00 | raj@example.com
INV-20419 | | 11/03/2026 | $0 |
Goal: in column F, the amount as a real number. Blank if D is blank.
Edge cases: extra spaces, "$" and thousands commas, empty cells, lowercase vs uppercase region.
Output: one formula for F2 that I can fill down. Explain each function in one line.
If it needs a function that is not in my app, say so and give an alternative.
Why each block earns its place:
- App and version. Excel 2019 has no
XLOOKUP. Sheets has noTEXTSPLIT. The model can't know. - Layout with data types. "Amount" could be a number or the text
$1,284.50. The formula is completely different. This is the single most common cause of a formula that "almost works". - Sample rows with the ugly ones. Three to five rows. Invent them if the real ones are sensitive.
- Edge cases and output shape. One cell to fill down, or a spilled array, or a whole-column result. Say which.
The last line (alternatives if the function doesn't exist) matters more than it looks. Without it, models sometimes confidently use a function from the other app. The same principle runs through the clarity and specificity lesson: the model fills every gap you leave with its most likely guess.
Which formulas work identically in Excel and Google Sheets?
If you need a file to move between both, stay in this set. All of these have the same argument order in Microsoft's and Google's documentation.
| Task | Formula (works in both) |
|---|---|
| Lookup with a fallback | =XLOOKUP(A2, Prices!A:A, Prices!C:C, "not found") |
| Sum with two conditions | =SUMIFS(D:D, B:B, "North", C:C, ">="&DATE(2026,1,1)) |
| Sorted unique list, blanks removed | =SORT(UNIQUE(FILTER(B2:B500, B2:B500<>""))) |
| Join values into one cell | =TEXTJOIN(", ", TRUE, A2:A10) |
| Text-formatted amount to number | =VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(D2),"$",""),",","")) |
| Remove non-breaking spaces | =TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")) |
| Text date dd/mm/yyyy to real date | =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)) |
Two notes on that table. XLOOKUP is not in Excel 2016 or 2019, according to Microsoft's page, so the "works in both" claim means Excel 365 and Sheets. And Sheets documents XLOOKUP with a missing_value fourth argument, Excel calls it if_not_found; it's the same slot.
The date formula is deliberately boring. DATEVALUE("07/03/2026") depends on your locale: it may read that as 7 March or 3 July. If your data came from a system that always writes dd/mm/yyyy, slicing the text apart with LEFT, MID and RIGHT can't be misread, and that's what you want when the answer feeds an invoice ageing report. Tell ChatGPT your date format explicitly, with a sample, or it will pick one.
The UNICHAR(160) line is for data copied from web pages or PDFs. A non-breaking space looks exactly like a space but TRIM ignores it, which is why a lookup "fails" on values that look identical.
Where do Excel and Google Sheets formulas differ?
This is where pasted-from-chat formulas break. Check this table against any answer you get.
| Task | Excel (365) | Google Sheets |
|---|---|---|
| Split "first last" into columns | =TEXTSPLIT(A2," ") | =SPLIT(A2," ") |
| Last word of a cell | =TEXTAFTER(A2," ",-1) | =REGEXEXTRACT(A2,"[^ ]+$") |
| Regex extract | =REGEXEXTRACT(A2,"INV-[0-9]+") (newer builds only) | =REGEXEXTRACT(A2,"INV-[0-9]+") |
| Apply a formula to a whole column | Write it once; it spills | =ARRAYFORMULA(TRIM(A2:A)) |
| Group and total | Pivot table, or SUMIFS per unique key | =QUERY(A1:D500,"select B, sum(D) where B is not null group by B",1) |
What I confirmed from the docs: Google's function list has no TEXTSPLIT, TEXTBEFORE, TEXTAFTER or XMATCH entries, and it has SPLIT, QUERY, REGEXEXTRACT, REGEXREPLACE and REGEXMATCH. Microsoft documents TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]), and a REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) that uses PCRE2 regex and always returns text. Sheets' version is REGEXEXTRACT(text, regular_expression) with two arguments only.
I did not find Microsoft's documentation stating which Excel versions have the regex functions, so if you see #NAME?, assume your build doesn't. TEXTAFTER with a negative instance number (counts from the end) is from my knowledge of the function and I did not re-check its page, so test it.
Two traps worth naming:
- Trailing spaces break
[^ ]+$. The pattern means "non-space characters at the very end". If the cell ends in a space, nothing matches. Wrap the input:REGEXEXTRACT(TRIM(A2),"[^ ]+$"). I ran that pattern in Python againstDana Q. Whitfield,Raj Pateland a cell with leading and doubled spaces; it returned the last word each time. - Regex output is text. In both apps, a number pulled out with regex is a string. Wrap it in
VALUE()before you sum it.
How do I prompt for each common job?
These are the prompt openings I'd reuse, each paired with what a correct answer looks like. The formulas shown are written by me and checked against syntax docs, not outputs from a ChatGPT run. Treat them as the target your prompt should land on.
Cleaning messy text
App: Excel 365. Column A has names like " dana WHITFIELD ". Return "Dana Whitfield" (proper case, single spaces). Fill-down formula for B2.
Target: =PROPER(TRIM(A2)). TRIM in both apps collapses repeated inner spaces to one. If the model answers with a REGEXREPLACE, ask whether plain TRIM does the job. Simpler formulas survive longer.
Pulling an ID out of a sentence
App: Google Sheets. Column A contains email subjects like "Re: INV-20417 overdue". Extract the invoice ID (INV- followed by digits). If none, return blank. If there are two IDs, return the first.
Target: =IFERROR(REGEXEXTRACT(A2,"INV-[0-9]+"),""). The Python check on paid INV-9 and INV-12 shows the pattern finds both, so "return the first" is something you must say, because the two apps handle multiple matches differently (Excel's return_mode lets you choose; Sheets returns the first).
Looking up a value that might not exist
App: Excel 365. Sheet "Orders" column B holds SKU. Sheet "Prices" has SKU in A and price in C, with 800 rows. Return the price, or the text "check SKU" if missing. Do not use VLOOKUP.
Target: =XLOOKUP(B2, Prices!A:A, Prices!C:C, "check SKU"). The "do not use VLOOKUP" line is a real constraint, not decoration: it stops the model from falling back to a column-index formula that silently breaks when someone inserts a column.
Summarising by group (the pivot question)
In Excel, ask for a pivot table walk-through, not a formula. Pivot tables don't need maintaining when rows are added; a SUMIFS grid does. In Sheets, QUERY is the compact option:
App: Google Sheets. Range A1:D500, header in row 1. Column B = Region, D = Amount (already numeric). One cell that returns Region and total Amount, largest total first, no blank regions.
Target: =QUERY(A1:D500,"select B, sum(D) where B is not null group by B order by sum(D) desc",1). QUERY uses Google's own query language, which models mix up with SQL. If the reply uses HAVING or JOIN, it's wrong for this function.
Prefer a pre-built version of this kind of request? The data prompts in the library cover CSV analysis when you'd rather work with an uploaded file than formulas.
Fixing an error someone else's formula returns
Paste the formula, the error text, and what the cell references contain:
This formula returns #VALUE! in row 14 only. App: Excel 365. Formula:
=C14*D14. C14 shows 12 but is left-aligned; D14 is 4.5. What is wrong, and what is the one-line test I can run to confirm?
Left-aligned "numbers" are text. The confirmation test is =ISNUMBER(C14). Asking for the test, not just the fix, trains you to diagnose the next one yourself.
How do I check a formula before trusting it?
Do this every time the result feeds money, a report, or another person's decision. It takes five minutes.
- Write the answers first. For five rows, write down by hand what the output should be. Include a blank row, a row with extra spaces, and a row that shouldn't match.
- Fill down five rows only. Compare. Any mismatch means the formula is wrong for that case, not that your data is "weird".
- Make the model attack its own formula. Prompt: "List three inputs where this formula returns a wrong value without showing an error." Wrong-but-no-error cases are the dangerous ones, and
IFERRORhides them. If a formula hasIFERROR(...,0)wrapped round it, remove the wrapper while testing so you can see what was being swallowed. - Cross-check one total another way. If a helper column's sum should equal the amount on a pivot, check it. In Excel, Formulas then Evaluate Formula steps through a nested formula one function at a time.
- Convert to values only after checking. Then keep the original column. Undo history doesn't survive closing the file.
The mindset is the same as in the iteration loop for refining prompts: the first answer is a draft, and you tighten the prompt using what the test showed you.
When should I not use a ChatGPT-written formula?
Skip it when you can't describe the correct output for a sample row. If you can't, no tool can check the result for you. Also skip it for anything with legal or payment consequences (tax computations, commission payouts) unless a second person reviews the logic. And don't paste real customer records to "get it working faster"; the invented-rows habit costs two minutes. If you already use Claude for spreadsheet work, the Claude Google Sheets and Excel automation post covers the automation side, which is a different problem from one-off formulas.
A reusable prompt template
App and version: [Excel 365 / Excel 2019 / Google Sheets]
Layout: [sheet name, header row, what each column holds AND its data type]
Sample rows (invented, include blank + messy cases):
[3-5 rows]
Goal: [output in plain words, which cell, fill-down or single cell]
Edge cases: [blanks, spaces, case, duplicates, text-formatted numbers]
Constraints: [functions to avoid, must work in both apps, no helper columns]
Reply with: the formula, one line per function explaining it, then
three inputs that would make it return a wrong answer without an error.
If a function is not available in my app, say so and offer an alternative.
Paste your own layout in, run the five-row test, and keep the version that passed in a "formulas" tab next to the data. Next time you need the same thing, you start from a tested formula instead of a blank prompt.
