GETPIVOTDATA in Excel: pull one number out of a pivot table

Click a pivot cell while writing a formula and Excel writes GETPIVOTDATA(...) instead of =B5. It looks alarming. It is actually useful — it keeps pointing at “North’s sales” even when the pivot re-sorts.

What Excel wrote

=GETPIVOTDATA("Sales", $A$3, "Region", "North")

• "Sales" — the value field.
• $A$3 — any cell inside the pivot (identifies which pivot).
• "Region", "North" — the row you want.

Make it use a cell instead of “North”

Type the region in E1, then:

=GETPIVOTDATA("Sales", $A$3, "Region", E1)

Change E1 to South — the number follows. This is how dashboards pull from pivots.

Two conditions

=GETPIVOTDATA("Sales", $A$3, "Region", E1, "Product", E2)

Check it worked

Sort the pivot differently. Your formula still shows North’s number; a plain =B5 would now show whoever moved into B5.

#REF! error

The combination does not exist in the pivot (no North + Bag row), or the field is collapsed. Wrap in IFERROR, or expand the pivot.

Turn it off (just want =B5)

Click in the pivot › PivotTable Analyze › Options ▾ › untick Generate GetPivotData. Or type the reference by hand instead of clicking.

Pivot basics: make a pivot table.