Excel Pivot Tables: A Genuinely Useful Introduction

Pivot tables have a reputation for being either magic or impenetrable, with very little in between. The reason is almost always the data going in rather than the feature itself. Get the layout right and a pivot table takes about thirty seconds to build and answers questions you would otherwise write formulas for all afternoon.

This is an introduction aimed at getting you to a working, useful pivot table, and at explaining the handful of things that make people give up on them.

Get the data right first

Almost every pivot table problem is a data layout problem. What a pivot table wants is a plain rectangular list: one header row at the top, one record per row underneath, and nothing else in the way.

• One header row only. Two stacked header rows will produce column names like Column1 and confusion.
• No blank rows or columns inside the range. A blank row stops the automatic range detection dead.
• No merged cells. Ever, anywhere near data you want to analyse.
• No subtotals in the source. The pivot table does the totalling. Leaving your own in double-counts everything.
• Consistent types per column. A date column with three text entries in it will not group by month.

If your export arrives as a cross-tab. A column per month with values in a grid. You need to unpivot it first. Power Query does this in two clicks and is the correct tool for the job.

Building your first one

That is the entire mechanic. Everything else is refinement.

The four zones, and what they do

A field in Values defaults to Sum for numbers and Count for text. If your numbers arrive as Count when you wanted Sum, the column contains text somewhere. That is the diagnosis, not a pivot table quirk.

Counting things, which is what IT people usually want

You often do not want to sum anything at all. You want to know how many. Drag the same field into Values that you used in Rows and Excel counts it. Then click the field, choose Value Field Settings, and confirm it is set to Count.

For distinct counts. How many unique users, not how many rows. Tick Add this data to the Data Model when you create the pivot table. Distinct Count then appears in the summarise-by list. It is hidden away, and it is the answer to a surprising number of questions.

Show values as. Percentages without formulas

Right-click a value, Show Values As, and pick from the list. Percentage of grand total, percentage of parent row, running total, difference from previous. All of these would be fiddly formulas outside a pivot table and are one click inside it. This is the feature most people never find.

Slicers and grouping

A slicer is a filter with buttons. Select the pivot table, Insert, Slicer, choose a field. Anyone can now click through the report without knowing anything about Excel, which is exactly what you want when you hand it to a manager.

Grouping is the other one worth knowing. Right-click a date field in Rows and choose Group to roll individual dates up into months, quarters or years. Right-click a numeric field and you can band it. Ages into ten-year bands, costs into price brackets. It takes seconds and turns a wall of rows into something readable.

The mistakes that make people give up

• Building on a range instead of a Table. Add a row next month and it is not included. Always Ctrl+T first.
• Forgetting to refresh. Pivot tables show a snapshot. Right-click, Refresh, or in Table properties, tick refresh on file open.
• Typing into a pivot table. You cannot. If you need to annotate, do it in cells beside it, well clear of where it will expand.
• Sum showing as Count. Text is hiding in your numeric column, often a stray dash for a blank.
• Deleting source rows and expecting fields to disappear. Old items linger in filters. Set Retain Items to None in PivotTable Options.

Glossary

Frequently asked questions

Why does my pivot table show Count when I want Sum?

The column contains at least one text value, often a dash, N/A or a space used to mean blank. Find and clear it, refresh, and the default changes to Sum.

Why does new data not appear?

Either the pivot is built on a fixed range rather than a Table, or you have not refreshed. Convert the source with Ctrl+T and right-click the pivot to refresh.

How do I count unique values?

Tick Add this data to the Data Model when creating the pivot table, then choose Distinct Count in Value Field Settings. It is not available otherwise.

Can I use a pivot table on data from another file?

Yes, though the cleaner approach is to pull the other file in with Power Query first and pivot the result. That way the connection is documented and refreshable.

Do pivot tables slow down large workbooks?

Less than the equivalent formulas would. A pivot table over a hundred thousand rows is comfortable; the same analysis in nested SUMPRODUCT formulas would not be.

Disclosure: this post may contain affiliate links. If you buy through one of them, we may earn a small commission at no extra cost to you. We only recommend products we’ve tested or genuinely rate.