PivotTables — Excel guide
PivotTables summarize thousands of rows by drag-and-drop — the fastest route from raw data to answers. This sheet covers the mechanics that trip people up.
Build one in four drags
Insert ▸ PivotTable, then drag fields: Rows = the groups (Region), Columns = optional cross-cut (Quarter), Values = what gets aggregated (Sales), Filters/Slicers = the audience controls. Region in Rows + Sales in Values = sales per region, instantly.
Why does it say COUNT instead of SUM?
All-numeric columns default to Sum; any text or blank in the column flips the default to Count. The surprise COUNT is a data-quality smoke alarm. Change it via right-click ▸ Value Field Settings (Sum, Average, Max, and more live there).
Pivots don't auto-update
They serve a cached snapshot until you Refresh (Refresh All / Ctrl+Alt+F5 for the whole workbook). Two habits prevent stale numbers: build on a Table (Ctrl+T) so new rows join automatically, and tick PivotTable Options ▸ Data ▸ 'Refresh data when opening the file'.
Show Values As
The same field dropped into Values twice can show monthly AND cumulative: right-click ▸ Show Values As ▸ Running Total, % of Grand Total, Difference From — cache-side math, no formulas.
Grouping and Top-N
Right-click a date field ▸ Group → months/quarters/years. Same menu on numbers makes histogram bins (0-500, 500-1000). The row filter's Value Filters ▸ Top 10 ranks by the aggregated measure and re-ranks on every refresh.
Drill-down and slicers
Double-click any value cell to get a new sheet of the underlying rows (a static copy). Slicers filter visually — and Report Connections wires one slicer to every pivot on the same cache for one-click dashboards.
Gotchas worth knowing
Same-source pivots share a cache, so grouping in one appears in the other. A "(blank)" row means the source has empty cells in that field. Need distinct counts? Tick 'Add this data to the Data Model' when creating. Exporting? Report Layout ▸ Tabular + Repeat All Item Labels makes flat, machine-readable output.