Most spreadsheet pain is not about Excel. It is about the ten minutes before Excel: you open a file exported from a CRM, and the dates are half text, half serial numbers, the column headers have trailing spaces, and someone merged cells in row 3. You spend the next hour doing work a machine should do.
An LLM will not click cells for you. But it is excellent at the part that actually eats time: turning a vague task into an exact formula, a cleaning rule, or a query you paste into Excel or Google Sheets. The trick is knowing what to ask and how to specify the schema.
Below are 12 prompts, each for a real recurring task. Copy them, swap the bracketed parts, and keep them in a notes file. The whole set works identically in Excel and Google Sheets unless a prompt says otherwise.
Formula generation: from plain English to a working cell
1. The "explain then build" formula prompt
Ask for the logic in words first, then the formula. This forces the model to commit to an interpretation before it writes syntax, and you catch a wrong assumption in one sentence instead of debugging a nested IF.
You are an Excel/Google Sheets expert. My data: columns A=Order ID, B=Customer, C=Order Date, D=Amount, E=Status.
Task: flag orders where Status is "Paid" but Amount is 0.
Step 1: describe the logic in one sentence.
Step 2: give the formula for cell F2 that returns "CHECK" or "".
Step 3: name one edge case where this formula fails.
Do not use volatile functions.
Example output: =IF(AND(E2="Paid",D2=0),"CHECK","") — and the edge case it names is usually text-formatted zeroes or a space in "Paid".
2. The version-aware formula prompt
Excel and Google Sheets diverge constantly: XLOOKUP exists in modern Excel and in Sheets, FILTER behaves differently with errors, and array formulas need ARRAYFORMULA only in Sheets. Tell the model which tool you use or you will get a formula that dies on paste.
Target: Google Sheets (not Excel). Give me a formula using ARRAYFORMULA that pulls all rows from Sheet2 where column C = "Active", into A2 of the current sheet, and returns "none" if empty. Explain why you chose IFERROR vs IFNA.
3. The debug-the-broken-formula prompt
This is the highest-ROI prompt in the list. Give the model the formula, the error, and the data types. It will usually spot the real cause — a number stored as text, a lookup range that is not locked, or a trailing space in a key.
This formula returns #N/A: =VLOOKUP(A2,Sheet2!A:D,4,FALSE)
A2 is "10045" (text, left-aligned). Sheet2 column A contains numbers.
Diagnose the cause, give the corrected formula, and give a version that is immune to text/number mismatch.
Cleaning dirty data
4. The schema-audit prompt
Before cleaning, ask the model to find what is wrong. Paste 20 rows and ask for a defect list with column names and severity. This is the step people skip, and it is why cleaning scripts break on row 4,000.
Here are 20 rows from a messy export (headers + data).
List every data-quality defect you can see: type inconsistencies, mixed date formats, duplicate keys, empty required fields, inconsistent casing.
Output as a table: Column | Defect | Example row | Fix approach.
5. The text-to-date normalizer
Dates are the single most common corruption in exports. Ask for a formula that handles several formats at once, and ask the model to state which formats it covers — silently dropping the unrecognized ones is the usual failure.
Column C has dates in mixed formats: "2026-03-01", "01.03.2026", "Mar 1, 2026", and some serial numbers like 46082.
Give a Google Sheets formula that converts all of them to a real date, and list which input formats it does NOT handle.
6. The deduplication prompt with a business rule
"Remove duplicates" is a button, but the question is which record survives. Specify the rule, or the model will guess.
Find duplicate customers by email (column B, lowercase-trimmed). Keep the row with the latest Order Date (column D) and the highest Amount (column E) as tiebreaker.
Give me: (a) a helper-column formula to rank duplicates, (b) the filter to keep only rank 1.
7. The regex extractor
LLMs write regex better than most people, and both Excel (with REGEXEXTRACT in Sheets, Power Query in Excel) and Sheets support it. Use this for pulling order numbers, domains, or phone fragments out of free text.
Column F contains free text like "Ref# INV-2026-00431 / client@corp.com".
Give me a Google Sheets REGEXEXTRACT for the invoice number (INV-YYYY-NNNNN) and one for the email.
Then give the Excel equivalent using Power Query or Text functions.
Aggregation, pivots, and reports
8. The pivot-table blueprint prompt
An LLM cannot build a pivot, but it can tell you exactly which fields go where, and that is 80% of the work. Ask for rows, columns, values, and filters explicitly.
I have sales data: Date, Region, Product, Rep, Amount, Status.
I need a monthly report: revenue by Region (rows) and Month (columns), only Status="Won", plus a % of total row.
Give me: pivot field placement, the calculated-field formula for % of total, and the chart type you recommend.
9. The SUMIFS/QUERY translator
When you need one number fast, SUMIFS is cleaner than a pivot. Ask the model to translate a sentence into SUMIFS (Excel) or QUERY (Sheets) and to warn you about criteria syntax — ">="&DATE(...) is where beginners break.
Google Sheets. Sum Amount (Sheet1!E) where Region (C) = "North", Date (A) is within the current month, and Status (D) is not "Cancelled".
Give a SUMIFS version and a QUERY version. Explain when QUERY is the better choice.
10. The dashboard spec prompt
Before you build anything, get a one-page spec: which KPIs, which charts, which filters. This prevents the classic dashboard that has 14 charts and answers no question.
My data: monthly sales by region and product, 24 months.
Audience: regional managers, reviewed weekly.
Give me a dashboard spec: 4 KPIs with formulas, 3 charts with axis choices, 2 slicers, and one thing I should deliberately leave out.
Validation and reliability
11. The data-validation and conditional-formatting prompt
Catch bad input at the door. Ask for validation rules and the exact conditional-formatting formula — note that Sheets and Excel use different relative-reference conventions here.
Column D = Amount must be a positive number under 1,000,000.
Column E = Email must contain "@".
Give: data-validation rules for both, and a conditional format that highlights the whole row red when either fails. Specify the range and the formula for Google Sheets.
12. The "explain this spreadsheet to a newcomer" prompt
Handover documentation is the invisible cost of every spreadsheet. Paste the formulas and ask for a plain-language map: what each sheet does, what depends on what, and where the fragile parts are.
Here are all formulas from a workbook (3 sheets, pasted).
Write a handover note: purpose of each sheet, key inputs, calculation flow, and the 3 most fragile points a new owner could break.
Keep it under 400 words.
Where this breaks
Two honest trade-offs. First, an LLM does not see your file — it sees what you paste, so a schema mismatch is your responsibility, not the model's. Second, formulas it writes are plausible, not verified: always test on a copy and check one known row by hand.
The non-obvious part: the value is not in the formula, it is in the specification habit. Writing "columns A–E, this rule, this edge case" for a model forces you to define the task precisely — and that alone prevents most spreadsheet errors before any formula exists.
If you want to build this skill systematically, our Data Analyst path covers the underlying logic, and the prompt-craft techniques from our art-director roundup transfer directly to spreadsheets.
Save these 12 prompts, adapt the schema blocks to your own files, and the next dirty export becomes a five-minute job instead of an afternoon.