Excel Pivot Table Cheat Sheet
Pivot table tasks and exactly where each one lives, plus the behaviours that surprise people.
Building
| Task | Where to find it | Worth knowing |
|---|---|---|
| Create a pivot table | Insert → PivotTable | Format the source as a Table first (Ctrl + T) so new rows are included automatically |
| Change what is summarised | Right-click a value → Summarize Values By | Defaults to Sum for numbers and Count for anything else — a stray text cell is why a Sum turns into a Count |
| Show as a percentage | Right-click a value → Show Values As → % of Grand Total | Add the same field twice to show the number and the percentage side by side |
| Add a calculated field | PivotTable Analyze → Fields, Items & Sets | It calculates on the TOTALS, not row by row — ratios can look wrong for that reason |
Working with it
| Task | Where to find it | Worth knowing |
|---|---|---|
| Refresh | Right-click → Refresh, or Alt + F5 | Pivot tables never update on their own |
| Refresh every pivot table | Data → Refresh All, or Ctrl + Alt + F5 | |
| Group dates into months | Right-click a date → Group | Requires real dates; text that looks like a date will not group |
| Group numbers into bands | Right-click a number → Group | Set the start, end and interval to build a histogram |
| Add a slicer | PivotTable Analyze → Insert Slicer | One slicer can drive several pivot tables — Report Connections |
Layout and output
| Task | Where to find it | Worth knowing |
|---|---|---|
| Flat, spreadsheet-like layout | Design → Report Layout → Show in Tabular Form | The compact default is hard to reuse as data |
| Repeat the row labels | Design → Report Layout → Repeat All Item Labels | Needed before you can filter or sort the output as a normal range |
| Stop GETPIVOTDATA appearing | PivotTable Analyze → Options → uncheck Generate GetPivotData | Lets you write ordinary cell references against a pivot table |
| Keep column widths on refresh | Right-click → PivotTable Options → uncheck Autofit column widths |
About this sheet
Most pivot-table trouble is not conceptual but locational — the command exists, in a menu you have not opened. This lists the task, the exact route, and the behaviour worth knowing: that pivot tables never refresh themselves, that a single text cell turns a Sum into a Count, and that a calculated field operates on the totals rather than row by row, which is why ratios computed that way can look wrong.
Free to use, share and republish — including commercially. Attribution isn't required, but it's what keeps these free to make, and there's one ready to paste below.
Questions
Is this cheat sheet free?
Yes, and there is no email gate. The whole table is on this page, and the download is built in your browser when you click it.
Can I print it?
Yes — the print layout drops the navigation and page furniture and keeps the tables, so it comes out as a clean reference rather than a screenshot of a web page.
What formats can I download?
Excel (.xlsx) and CSV. Both hold exactly the rows shown on this page.
Is this reference complete?
It covers the entries people actually look up. A reference you can read in one sitting beats an exhaustive dump you scroll past — the same reason the shortcuts sheet is curated rather than a 350-row export.