Summarize and analyze large datasets with dynamic pivot tables and cross-tabulations
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Most pivot table problems are not pivot table problems. They are shape problems: data stored wide, with months as columns and subtotals mixed into the rows. This template does the unpivoting first, then the pivot works.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Date | Region | Product | Rep | Units | Revenue |
| 2 | 2026-07-03 | EU | Standard | A. Duval | 12 | 3,600 |
| 3 | 2026-07-03 | EU | Premium | A. Duval | 4 | 2,800 |
| 4 | 2026-07-04 | NA | Standard | K. Owens | 9 | 2,700 |
| 5 | 2026-07-04 | NA | Premium | K. Owens | 7 | 4,900 |
Why each one is written the way it is
Power Query: Select month columns → Unpivot Columns
The single most useful transformation in reporting. Twelve month columns become two: attribute and value. Everything downstream gets simpler.
=FILTER(Raw, (Raw[Type]<>"Subtotal")*(Raw[Region]<>""))
Strips subtotal rows and blank separators before the data reaches the pivot — the two things that silently double-count.
=TRIM(CLEAN([@Key]))
Removes trailing spaces and non-printing characters. Keys that look identical but do not match are almost always this.
=SORT(UNIQUE(Data[Region]))
A spilled distinct list for validation dropdowns, so new categories cannot be typed inconsistently.
A worked example with real numbers
A sales report built on a monthly export never tied back to the accounting system, always by a few percent. The export included quarterly subtotal rows styled like data. The pivot summed them alongside the detail, double-counting one month in three. Filtering rows where the type column was blank removed the discrepancy entirely — and the same filter has caught two format changes in the export since.
Tab by tab
| Tab | Contents |
|---|---|
| Raw | The export exactly as received, never edited. |
| Clean | Unpivoted, trimmed and filtered — the pivot source. |
| Pivot | Pre-built pivots by region, product and period. |
| Checks | Row counts and totals reconciled against the source. |
| README | The transformation steps, in order, and why each exists. |
What is included, and what to look at next
Specific to this workbook, not generic
Almost always because it is wide rather than long. A pivot needs one row per observation with each attribute in its own column. Months across the top means the pivot cannot group by date.
Turning columns into rows. Twelve month columns become a Month column and a Value column, so date becomes a dimension you can group, filter and compare by.
For a one-off, no — TOCOL or a helper-column approach works. For anything you refresh, yes: Power Query replays the transformation on new data instead of you redoing it.
"This pivot table generator has transformed our data analysis process. We can now create complex reports in minutes instead of hours."