Excel like a finance pro.
Lookup function
GETPIVOTDATA
Pulls a specific value out of a pivot table by field and item names.
When to use it
Pulls a specific number out of a pivot table by field and item, so a report stays right when the pivot re-sorts or filters. Excel writes it for you when you click a pivot cell from a formula.
The shape of it
- Syntax
=GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)
Worked examples
Grand total
=GETPIVOTDATA("Sales",$A$3) → 1,250,000
The grand total of the Sales field, anchored at the pivot's top-left cell.
One item
=GETPIVOTDATA("Sales",$A$3,"Region","West") → 425,000
One region's total.
Two fields
=GETPIVOTDATA("Sales",$A$3,"Region","West","Product","Widget") → 96,000
Two field/item pairs narrow it further.
Worth knowing
- Excel writes this automatically when you click into a pivot from a formula. Turn that off under PivotTable Options if you want plain references.
- Point the item arguments at cells so one formula fills a whole grid.
- Turn off the automatic insertion under PivotTable Analyze, Options, Generate GetPivotData if you prefer direct references.
- Dates must be passed as dates: DATE(2025,3,1), not "Mar".
Where it goes wrong
- #REF! when the item is not visible in the pivot (filtered out or collapsed).
- Field names must match the pivot exactly, including "Sum of".
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.