Excel like a finance pro.
GETPIVOTDATA
Lookups & referencesExcel inserts it when you click a pivot cell. Everyone has met it; nobody chose it.
Difficulty
1What is it?
Type = and click inside a pivot table, and instead of a tidy B7 Excel writes a long GETPIVOTDATA formula. Most people delete it in irritation. It's worth understanding instead, because it has a real advantage: it asks for a value by its LABELS — Sales, September — rather than by position, so it keeps returning the right number when the pivot is rearranged, filtered or grows a new row. A plain reference into a pivot silently starts pointing at the wrong cell the moment the layout moves. The one thing it can't do is find an item the pivot isn't currently showing.
2What it looks like
GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)- data_field
- The value field to return, in quotes — "Amount".
- pivot_table
- Any cell inside the pivot. It just identifies which pivot.
- field, item pairs
- The labels that locate the value: "Dept", "Sales", "Month", "Sep". Point these at cells rather than typing them and the formula becomes reusable.
3When you use it
- Pull a figure from a pivot into a report that survives the pivot being rearranged.
- Build a summary table that reads labels from its own row and column headers.
- Reference a total that moves position as the data grows.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Move the pivot around. The plain reference follows the cell; GETPIVOTDATA follows the label.
=GETPIVOTDATA("Amount", $A$3, "Dept", "Sales")=B5 (plain reference) → $79,500| A | |
|---|---|
| 1 | Result |
| 2 | =B5 (plain reference) → $79,500 |
| 3 | GETPIVOTDATA → $79,500 |
| 4 | Both agree — for now. Rearrange the pivot. |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#REF!The item isn't visible in the pivot right now — filtered out, collapsed, or it simply has no data this period.Fix: Wrap in IFERROR(…, 0) where a missing item genuinely means zero.
Can't copy it across a tableThe items are typed in as literal text, so every copy asks for the same thing.Fix: Replace "Sales" with a cell reference to the row label. That one change is what makes it usable.
Excel keeps writing it when you don't want itThe Generate GetPivotData option is on.Fix: PivotTable Analyze → Options → untick Generate GetPivotData. Keep it on once you see why it's useful.
#REF! after a refreshA field was renamed in the source data.Fix: Field names are matched as text; update the formula to the new name.
6Better functions & alternatives
- A plain cell reference — Shorter, and wrong the moment the pivot's layout changes — which is exactly the failure GETPIVOTDATA exists to prevent.
- SUMIFS on the source — Skips the pivot entirely and never goes stale. Often the better choice for a report that doesn't need the pivot at all.
- GROUPBY / PIVOTBY — A pivot as a formula, so there's nothing to reference into.
Want GETPIVOTDATA already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
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.