Learning objectives
By the end you can:
- Build a PivotTable from a Table source and place fields deliberately into the four areas (Filters, Columns, Rows, Values) explaining each from first principles (a pivot is an interactive
GROUP BY+ aggregate). - Change a value field's Summarize Values By across Sum, Count, Average, Max, and Min, and explain precisely why a numeric column defaults to Sum while a column containing any blank or text silently defaults to Count, the count-when-you-meant-sum trap.
- Apply Show Values As: % of Grand Total, % of Column Total, % of Row Total, Running Total In, and % Difference From, and state exactly what question each one answers, knowing it changes display only.
- Group dates into months, quarters, and years, and numbers into equal-width bins, and know that grouping is a property of the shared cache, so it regroups every pivot built on the same source.
- Insert slicers and timelines, multi-select and clear them, and connect one slicer to many pivots via Report Connections, understanding that this requires a shared pivot cache.
- Use GETPIVOTDATA deliberately for stable single-cell KPIs, and turn off its auto-generation when you need an ordinary, draggable cell reference.
- Assemble a pivot-driven P&L / KPI view (category lines down, periods across, a common-size (% of column) presentation, a slicer, and a PivotChart) and know the one honest limit of a calculated field.
- Diagnose and cure the pivot-cache staleness trap by Refreshing (
Alt+F5/Ctrl+Alt+F5), and explain why a Table source auto-expands where a fixed range silently freezes. - Recognise and prevent the five classic pivot failures, stale cache, non-growing range, GETPIVOTDATA breaking a dragged formula, cache-shared grouping, and the silent Count default.
Prerequisites & connections
Builds on. EX0.01 (Interface, Navigation & Data Entry, the Table discipline, Ctrl+T; a pivot's source should always be a Table, for reasons this module makes concrete). EX1.01 (Formulas II, the SUMIFS/COUNTIFS aggregation family and the "one fact or a sum across many?" question; a PivotTable is the interactive, drag-driven generalisation of exactly that GROUP BY, so everything you learned about criteria and totals transfers). EX1.02 (Lookups Mastery: GETPIVOTDATA is, quite literally, a structural lookup into a pivot, matching by field/item label rather than cell position, and it inherits the same "match by name, not by counted offset" robustness you drilled there). You also lean on EX1.01's date handling: a pivot can only group real dates (serial numbers), never text that merely looks like a date.
Feeds forward. EX3.02 (Charts & Dashboards) turns the slicer-wired pivots here into a full interactive dashboard, and the "one slicer, many pivots" pattern is its backbone. The data model / Power Pivot work later in the track replaces the single-table pivot cache with a relational model and DAX measures, but the mental model of drag a field, get an aggregate is identical, so this module is the on-ramp. On the analyst track, the pivot is the everyday engine of management reporting: M2's KPI packs, the variance analysis of a monthly close, the revenue-by-segment cuts a board deck needs, all are pivots over a transaction log. The pivot-driven P&L you build here is the seed of every "actuals by month" view an FP&A analyst maintains.
The pivot engine: rows, columns, values, filters
A PivotTable answers one shape of question: "for each combination of these categories, what is the aggregate of this number?" Give it a flat table (one row per transaction) and it collapses those rows into a compact summary. Every cell of that summary is an aggregate, usually a sum, of all the source rows that share a category combination. That is precisely SQL's SELECT category, SUM(value) ... GROUP BY category, except you build it by dragging field names into boxes and you can rearrange it live.
There are four drop zones in the Field List, and understanding what each does is the whole foundation: