Most people who ask an LLM to "clean this export and build a pivot" get back a wall of plausible-looking formulas that break on the first blank cell. The problem is rarely the model. It is that the prompt was written for the wrong execution environment.
Excel and Google Sheets look interchangeable from a screenshot. Under an LLM they are two different machines. Excel rewards formula-first prompts you can paste into a cell and edit live. Google Sheets rewards script-first prompts that produce repeatable Apps Script pipelines. Pick the wrong one and you spend an afternoon debugging a mismatch you created yourself.
Below is a working prompt stack for both paths, with the decision criterion stated up front: fast summary and live editing → Excel formulas; automation and a repeatable pipeline → Apps Script.
The decision table: which stack fits your task
| Criterion | Excel formula prompts | Google Sheets / Apps Script prompts |
|---|---|---|
| Data arrives | once, ad hoc | on a schedule, repeatedly |
| You need to edit the result | yes, cell by cell | no, the script owns it |
| Volume | up to ~1M rows | API-bound, quota-limited |
| Audit trail | formula is visible in the cell | lives in version history |
| Failure mode | broken references after a row shift | silent timeout mid-run |
Use this table before you type a single prompt. The rest of the article assumes you already know which column you are in.
Formula prompts: make the LLM reason about your grid, not about Excel in general
1. The schema-first pivot prompt
A pivot table is a grouping operation, and LLMs fail at it when they do not know your column letters. Force the model to restate the schema before it writes anything.
You are a spreadsheet analyst. Here is my sheet, starting at A1:
A=Date, B=Region, C=Product, D=Units, E=UnitPrice, F=Revenue
Rows 2..5000 are data. F is currently empty.
Task: build a summary by Region and Product.
1. First, restate my schema back to me as a table.
2. Then give me a SUMIFS formula for F2 that computes Revenue = Units * UnitPrice,
and a separate SUMIFS block for a Region x Product matrix on a new sheet.
3. Use absolute references for the criteria ranges.
4. Tell me exactly which cell each formula goes into.
Example result: the model returns =D2*E2 for F2, then a matrix using =SUMIFS(Data!$F:$F,Data!$B:$B,$A2,Data!$C:$C,B$1). The restate step is what stops it from inventing a column G.
2. The data-cleaning prompt that names every defect
Generic "clean my data" prompts produce generic TRIM formulas. Instead, enumerate the defects you actually saw in the first 20 rows.
My export has these defects, confirmed by eyeballing rows 2-21:
- Leading/trailing spaces in column B (names)
- Mixed date formats: some DD/MM/YYYY, some YYYY-MM-DD text in column A
- Duplicate rows where A+B+C match but D differs
- Empty cells in E that should be 0
For each defect, give me ONE formula that flags the row in a helper column,
not one that silently fixes it. I want to review before I overwrite.
Flagging instead of overwriting is the non-obvious move. A formula like =IF(TRIM(B2)<>B2,"space","") lets you filter and eyeball before you destroy the original. Silent fixes are how people lose a column of real data.
3. The formula-explainer prompt for inherited workbooks
When someone hands you a workbook with =IFERROR(INDEX(...MATCH(...)),"") in 40 cells, ask the LLM to narrate it in plain language and then simplify.
Explain this formula as if to a colleague who knows VLOOKUP but not INDEX/MATCH:
=IFERROR(INDEX(Data!$F:$F,MATCH(1,($B2=Data!$B:$B)*($C2=Data!$C:$C),0)),"")
Then give me an equivalent XLOOKUP version and say which is faster on 50k rows.
The result explains the array-multiplication trick, then offers =XLOOKUP(1,($B2=Data!$B:$B)*($C2=Data!$C:$C),Data!$F:$F,""). The speed question matters: volatile array formulas recalculate on every edit and will crawl on large sheets.
Script prompts: make the LLM write a pipeline you can re-run
4. The Apps Script skeleton prompt
Apps Script runs server-side against the Sheets API. Ask for structure first, logic second.
Write a Google Apps Script function cleanExport() that:
1. Reads sheet "Raw" into a 2D array with getDataRange().getValues().
2. Finds column indexes by header name, not by position.
3. Trims strings, parses dates with a try/catch per cell, logs bad rows.
4. Writes the cleaned array to sheet "Clean" with setValues().
Return the full function with comments. Do not use SpreadsheetApp.flush() unless needed.
The header-name lookup is the whole point. Position-based scripts break the moment someone reorders columns; name-based ones survive.
5. The quota-aware batch prompt
Apps Script has execution time limits and API call quotas. A naive script that calls getRange() inside a loop will time out on a few thousand rows.
My script loops over 8,000 rows calling sheet.getRange(i,1).setValue().
It times out. Rewrite it to:
- read everything once into an array,
- transform in memory,
- write back in a single setValues() call.
Explain how many API calls the old and new versions make.
Example result: the model returns a version that makes roughly 2 calls instead of 16,000, and explains that each setValue is a separate request. This is the single biggest performance fix in Apps Script work.
6. The idempotent pipeline prompt
If your script runs on a trigger, it must not duplicate rows on a re-run. Say so explicitly.
My script appends cleaned rows to "Archive" daily via a time-driven trigger.
If it crashes halfway and re-runs, it must not duplicate.
Add a dedupe key (Date + OrderID) and skip rows already present in Archive.
Show the guard code and where it goes.
The result adds a Set of existing keys built from the Archive sheet before the append loop. Without this guard, a mid-run failure quietly doubles your data over a week.
Where both stacks break, and how to catch it early
LLMs hallucinate spreadsheet functions less than people fear, but they hallucinate syntax variants often. They will mix Excel and Sheets dialects in one answer: TEXTJOIN exists in both, but FILTER and UNIQUE behave differently across versions, and SPILL ranges do not exist in older Excel.
Name your environment in the prompt: "Google Sheets, current version" or "Excel 365 desktop." One line saves a debugging session.
The second failure is silent coercion. A date stored as text passes through SUMIFS as zero without an error. Ask the model to add a validation column that flags non-numeric values before any aggregation, and check that column has no hits before you trust the total.
Picking your stack in one sentence
If the answer has to be edited by a human later, prompt for formulas. If the answer has to be re-run by a machine tomorrow, prompt for Apps Script. Everything else — model choice, temperature, phrasing — is noise next to that decision.
Start with one real export. Run the schema-first prompt on it, then run the script skeleton on a copy. Whichever one you would rather maintain in three months is your stack. For deeper prompt structure and security patterns, see our prompt engineering guide and the AI Business Automation.