Pull a Value from a PivotTable (GETPIVOTDATA)

All versionsGETPIVOTDATA

Reference a specific number inside a PivotTable without it breaking when the pivot reshapes. GETPIVOTDATA fetches a value by its field names — not its cell position — so your dashboard formulas stay correct.

Quick formula: to get the East region total from a pivot:

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

The first argument is the data field; then the pivot anchor; then field/item pairs that locate the cell. Position-independent.

Functions used (tap for the full reference guide):

GETPIVOTDATA SUMIFS

The example

Fetching one region’s total from a sales pivot.

	A	B
1	Lookup	Result
2	East Sales	$42,000

The formula

Fetch by field, not by cell:

=GETPIVOTDATA("Sales", $A$3, "Region", "East") // survives pivot reshaping

How it works

GETPIVOTDATA addresses pivot values by name:

Data field first — the value you summarized (e.g. "Sales").
The pivot anchor (any cell in the pivot, usually its top-left).
Then field/item pairs — "Region", "East" — that pinpoint the value.
Because it locates by labels, moving or refreshing the pivot doesn’t break the formula. (Tip: typing = then clicking a pivot cell auto-writes GETPIVOTDATA.)

Hate the auto-GETPIVOTDATA? Turn it off via PivotTable Analyze → Options → uncheck “Generate GetPivotData” if you’d rather click and get a plain cell reference. And if you don’t even need a pivot, SUMIFS against the raw data gives the same totals with full flexibility.

Try it: interactive demo

Live demo

Pick a region; fetch its total.

Region

Total:

Variations

Dynamic item

Reference a cell for the item:

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

Two fields

Region and month:

=GETPIVOTDATA("Sales",$A$3,"Region","East","Month","Jan")

SUMIFS instead

No pivot needed:

=SUMIFS(sales, region, "East")

Pitfalls & errors

Field/item names must match exactly. A typo or a changed field name returns #REF!.

The item must be visible. If a filter hides “East,” GETPIVOTDATA can’t fetch it — it errors.

Anchor stays put. Point at the pivot’s top-left; if the pivot moves sheets, update the anchor.

Practice workbook

📊

Download the free Pull a Value from a PivotTable (GETPIVOTDATA) practice workbook

Source data with a SUMIFS equivalent (and GETPIVOTDATA on the page), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I reference a PivotTable value in a formula?

Use GETPIVOTDATA: =GETPIVOTDATA("Sales", $A$3, "Region", "East"). It fetches by field/item names, so it survives the pivot being reshaped or refreshed.

How do I make GETPIVOTDATA use a cell for the item?

Replace the literal with a cell reference: =GETPIVOTDATA("Sales", $A$3, "Region", D1), so changing D1 changes which value is fetched.

How do I stop Excel auto-creating GETPIVOTDATA?

PivotTable Analyze → Options → uncheck "Generate GetPivotData" to get plain cell references when you click pivot cells.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: GETPIVOTDATA · SUMIFS