Excel Pivot Tables: A Worked Guide to Summarising Real Data

A pivot table answers questions about a list. Which region sold most, what each customer spent this year, how many orders came in per month — all from the same raw data, without writing a formula or sorting anything by hand.

Most guides explain what the buttons do. This one uses one example throughout, so the buttons mean something.

The example

Say you have a sheet of orders, one row per order:

Date	Customer	Region	Product	Amount
04/01/2026	Hartley Ltd	North	Service plan	1,200
04/01/2026	Brooke & Co	South	Installation	3,400
07/01/2026	Hartley Ltd	North	Installation	2,750

Two thousand rows of that is unreadable. Every question you have about it — totals by customer, by month, by region — is a pivot table.

Get the source data right first

Nearly every pivot table problem is a source data problem. Before inserting anything, the sheet must be:

• One header row, with every column named. Blank headers break the field list.
• No merged cells. Anywhere.
• No blank rows or columns inside the data. Excel uses them to decide where your data stops.
• One kind of thing per column. Dates in the date column, numbers in the amount column — not "1,200" typed as text, and not "N/A" sitting in a numeric column.
• No subtotal rows. The pivot table produces the subtotals; leaving your own in double-counts them.

Then select any cell in the data and press Ctrl + T to turn it into a proper Excel Table. This is the step most guides omit and it is the one that saves the most pain: a pivot table built on a Table automatically includes new rows when you add them. Built on a plain range, it silently keeps reporting on the old boundary, and next month's figures quietly go missing.

Build it

Click inside the data, then Insert → PivotTable → OK. You get an empty frame and a field list.

Four drop zones, and the whole tool is knowing what each is for:

• Rows — what you want a line for. Drag Customer here.
• Values — what you want measured. Drag Amount here.
• Columns — what you want to split those lines across. Drag Region here.
• Filters — what you want to narrow the whole thing by.

That is total spend per customer, broken down by region, in about ten seconds. Drag Region out and drop Product in its place and it is the same question asked a different way. The rearranging is the point — "pivot" is a description of what you do, not of what it is.

Group dates into months

Drag Date into Rows and you get one line per day, which is rarely useful.

Right-click any date in the pivot table and choose Group, then pick Months and Years. Now it is a monthly summary, and adding Years alongside gives you this year against last.

If Group is greyed out, one of your date cells is text rather than a real date. That is the source data problem again, and it is worth fixing properly rather than working around.

Change what Values actually does

Excel sums numeric fields and counts everything else. To change it, right-click a value and choose Summarise Values By: Sum, Count, Average, Max, Min.

A trap worth knowing: if a column you expect to Sum shows a Count instead, there is text somewhere in it — a stray "n/a", or numbers imported as text. Excel is telling you about a data problem.

The genuinely useful menu is the one below it, Show Values As:

• % of Grand Total — each customer's share of revenue, instead of a figure you then have to divide in your head.
• % of Column Total — the same, within each region.
• Running Total In — cumulative revenue through the year.
• % Difference From — this month against last, growth calculated for you.

These are the ones that turn a summary into an answer, and they are the ones most people never open.

Slicers, for anyone who is not you

PivotTable Analyze → Insert Slicer puts clickable buttons beside the table — region, product, salesperson — that filter it on click.

Use them whenever someone other than you will read the report. Filter dropdowns hide what has been filtered; slicers show it. Half of all "the numbers are wrong" reports are a filter someone forgot was on.

The things that go wrong

• New data does not appear. The source was a fixed range. Convert to a Table with Ctrl + T. Failing that, right-click → Change Data Source, and refresh with Alt + F5 — pivot tables do not update on their own.
• Names appear twice. "Hartley Ltd" and "Hartley Ltd " with a trailing space are two customers as far as Excel is concerned. Fix it at source, and stop free-typing names.
• Count where you wanted Sum. Text in a number column.
• Dates will not group. They are text, not dates.
• Referencing pivot cells in a formula. Excel writes a GETPIVOTDATA formula that breaks the moment the layout changes. Turn it off under PivotTable Analyze → Options, or point the formula at the source data instead.
• Building the report on top of a filtered view. The pivot table reads the whole source, not what is visible. Filter inside the pivot table, not outside it.

When a pivot table is the wrong tool

Pivot tables summarise a list. That is precisely what they are for, and they are excellent at it. They are not a reporting system, and the difference shows up in a few recognisable ways:

• You are pasting new data into the source sheet every week to keep the report alive.
• You are pivoting across several workbooks and reconciling them by hand.
• The source sheet is now the master record of something — customers, jobs, stock — rather than an export of it.
• Two people cannot work on it at once without one of them getting a read-only copy.
• The workbook has become slow enough that people avoid opening it.

Each of those is the same underlying signal: a spreadsheet is being used as a database. Pivot tables are not the problem there — they are usually the last thing holding it together — but no amount of pivoting fixes data that has nowhere proper to live.

The honest test is what would happen if the file were lost or the person who maintains it left. If the answer is uncomfortable, the fix is a database with reporting on top, not a better spreadsheet.

Our Microsoft Excel services page covers both improving what you have and moving beyond it, and a free system review will tell you which side of that line you are on.