Concept
1. Why not just =B5?
If a pivot cell is referenced as =B5, it breaks when the pivot grows, is filtered or is sorted — B5 now holds a different region. GETPIVOTDATA asks for the value by name: "Amount for Region = North".
2. Let Excel write it
Type = and click a value cell inside the pivot. Excel writes:
=GETPIVOTDATA("Amount",$A$3,"Region","North")
→ 5,09,000
Arguments: the value field, any cell in the pivot (usually its top-left), then field/item pairs.
Two conditions:
=GETPIVOTDATA("Amount",$A$3,"Region","West","Category","Furniture")
→ 1,20,000
If = + click gives =B5 instead, turn it back on: PivotTable Analyze → Options dropdown → Generate GetPivotData (tick).
3. Make it dynamic
Replace typed items with cell references:
=GETPIVOTDATA("Amount",Pivot!$A$3,"Region",$B2)
Copy down next to a list of regions in your own layout — a report with your formatting, your order, and numbers that follow the pivot.
Grand total: just the value field, no pairs:
=GETPIVOTDATA("Amount",Pivot!$A$3)
4. Requirements and errors
- The item must be visible in the pivot. If North is filtered out (or a field isn't in the pivot layout), you get #REF!.
- For dates grouped by month, items are like "Apr"; for numbers, use numbers (not text).
- Wrap for clean reports:
=IFERROR(GETPIVOTDATA(...),0).
5. KPI card example
On a dashboard sheet:
| A | B | |
|---|---|---|
| 1 | Total sales | =GETPIVOTDATA("Amount",Pivot!$A$3) |
| 2 | North share | =GETPIVOTDATA("Amount",Pivot!$A$3,"Region","North")/B1 |
B1 = 10,70,000, B2 = 47.6%. Connect the pivot to a slicer, and the KPI cards change with the slicer too.
6. When to use something else
- Lots of cells, complex layouts → CUBE functions with the Data Model (Module 4:
CUBEVALUE). - Live formula tables without a pivot → SUMIFS or dynamic arrays (Module 6).
Common mistakes
Hard-coding =B5 to pivot cells. #REF! because the item is filtered out or the field isn't in the pivot. Turning off Generate GetPivotData and forgetting it's off.