Excel like a finance pro.
Pivot tables · Advanced
GETPIVOTDATA
A formula that pulls a specific value out of a pivot and keeps pulling the right one when the pivot moves.
When to use it
GETPIVOTDATA pulls a specific number out of a pivot by field and item, so a formula keeps pointing at the right value when the pivot re-sorts or changes shape. Excel writes it when you click a pivot cell from a formula.
The shape of it
- How
Type = and click a pivot cell; Excel writes it. Turn off Generate GETPIVOTDATA under PivotTable Options to get plain references instead.
Worked examples
Auto-generated
Type =, click the West total in the pivot → =GETPIVOTDATA("Sales",$A$3,"Region","West")
Excel writes it for you.
Reference cells
Replace "West" with a cell reference → One formula fills a report grid
Parameterize it.
Turn it off
PivotTable Analyze, Options, Generate GetPivotData off → Clicking gives plain references instead
When you want normal references.
Worth knowing
- Point items at cells for a formula-driven report layout.
- Dates must be passed as dates: DATE(2025,3,1).
- It returns #REF! when the item is filtered out, which is a useful warning.
Where it goes wrong
- #REF! when the item is not visible in the pivot.
- The field name must match 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.