Excel like a finance pro.
Pivot tables · Advanced
Flattening a pivot to a table
Turns pivot output into a plain table that formulas can reference.
When to use it
Turning pivot output into a plain table that formulas can reference: tabular layout, repeated labels, subtotals off, then Paste Values.
The shape of it
- How
Tabular layout, repeat labels, subtotals off, then copy and paste as values.
Worked examples
Set the layout
Design, Report Layout, Tabular; Repeat All Item Labels; Subtotals off; Grand Totals off → A clean rectangular table
The layout recipe.
Paste values
Select the pivot, Ctrl + C, Ctrl + Alt + V, V → A static copy
Freeze it.
Make a Table
Ctrl + T on the pasted block → A Table for lookups and formulas
Use it.
Worth knowing
- GETPIVOTDATA references the live pivot instead when the numbers must update.
- Power Query can produce the same summary as a refreshable table.
- Name the pasted Table so formulas read well.
Where it goes wrong
- The frozen copy does not update.
- Blank cells in the pivot become empty cells in the copy.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.