Excel Pivot Tables: A Plain-English Guide to Summarising Data Fast
If you've ever copied numbers into a calculator to total up a spreadsheet by hand, a pivot table would have done it in about ten seconds. They have a reputation for being technical and intimidating, but the core idea is simple: a pivot table takes a long list and folds it into a short summary. That's it. This guide explains when they help, when they don't, and how to build your first one.
What a pivot table actually is
Imagine a spreadsheet with one row per sale — thousands of them — each with a date, a salesperson, a region and an amount. A pivot table lets you answer questions like "how much did each region sell last quarter?" without writing a single formula. You drag "Region" into one box and "Amount" into another, and Excel does the grouping and totalling for you.
The word "pivot" just means you can spin the same data around to see it from different angles: by region, then by salesperson, then by month — all from the same source list, in seconds.
When a pivot table is the right tool
Pivot tables shine when you have:
- A long list of records — transactions, orders, timesheets, survey responses
- Categories you want to group by — by customer, by month, by product, by status
- Numbers you want to summarise — totals, averages, counts
If your question starts with "how much" or "how many, broken down by…" — that's a pivot table.
When a pivot table is the wrong tool
They're not always the answer. Skip the pivot table when:
- Your data isn't a clean list (merged cells, blank rows, headings scattered through it)
- You need a fixed, formatted report that looks identical every time — a formula-driven layout is often better
- The result needs to feed another automated process
That first point is the big one. Pivot tables need clean, tabular data: one row per record, one column per field, proper headers, no gaps. Most pivot-table frustration is actually messy-data frustration in disguise.
How to build your first pivot table
- Click any cell inside your data. Make sure it's a proper list with headers at the top and no blank rows.
- Insert → PivotTable. Excel guesses the range and offers to drop it on a new sheet. Accept that.
- You'll see a field list. These are your column headers. There are four boxes: Rows, Columns, Values and Filters.
- Drag a category into Rows (say, "Region"). You'll instantly get a list of every region.
- Drag a number into Values (say, "Amount"). Excel totals it for each region automatically.
- Want it by month too? Drag "Date" into Columns. Now you've got a grid: regions down the side, months across the top.
That's a working pivot table. Everything else — sorting, percentages, charts — is built on this same drag-and-drop foundation.
Three things that trip people up
- "My numbers are counting, not adding." Excel defaults to count when a column has any text or blanks in it. Click the value, choose "Value Field Settings," and switch it to Sum.
- "My new data isn't showing." Pivot tables don't update themselves. Right-click → Refresh. (If your data grows often, this is exactly the kind of thing worth automating.)
- "It looks ugly." Pivot tables are for answers, not presentation. Once you know what you want to show, a clean formula-based layout often presents it better.
When it's worth bringing in help
For a one-off summary, a pivot table you build yourself is perfect — and we'd genuinely encourage you to try. Where it pays to get help is when the data is too messy to pivot cleanly, when you need the same analysis every week without the manual refresh, or when the summary needs to become a polished, automated dashboard for managers.
That's the work we do — cleaning the underlying data, building the analysis, and making it refresh on its own. If you've got a spreadsheet you keep summarising by hand, email it through and we'll tell you the fastest way to get the answers you need — no obligation.