A colored bar chart on a slide can hide a month of duplicated rows, a column where half the values are text, and one customer whose order is ten times the median. The chart looks fine. The number on the slide is wrong. And the person who made the slide genuinely believes they did an analysis.
This is the most common failure mode when people point an LLM at a spreadsheet: they ask for a chart, get a pretty one, and stop. The prompts below reverse the order. First statistics and data quality, then a hypothesis, then a visualization that confirms or kills it. Each prompt is written to be pasted as-is, with your column names swapped in. For a deeper dive into this topic, see LLM at.
Why the chart-first habit breaks
A pivot table and a bar chart answer the question "what does the data look like." They cannot answer "is this number trustworthy." Those are different jobs, and the second one has to come first.
Here is what that looks like in practice. Suppose you have twelve months of sales by region and one region shows a spike in March. The chart-first reflex is to write a caption about a spring campaign. The statistics-first reflex is to ask: how many rows feed that March point, what is the distribution of order values behind it, and are there duplicates with different timestamps?
Very often the spike turns out to be a re-import of the previous quarter's file. The chart was never wrong. The story you told about it was.
So the whole game is sequencing. Below is the prompt chain I use, in the order it should run.
Prompt 1: Profile the sheet before you trust a single number
Most bad analyses start with a skipped step: nobody looked at the shape of the data. Row count, column types, share of blanks, number of distinct values, min and max per numeric column. That is five minutes of work and it catches the majority of surprises.
You are a data quality auditor. I will paste a description of my Excel sheet (column names, row count, and a sample of 10 rows). For a deeper dive into this topic, see Excel and Google Sheets.
For each column, produce a table with: name, inferred type (numeric / date / text / mixed), share of blank cells, number of distinct values, min and max (for numeric and date), and 3 example values.
Then flag, in a separate list, every column where:
- the type is mixed (numbers stored as text, dates as strings),
- blanks exceed 5% of rows,
- a single value accounts for more than 80% of all rows,
- distinct count equals row count (possible ID column) or equals 1 (useless column).
Do not suggest charts. Do not summarize the business meaning. Only the profile and the flags.
Example. You paste a 4,000-row export of orders with columns order_id, date, region, amount, manager. The model comes back noting that amount is mixed type — about 200 cells are text like 1 250,00 with a space and a comma. Those 200 rows silently drop out of every SUM you have ever run on that column. That single flag is worth more than any chart in the file.
If you are unsure whether to have the model write formulas or a script for this kind of profiling, the trade-offs are laid out in Excel formulas or Apps Script: choosing your prompt stack. For a deeper dive into this topic, see Spreadsheets Don't Lie, They Just Return #N/A.
Prompt 2: Hunt outliers with a stated rule, not a vibe
"Find anomalies" as a prompt gets you a list of the biggest numbers. That is not anomaly detection, that is sorting. You need the model to name the method and the threshold before it names the rows. For a deeper dive into this topic, see Prompts That Read a P&L Like a CFO.
Act as a statistician reviewing one numeric column in Excel. Column: [NAME], with N = [ROWS] values.
First, compute and report: mean, median, standard deviation, quartiles Q1 and Q3, and the interquartile range.
Then identify outliers using the 1.5 x IQR rule (values below Q1 - 1.5IQR or above Q3 + 1.5IQR). List up to 15 rows: row number, value, and how many IQRs from the median.
For each outlier, state one of three likely causes: data entry error, legitimate extreme value, or unit mismatch (e.g. cents vs dollars). Say what additional column would confirm which.
Do not remove or correct anything. Only classify and ask.
Why the rule matters. The 1.5×IQR fence is a convention, not a law — but naming it makes the model's output reproducible. Run the same prompt tomorrow and you get the same list. "Find weird values" gives you a different answer every time, which is useless when you have to defend the analysis to someone else.
Prompt 3: Check whether a correlation is real or a coincidence
Ask an LLM "is there a relationship between these columns" and it will find one. It always finds one. Your job is to force it to argue against itself.
I have two numeric columns in Excel: [A] and [B], N = [ROWS].
Step 1: describe the expected direction of the relationship and why it would exist mechanically, if it does.
Step 2: compute Pearson correlation and report it with the sample size.
Step 3: name at least two confounders that could produce this correlation without a causal link (a third variable driving both, a shared time trend, a subset of rows from one period or region).
Step 4: tell me what slice of the data would test the confounders — which column to split on and what result would weaken the correlation.
Do not conclude causality. End with the single most useful split to run next.
Example. Ad spend and revenue correlate at 0.7 across 36 months. Both grew over the same period, so the correlation mostly encodes time. Split by month-over-month change instead of absolute values and the number often collapses. That is not a failure of the analysis — that is the analysis.
This is the point where the visualization finally earns its place: a scatter of month-over-month changes, not of absolute values, shows whether anything survives. For the broader chain of exploratory work — profiling, distributions, feature ideas — there is a fuller set of recipes in Data Science Prompts That Actually Work. For a deeper dive into this topic, see A/B Testing That Survives Scrutiny.
Prompt 4: Build the pivot table as a hypothesis test
A pivot table is not a summary. It is a test: "I believe region A differs from region B, and this table will show by how much." Write the prompt so the model states the hypothesis before it describes the table.
Build a pivot table specification for this data. Columns available: [LIST].
My hypothesis: [e.g. average order value differs by region by more than 20%].
Produce: rows field, columns field, values field with aggregation (sum / average / count), and any filter.
Then tell me: what result would confirm the hypothesis, what result would refute it, and which cell of the resulting table is the single most informative one.
Give me the exact Excel steps to build it (Insert > PivotTable, then field placement), not a description of the output.
Why this beats "make me a pivot table." The hypothesis forces a decision criterion. Without it, you get a table, you stare at it, and you pick whichever cell supports the story you already wanted to tell. That is how confirmation bias gets into a spreadsheet.
There is a larger library of pivot and cleaning prompts in Excel on Autopilot if you want to go deeper on that layer. For a deeper dive into this topic, see Making LLMs Speak Pandas and Polars.
Prompt 5: Only now — the chart, with its interpretation attached
This is the last prompt in the chain, and it comes with a rule: no chart without a one-sentence claim it is meant to support, and no chart without the caveat.
Based on the confirmed findings above, recommend exactly one chart.
Format: chart type, X axis, Y axis, series, and a one-sentence claim the chart supports.
Then write the caption I should put under it, including one limitation the reader should know (sample size, missing data, a period excluded).
Finally, name one alternative chart type and why you rejected it.
Example output. A line chart of monthly average order value, not total revenue — because totals mix volume and price, and the hypothesis was about price. Caption: "Average order value by month; March excluded due to a suspected duplicate import." The rejected alternative: a stacked bar by region, because it would obscure the time trend the claim is about.
When this chain works and when it falls apart
| Your situation | Does the chain work? | What to change |
|---|---|---|
| Few hundred to a few thousand rows, clean-ish export | Yes, fully | Nothing — run it in order |
| Under ~30 rows | Partly | Skip outlier statistics; with small N the IQR rule flags noise |
| Hundreds of thousands of rows | Partly | Have the model write formulas or a script instead of reasoning over a pasted sample |
| Multiple sheets with joins | No, not as-is | Describe the schema first, get the join logic confirmed, then run prompts 1–5 |
| Data you cannot paste anywhere | No | Use column names and aggregates only; never paste personal data into a chat |
That last row is not a technicality. Order-level exports routinely contain names, emails and addresses. Pasting the file into a chat window is a data transfer, and under data protection law such as the GDPR it needs a lawful basis. Strip identifiers before the first prompt, not after the fifth.
The one habit that matters more than the prompts
Every prompt above has the same shape: it asks the model to state a rule, a hypothesis, or a limitation before it produces an answer. That is the whole trick. An LLM will happily generate a confident summary of anything you hand it — including garbage — unless you force it to commit to a criterion first.
The next time you open a spreadsheet and feel the urge to insert a chart, run prompt 1 instead. Nine times out of ten the chart you were about to build would have been a picture of a data problem you had not noticed yet.