Practice free

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.

FormulaExcel showsCase
=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

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 .