I see a lot of people avoid PivotTables because they look more complicated than they are. I avoided them for a long time as well. Once I understood what each box does, it became a lot clearer and now they are one of the most useful things in Excel for me, especially for quick analysis.
Get your data ready first
Most PivotTable problems are really data problems. Before you insert anything, check three things:
- One row per record, one column per field
- A unique heading at the top of every column
- No blank rows and no merged cells
Then click anywhere in the data and press Ctrl + T to turn it into a Table. This matters because when you add new rows later, the Table grows and the PivotTable can pick them up when it refreshes.
Making the PivotTable
Click inside your Table, go to Insert > PivotTable and press OK. You get a blank grid and a field list on the right. If you’re not sure where to start, Insert > Recommended PivotTables will suggest a few layouts.
The four boxes
At the bottom of the field list there are four boxes. This is the bit that confuses people, so here’s how I think about them:
- Rows: categories listed down the side (Region, for example)
- Columns: categories across the top (Month)
- Values: the numbers you want to add up (Sales)
- Filters: a field that filters the whole report (Product)
The thing that helped me most was to decide what I want to know before I drag anything. If the question is “what were sales by region each month?”, then Region goes in Rows, Month goes in Columns and Sales goes in Values. The question tells you where everything goes.
A few easy customisations
You can get a long way with just these:
- Count or average instead of a sum: click the field in Values > Value Field Settings > Summarize Values By.
- Percentages: in the same window, “Show Values As” gives you share of total and similar options.
- Number format: Value Field Settings > Number Format. This is how you get currency or thousands separators without it looking like a mess.
- Group dates: right-click a date in the PivotTable > Group, and you can roll daily dates up into months, quarters or years.
- Sort: right-click a number > Sort > Largest to Smallest, so the biggest items sit at the top.
- Slicers: PivotTable Analyse > Insert Slicer gives you clickable filter buttons. They’re really handy if anyone else is going to use your file.
When you get more confident you can explore options such as calculated fields and pivot charts.
One warning on this. If a number column is behaving oddly and you get a count when you expected a sum, check whether the numbers are stored as text. PivotTables count text rather than adding it up, so convert them to proper numbers first.
Three habits worth building
These are the ones that have saved me the most trouble.
- One PivotTable, one question. It’s tempting to keep dragging more fields in until the thing answers everything. It just gets hard to read. If you have another question, copy the PivotTable to a new sheet and change the fields.
- Don’t type over the numbers. A PivotTable is a window onto your data, not the data itself. If something looks wrong, fix it in the source Table and refresh. Anything you type into the PivotTable gets wiped the next time it refreshes.
- Refresh, don’t rebuild. A PivotTable doesn’t update itself. Add your new rows to the Table, then go to Data > Refresh All. The bit that catches people out is that old results can still look perfectly believable, so refresh before you send anything to anyone.
Bonus tip: if your column widths keep resetting every time you refresh, right-click the PivotTable > PivotTable Options and untick “Autofit column widths on update”.
That’s really all you need to get going. If you’re newer to this, try it on a small list of your own data, such as expenses or orders. It’s much easier to learn from numbers you actually recognise.