How to Summarise in Excel: PivotTables Before AI

"Summarise this in Excel" is actually two different jobs wearing one phrase. One is condensing three thousand rows into totals and breakdowns you can trust — a job Excel already has a purpose-built tool for. The other is turning that finished table into two sentences someone can read without opening the file, which is where an AI tool genuinely helps. Mixing up which tool does which job is the whole problem, and it happens constantly because both get called "summarising".

The tool built for the first job: PivotTable

A PivotTable is not a formula you write — it is a structure you build by dragging fields into four boxes: Rows, Columns, Values and Filters. Put "category" in Rows and "amount" in Values, and a table of three thousand transaction rows becomes a dozen lines showing total spend per category, recalculated instantly if the underlying data changes. That is the actual summarising step for most spreadsheet work, and it is why so many searches for "summarise in Excel" are really searches for a feature the person has never used, not a formula they are missing.

The function version of the same idea is SUBTOTAL, and it solves a specific problem SUM does not: =SUBTOTAL(109, D2:D500) totals only the visible rows in a range. Filter that range down to one region first, and SUM still adds the hidden rows behind the filter — SUBTOTAL does not. That distinction matters the moment you filter a table to check one slice of it and then want a total for exactly what you are looking at, nothing more.

A worked example

Weak approach: paste three thousand rows of transactions into a chat tool and ask it to "summarise this."

Better approach: build the PivotTable first — category in Rows, amount in Values, filtered to the quarter you care about. Once that table exists and you trust the numbers in it, that is the point to bring in AI: "Here is a finished PivotTable showing total spend by category for Q3. Write two sentences for a slide describing which category grew the most and roughly by how much. Do not recalculate anything — use only the numbers already in the table."

The second version asks the model to do what it is actually good at — turning a table into readable prose — while keeping every number in a cell you can point to and re-check. How to write a prompt that works on the first try covers the same split for any task: separate what you want the model to describe from what you want it to calculate. Both OpenAI’s and Anthropic’s own prompting documentation give the same guidance — state the task and its boundary together.

Where this goes wrong

The failure that actually costs something is skipping the PivotTable and asking an AI tool to add up the raw rows itself — "what’s the total spend by category across these three thousand rows." This is a documented property of how these models generate text: they predict a plausible-sounding continuation, and a plausible total is not the same as a correct one on a sheet with three thousand rows to get right. A PivotTable’s numbers come from an aggregation engine that does the same arithmetic every time. An AI-generated total is a guess wearing a formula’s clothing until you check it.

There is a quieter version of the same mistake: previewing the number you expect before asking. "This should be around £40,000 total, right?" — research on how these models respond to a stated expectation has found they tend to agree with what the question implies rather than checking independently. Ask what the table shows, not whether it confirms a figure you already have in mind.

Let the PivotTable do the counting. Let AI describe what the PivotTable already shows. Keep them in that order.

Checks before you trust either one

• —Cross-check one PivotTable subtotal against a plain SUMIFS formula for a single category before trusting the whole table — a field dropped in the wrong box silently changes what is actually being summed, and one manual check catches it.
• —If AI wrote a caption or a summary sentence about a finished table, re-read the specific figures it quoted against the table itself before they go in a deck someone else will repeat.
• —If a formula behind a subtotal looks off, the formula bar shows exactly what a cell is calculating — worth a look whether the formula came from you, a colleague, or an AI suggestion.
• —A PivotTable and a chat summary both answer "what happened." Neither tells you whether a gap between two groups is a real difference or noise — ANOVA in Excel is the tool built for that question.

When the summary needs to update itself

A PivotTable answers a one-off question well, but it does not refresh on its own when the source sheet changes — you have to right-click and refresh it, or someone has to remember to. If the same summary needs to stay current every week without anyone doing that by hand, that is a different problem: data automation tools covers what it actually takes to keep a workflow synced to a sheet that keeps changing, rather than re-building the same PivotTable from scratch each time. Can you upload Excel to ChatGPT covers the related but distinct case of handing a whole file to a chat tool directly — useful for a one-off question, and a snapshot the moment you upload it, not a live summary.

What to do Monday

Take one table you currently reduce to a total by scrolling and eyeballing it, and build an actual PivotTable from it — category or region in Rows, the number you care about in Values. That alone replaces most of what people mean by "summarise this in Excel." Then, separately, take the finished table and ask an AI tool for a two-sentence description of it for a slide or an email, with the numbers already fixed in the table where you can check them. Quick Analysis in Excel is worth opening first if you are not sure a PivotTable is the right amount of tool for a small range — it does a lighter version of the same job in one click.

Once a summary is genuinely something you hand to someone else regularly, AI report generator covers the brief that turns a generated writeup into something checkable rather than just fluent. That gap between the routine counting and the judgement about what is worth flagging is exactly where Stanford GSB’s own research on spreadsheet-heavy roles says the work is actually shifting. Coursium teaches that practical layer — which tool does the counting, which does the describing, and what to check before you trust either. Stay ahead of AI by learning it on your phone.