You paste a 4,000-row sales export into ChatGPT and type: «analyze this data». Thirty seconds later you get a confident paragraph about «a strong positive correlation between discount and revenue» and a bar chart. You forward it to your boss. Two weeks later finance asks why margin dropped in March — and it turns out the «correlation» was one wholesale order with a typo: quantity 4000 instead of 400. The model didn't lie. It did exactly what you asked: it produced plausible text. It never checked that the column labeled revenue was stored as text, never noticed 12% of rows had empty region, and never looked for outliers at all.
That is the whole problem in one sentence: an LLM without a schema and a business context is a text generator, not an analyst. A real analyst opens the file, checks types, counts blanks, looks at the distribution, finds the weird rows, and only then draws a chart. The prompts below force that order of operations. Each one targets a specific stage, and skipping a stage is how you get a beautiful chart of garbage.
Why «analyze these data» fails before it even starts
Three mechanisms break the naive prompt, and all three are invisible in the output.
1. The model cannot see your column types. In Excel and Google Sheets, a number can be a number, a text string, or a date serial that displays as a number. When you paste a table, 1,250 and 1250 may arrive differently; a European 1.250,50 may be read as 1.25. The model averages what it thinks is numeric and quietly drops what it thinks is text. Result: your mean is computed on 60% of the rows and nobody knows.
2. Correlation is the cheapest thing to hallucinate. Ask for «insights» and the model reaches for relationships, because relationships read well. It has no concept of a confounder. Two columns rising together in your export can be a shared season, a shared region, or a shared data-entry clerk.
3. Outliers are the thing you actually needed, and the thing it skips. An anomaly is by definition rare, and rare things don't show up in a summary paragraph. You have to hunt them explicitly, with a rule — IQR fences or z-scores — and you have to ask for the row numbers.
Stage 1: make the model read the table before it interprets it
1. The schema extraction prompt
Use this the moment you have a file you didn't build yourself — a vendor export, a colleague's tracker, a Google Form dump. It turns a wall of cells into a contract the model must follow for the rest of the session.
Prompt:
You are a data analyst. Below is the header row and the first 5 data rows of a spreadsheet. Do NOT analyze anything yet. Produce a table with columns: column name
| what it appears to contain | likely data type (number/date/text/currency/boolean) | unit or format you infer | ambiguity or risk. Then list every column where you are less than 90% sure of the meaning. Ask me up to 5 questions that would remove the ambiguity. Do not guess business meaning — ask.
Example: you paste a CRM export with columns deal_value, close_date, stage, owner_id, created. The model comes back flagging that deal_value has no currency and that stage contains both «Closed Won» and «closed-won» — a normalization problem you would have found three pivot tables later.
2. The type-audit prompt
Schema tells you what a column should be. This one tells you what it is.
Prompt:
For each column in this data sample, tell me how many values would fail a strict type check: text in a numeric column, non-ISO dates, numbers stored as text, mixed decimal separators, trailing spaces. For each failure, show one example value exactly as it appears. Rank columns by how much damage the failures would do to an average or a SUM.
This is where the 1.250,50 problem surfaces. A column with mixed separators will silently sum to nonsense, and the model will happily report the nonsense.
3. The missing-data prompt
Prompt:
Count blank and placeholder values per column (blank, "n/a", "-", "0"). For each column with more than 2% missing, tell me: is the missingness random, or does it cluster — e.g. all blanks in one region, one month, or one owner? If it clusters, what could that mean for any average I compute on this column?
Clustered missingness is the silent killer. If discount is blank for every order from one sales rep, your discount average describes the other reps, not the company.
Stage 2: anomaly hunting with an actual rule
4. IQR fences for a single numeric column
This is the workhorse. The interquartile range method flags anything outside 1.5×IQR below Q1 or above Q3 — the same rule a box plot draws.
Prompt:
For the column [NAME], compute Q1, Q3, IQR, and the lower and upper fences at 1.5×IQR. List every value outside the fences with its row number and the original row. For each, tell me whether it looks like a legitimate extreme (a real large order) or a data error (a typo, a unit mix-up, a duplicated entry) and why. Then recompute the mean and median with and without those rows and show me how much the mean moves.
Example: quantity column, Q1=5, Q3=40, IQR=35, upper fence=92.5. The model pulls out three rows: 4000 (likely a typo for 400), 150 (a genuine bulk order), and 98 (borderline). Recompute: mean drops from 47 to 31 once the typo is excluded. That gap — 16 units of fiction — is the number your original «analyze this» prompt would have reported as fact.
5. Z-score sweep across many columns at once
IQR is per-column. When you have 30 numeric columns and no idea where to look, sweep.
Prompt:
For every numeric column, compute the z-score of each value against that column's mean and standard deviation. Flag every value with
|z| > 3. Give me a single ranked list: column | row number | value | z-score. Then tell me which columns produced the most flags — that column is either genuinely volatile or has a data-entry problem, and I need to know which.
A column that produces a dozen
|z|>3 flags is not a column full of business surprises. It's a column with a broken input rule.
6. The duplicate and near-duplicate prompt
Prompt:
Find rows that are exact duplicates on all key fields. Then find near-duplicates: same customer, same amount, dates within 48 hours, different order IDs. Show me both lists with row numbers. For the near-duplicates, tell me which are likely double entries and which are legitimate repeat purchases, and what field would let me tell them apart.
Double entries inflate every total. In a monthly revenue report, one duplicated invoice can move a small team's number by a visible percentage.
7. The time-series break prompt
Anomalies aren't only extreme values — they're breaks in a trend.
Prompt:
Plot the daily/weekly totals of [METRIC] as a sequence. Identify any point where the value deviates from the recent trend by more than 3 standard deviations of the residual, or where the level shifts and stays shifted. For each break, give the date, the size of the shift, and the two or three rows most responsible. Then tell me whether the break coincides with a weekday/holiday pattern.
A level shift that starts on a Monday and never recovers is usually a process change, not a demand change. Knowing which saves you a wrong explanation in the meeting.
Stage 3: only now do you aggregate
8. The pivot specification prompt
Once the data is clean and you know where the outliers are, ask for the pivot as a specification you can build — not as a picture.
Prompt:
I want a pivot with [ROWS] as rows, [COLUMNS] as columns, and SUM of [VALUE] as values, filtered to [CONDITION]. Write the exact steps for Google Sheets and for Excel separately, including where the outlier rows I excluded should be handled (filter vs. helper column). Then tell me what this pivot will NOT show me.
That last sentence is the point. A pivot by region hides seasonality; a pivot by month hides customer concentration. The model naming the blind spot is worth more than the pivot.
9. The QUERY formula prompt for Google Sheets
Prompt:
Write a Google Sheets QUERY formula that returns [DESIRED OUTPUT] from range A1:H5000, excluding rows where column D is blank and where column F is outside the IQR fences we computed. Explain each clause. Then give me the equivalent as a pivot table, and tell me which one updates automatically when new rows are added.
10. The XLOOKUP / INDEX-MATCH reconciliation prompt
Prompt:
I need to match records between two sheets on [KEY]. Write an XLOOKUP that returns [FIELD] and a fallback of "NOT FOUND". Then write a second formula that flags rows where the match exists but a second field disagrees. Tell me which failure mode — missing key, duplicate key, type mismatch — will bite first, and how to check for duplicates before running the lookup.
Duplicate keys are why XLOOKUP returns the «wrong» value: it returns the first match, silently. The prompt makes the model warn you before you trust the join.
11. The Apps Script prompt for repeated cleaning
If you clean the same export every week, formulas stop being enough.
Prompt:
Write a Google Apps Script that opens the active sheet, trims whitespace in all text columns, converts numbers stored as text to numbers, deletes rows where [KEY COLUMN] is blank, and logs the count of each change to a new sheet called "cleaning_log". Add a comment on every non-obvious line. Do not touch any other sheet.
The log sheet is the non-obvious part — it lets you prove next month that the cleaning actually ran.
12. The «what would change my mind» prompt
Prompt:
Here is the conclusion I plan to present: [YOUR CONCLUSION]. List the three data checks that could falsify it, and for each, tell me the exact column, filter, and threshold I should test. Then tell me which of those three is most likely to fail on this dataset and why.
This is the prompt that catches you before the meeting, not after.
Where this whole approach breaks
Two honest limits. First, the model doesn't compute — it predicts. For anything where a wrong number costs money, ask for the formula or the script and run it yourself; treat the model's arithmetic as a draft, not a result. Second, context you didn't give is context it invents. If you never say that revenue is net of returns, the model will describe gross revenue with total confidence. The schema prompt exists precisely to stop that.
For the spreadsheet-formula side of this workflow, the formula-or-script decision guide covers when to reach for a formula versus a script. And if your anomalies turn out to be a time-series problem, why Prophet and ARIMA break where you least expect it is the natural next read.
Start with prompt 1 on the next export you open. If the model can't describe your columns and their units back to you, you have no business asking it for a chart — and neither does it.