The Excel GETPIVOTDATA function extracts a value from a PivotTable by describing it — “Sum of Sales where Region is North” — instead of pointing at a cell address. That makes reports survive pivot rearranging: when fields move, a cell reference like B4 grabs the wrong number, but GETPIVOTDATA still finds the right one. It’s also the formula Excel writes for you when you click a pivot cell while building a formula.
Syntax
| Argument | Description | |
|---|---|---|
data_field | Required | The name of the value field to retrieve, as text — "Sales" (Excel also accepts "Sum of Sales"). |
pivot_table | Required | Any cell inside the PivotTable (conventionally its top-left corner). This identifies which pivot to read. |
field1, item1, ... | Optional | Up to 126 field/item pairs that filter down to the value you want: "Region", "North". With no pairs, you get the grand total of the data field. |
The golden rule: GETPIVOTDATA can only return values that are visible in the PivotTable. Filter a region out, or remove a field, and formulas describing it return #REF!.
Why Excel writes GETPIVOTDATA for you (and how to stop it)
Type = in any cell, click a value inside a PivotTable, and Excel inserts a full GETPIVOTDATA formula instead of a plain reference like B4. That surprises people, but it’s deliberate: the generated formula keeps pointing at the same logical value even after the pivot is rearranged, refreshed, or re-sorted.
If you genuinely want plain cell references, turn the behaviour off:
- PivotTable Analyze tab → Options dropdown (left end of the ribbon) → untick “Generate GetPivotData”, or
- File → Options → Formulas → untick “Use GetPivotData functions for PivotTable references”.
Both switches control the same setting. Toggling it off only affects future clicks — existing formulas stay as they are.
Our advice: leave it on for report sheets that read from pivots (robust), turn it off temporarily when you need to fill a formula down a range (the auto-generated text hard-codes items, so it doesn’t fill — see the next section for the fix).
Worked examples: from hard-coded to flexible
Take this PivotTable (top-left cell A3, Region in rows, Product in columns, Sum of Sales as values):
| A | B | C | D | |
|---|---|---|---|---|
| 3 | Sum of Sales | Widget | Gadget | Grand Total |
| 4 | North | $12,400 | $9,800 | $22,200 |
| 5 | South | $10,100 | $11,300 | $21,400 |
| 6 | Grand Total | $22,500 | $21,100 | $43,600 |
The auto-generated version hard-codes "North" and "Widget". Swap the literals for cell references and one formula serves a whole report block — put region names in F2 and product names in G2:
Numbers and dates need care: if a field contains numbers (e.g. Year), pass a number — "Year", 2026, not "2026". For dates, use DATE() or a real date cell rather than date-looking text.
Try it: generate a GETPIVOTDATA formula
Describe the value you want from the PivotTable above — the formula writes itself, exactly as Excel would generate it.
Errors & common pitfalls
#REF! — the value isn’t visible in the pivot. The item is filtered out, the field was removed from the layout, an item or field name is misspelled, or the pivot itself moved/was deleted. GETPIVOTDATA reads the rendered pivot, not the source data.
#REF! — numbers passed as text. "Year", "2026" fails when Year holds real numbers; use "Year", 2026. Same for dates — use DATE(2026,6,1) or a date cell.
Pitfall: formulas that won’t fill down. Auto-generated formulas hard-code every item name, so dragging produces identical results. Replace the quoted items with cell references first.
Pitfall: refresh changes what exists. A refresh can remove items (no sales for that region this month), instantly turning dependent GETPIVOTDATA cells into #REF!. Wrap in IFERROR to keep reports presentable: =IFERROR(GETPIVOTDATA(...), 0).
Pitfall: calculated custom subtotals and values hidden by collapsed groups can’t be fetched — only what’s actually displayed.
Practice workbook
Frequently asked questions
How do I stop Excel inserting GETPIVOTDATA when I click a pivot cell?
Why does GETPIVOTDATA return #REF!?
How do I make GETPIVOTDATA flexible instead of hard-coded?
=GETPIVOTDATA("Sales", $A$3, "Region", F2). Now F2 can be a dropdown, and the formula fills down a list of regions. Keep the pivot anchor absolute ($A$3).How do I get the grand total of a PivotTable?
=GETPIVOTDATA("Sales", $A$3) returns the grand total of Sum of Sales. One pair gives a row or column subtotal.Is GETPIVOTDATA better than a plain cell reference to the pivot?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class