The claim that "an LLM can't do spreadsheets, you're better off with VLOOKUP and filters by hand" survives mostly because people test it wrong. They paste a vague question — "why isn't my formula working" — and get a vague answer. Feed the model your column structure, two sample rows and the exact result you expect, and the same model writes a working XLOOKUP, explains a #REF!, builds a pivot spec and drafts Google Apps Script. The skill is not in the model, it's in the prompt.
Below is a working set of prompts for Excel and Google Sheets, each with the context you must supply and the failure mode to watch for.
The setup prompt that makes everything else work
Before any formula, give the model a schema. One prompt, reused as a prefix, saves you from 80% of bad answers.
Here is my table structure.
Sheet "Sales", columns A:H:
A Date
| B Manager | C Region | D Client | E Product |
F Qty
| G Unit Price | H Status (Paid / Pending / Cancelled)
Sample rows:
2026-10-01
| Ivanov | North | Acme | Widget | 3 | 120 | Paid
2026-10-02
| Petrova | South | Beta LLC | Gadget | 1 | 400 | Pending
Target: Excel 365 (or Google Sheets — I will say which).
Data starts at row 2, ~5,000 rows, no blanks inside the range.
Answer only with the formula plus one line of explanation.
Why this matters: without column letters and a data type per column, the model guesses whether "Status" is text or a code, and produces a formula that looks right and returns #VALUE!. The sample rows are not decoration — they anchor the delimiter, date format and whether numbers are stored as text.
Prompt 1: "Explain why this returns #N/A / #VALUE / #REF"
The most-used prompt in real work. The trick is to hand over the formula, the referenced ranges and one failing row — not just the error.
Formula: =XLOOKUP(D2, Clients!A:A, Clients!C:C)
Returns #N/A for client "Beta LLC", works for others.
Clients!A contains names, Clients!C contains emails.
In Sales, D2 was typed manually; in Clients, names came from a CSV import.
Diagnose the cause and give a fix that does not require editing 5,000 rows.
A good model will name the two usual suspects: trailing spaces from the CSV import, and text-vs-number mismatch. It will then propose =XLOOKUP(TRIM(D2), TRIM(Clients!A:A), Clients!C:C) or point you to "if_not_found" as the fourth argument so the error becomes a readable label instead of a wall of #N/A.
Failure mode: the model confidently suggests IFERROR as a fix. It hides the symptom and leaves dirty keys in place — the same rows will fail in next month's pivot. Ask for the root cause first, the wrapper second.
Prompt 2: conditional aggregation with SUMIFS and COUNTIFS
In Google Sheets, sum column F where B = "Ivanov", H = "Paid",
and A is between 2026-10-01 and 2026-10-31.
Dates in A are real dates, not text. Give the SUMIFS and a COUNTIFS
that counts the same slice, plus a note on why the criteria order must
match the sum range order.
This is where models shine: they keep the range/criteria pairing straight, which is the single most common hand-written bug. Expect =SUMIFS(F2:F, B2:B, "Ivanov", H2:H, "Paid", A2:A, ">="&DATE(2026,10,1), A2:A, "<="&DATE(2026,10,31)).
Trade-off: SUMIFS over whole columns is readable but recalculates over a million rows. Bounded ranges (F2:F5000) are faster and break the moment someone appends rows. Pick bounded ranges plus a named dynamic range if the sheet grows.
Prompt 3: pivots described in words, not clicked by hand
Build a pivot from "Sales": rows = Manager, columns = month of A,
values = sum of F*G (revenue), filter H <> "Cancelled".
Give me (a) the exact field placement for the PivotTable editor,
(b) a SUMIFS grid that reproduces the same numbers without a pivot,
so I can verify it.
The second half is the non-obvious part: a formula grid next to the pivot is a cheap audit. If the totals disagree, either the pivot filter or the SUMIFS criteria is wrong, and you find it in minutes instead of at month-end.
| Need | Pivot | SUMIFS grid |
|---|---|---|
| Ad-hoc exploration | Fast | Slow to set up |
| Sharing a static report | Needs refresh | Recalculates live |
| Auditing someone else's numbers | Opaque | Transparent |
| Grouping by month | Built in | Needs a helper column |
Prompt 4: QUERY for Google Sheets people
Write a QUERY that selects B, sum(F*G) from Sales where H = 'Paid',
groups by B, orders by sum desc, labels the sum 'Revenue'.
Remind me of the header-row syntax and the date literal format.
You get something like =QUERY(Sales!A2:H, "select B, sum(F*G) where H = 'Paid' group by B order by sum(F*G) desc label sum(F*G) 'Revenue'", 0). The trailing 0 tells QUERY the data has no header row — a parameter people forget, then wonder why the first manager disappears.
Where it breaks: QUERY cannot mix column types in one column. If column F holds numbers as text after an import, sum(F*G) returns null with no error. The fix is a helper numeric column, not a cleverer query.
Prompt 5: an Apps Script that emails the report on a schedule
Write a Google Apps Script that, every Monday at 08:00,
reads sheet "Summary" range A1:D20, converts it to an HTML table,
and emails it to a fixed address with subject including the date.
Use a time-based trigger. Show how to install the trigger once.
Do not use deprecated APIs.
A competent answer uses SpreadsheetApp.getActiveSpreadsheet().getSheetByName(...), getDisplayValues() (not getValues() — you want formatted dates, not serial numbers), builds <table> rows in a loop, sends via MailApp.sendEmail({to, subject, htmlBody}), and installs the trigger with ScriptApp.newTrigger('sendReport').timeBased().onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(8).create().
Failure mode: the trigger fires in the script owner's timezone, and the sheet's timezone may differ. Set both explicitly in project settings, or the "Monday 08:00" report arrives Sunday evening for half the team.
Prompt 6: from messy import to a clean table
Column A has "Ivanov Ivan" and "ivanov, i." mixed. Column B has
dates as text in dd.mm.yyyy. Give me formulas (not manual edits)
to normalize both, and flag rows where parsing is ambiguous.
Expect =TEXTJOIN(" ", TRUE, ...) splits, =DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2)) for the date, and — the useful part — a flag column that returns "check" when the pattern does not match. Models are good at writing the flag; they are bad at guessing your naming convention, so state it.
Prompt 7: explain the formula you just wrote
Explain this formula line by line as if to a colleague who knows
VLOOKUP but not dynamic arrays. Then give a shorter equivalent.
This is the prompt that turns copy-paste into skill. It also catches errors: if the model's explanation contradicts the formula, one of them is wrong, and you will spot it faster than by testing.
What actually decides quality
Three inputs separate a working answer from a plausible one: column letters with types, one or two real rows, and the target engine. Add a fourth — the expected result for a known row — and you can verify the model's formula before it touches your file.
The myth dies here: the model is not bad at tables, it is bad at guessing your table. Give it the schema and it behaves like a patient colleague who never gets tired of rewriting SUMIFS. For a parallel look at how structured prompts close security gaps in data layers, see Firestore Security Rules and Supabase RLS; for prompts that rehearse a conversation instead of a formula, the salary negotiation roleplay follows the same principle — context in, usable output out.
Start with the schema prompt, reuse it as a prefix, and keep one verification row per formula. That habit alone removes most of the #N/A you currently fix by hand.