GETPIVOTDATA function
GETPIVOTDATA reads a specific value from a PivotTable so the result stays correct when the pivot is rearranged. Excel creates it automatically when you click a pivot cell from a formula.
Example
=GETPIVOTDATA("Sales",A3)
Expected result: n/a Not yet checked in desktop Excel
Syntax
GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)
Arguments in [square brackets] are optional.
-
data_field(required) - Name of the value field, in quotes.
-
pivot_table(required) - Any cell inside the PivotTable.
-
field1, item1(optional) - Optional pairs narrowing down which total to fetch.
Returns: A value from a PivotTable.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=GETPIVOTDATA("Qty",Items!B1) | #REF! | No PivotTable at the reference |
=GETPIVOTDATA("Qty",Items!B1,"Category","Smoking") | #REF! | With field pair, no PivotTable |
The Items sheet these formulas read
B
1
Item
Items
Watch out for
- Returns #REF! if the requested item is not visible in the pivot.
- Turn off Generate GetPivotData in PivotTable options if you prefer plain cell links.
Where it works
- Excel
- GETPIVOTDATA is in every version of Excel from 2007 on.
- Google Sheets
- Same name, but behaves differently. Exists in Sheets, but the argument layout differs, so formulas do not transfer unchanged.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .