GETPIVOTDATA
Returns data stored in a PivotTable. Lets you retrieve specific summary values from a PivotTable by referencing field names rather than fixed cell addresses.
GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)Arguments
data_fieldThe name (in quotes) of the data field to retrieve.pivot_tableA reference to any cell in the PivotTable.field1, item1Optional pairs of field name and item name that describe the data to retrieve.(optional)
=GETPIVOTDATA("Sales",A3)Returns the grand total of the Sales field from the PivotTable starting at A3
=GETPIVOTDATA("Sales",A3,"Region","North")Returns the Sales total for the North region
=GETPIVOTDATA("Sales",A3,"Region","North","Year",2024)Returns the Sales total for North in 2024
- •Excel auto-generates GETPIVOTDATA when you click a PivotTable cell in a formula — disable via PivotTable Options if you prefer normal cell references
- •Returns #REF! if the requested field/item is not visible in the PivotTable
- •Field and item names are case-sensitive
- •Use it for dashboards that need stable references to PivotTable values, even if the layout changes
- •Not realizing Excel auto-generates GETPIVOTDATA formulas when you click inside a PivotTable while building another formula, which can be surprising if you actually wanted a plain cell reference instead
- •Forgetting that field and item names in GETPIVOTDATA are case-sensitive and must match the PivotTable's actual labels exactly, including any trailing spaces
- •Referencing a field or item combination that isn't currently visible in the PivotTable (due to filtering or collapsed groups), which returns a #REF! error rather than a zero or blank
INDEXCan retrieve a value from a fixed cell position instead, useful when you specifically want a plain reference rather than GETPIVOTDATA's field-based lookup.VLOOKUPRetrieves data from a regular table by matching a lookup value, a different retrieval mechanism than GETPIVOTDATA's PivotTable-specific field/item syntax.SUMIFSCan replicate some PivotTable-style aggregation directly on source data, an alternative when you'd rather not depend on the PivotTable's current layout at all.Why did Excel insert a GETPIVOTDATA formula when I just clicked a cell?
Excel automatically converts a click into a GETPIVOTDATA formula whenever you're building a formula and click inside a PivotTable. Disable this by unchecking 'Generate GetPivotData' in PivotTable Options if you want plain cell references instead.
Why does GETPIVOTDATA return a #REF! error?
The field or item combination you're requesting probably isn't currently visible in the PivotTable - it may be filtered out or hidden inside a collapsed group.
Why doesn't GETPIVOTDATA find my field even though the name looks correct?
Field and item names are case-sensitive and must match the PivotTable exactly, including spacing - copy the name directly from the PivotTable rather than retyping it.
Need to translate a formula using GETPIVOTDATA?
Use our translator to convert your complete formula