Amey-Thakur Pivot tables
Pivots do in seconds what formulas take hours to build, and they depend entirely on the source being a clean flat table. Most pivot problems are source data problems.
Method
- Start from a flat table with one header row. Merged cells, blank rows, and repeated headers break pivots, and fixing the source is faster than working around it.
- Use a table or named range as the source. It expands automatically as data is added, which prevents the pivot silently missing new rows.
- Choose the summary function deliberately. Sum, count, average, and distinct count answer different questions, and the default is often not the one you want (see sql-aggregation).
- Group dates at the right level. Grouping by month or quarter is what turns a transaction list into a report.
- Understand what filters exclude. A filtered pivot showing a total that excludes rows is a common misread, so state the filter in the presentation.
- Refresh after source changes. Pivots do not update automatically, and stale pivots presented as current are a frequent error.
- Keep the pivot separate from the source. Pivots on their own sheet, with the raw data untouched, so the source stays reusable.
Boundaries
Pivots summarise and do not transform; complex reshaping belongs in Power Query. Very large sources slow refresh and may need a data model. Pivot output is a snapshot, so it needs refresh discipline when shared (see spreadsheet-collaboration).