Quick Navigation

GETPIVOTDATA Function

Excel 2007+

Summary

The GETPIVOTDATA function extracts specific data values from a PivotTable by referencing field names and items. This powerful function enables precise data retrieval from complex PivotTable summaries without manual cell referencing.

Syntax

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)

Parameters

Parameter	Type	Required	Description
data_field	`string`	Yes	Quoted name of the PivotTable field containing the desired data values
pivot_table	`reference`	Yes	Cell reference to any location within the source PivotTable
field/item pairs	`array`	No	1-126 pairs of field names and specific item values defining data location

Using the GETPIVOTDATA Function

GETPIVOTDATA transforms static PivotTable references into dynamic formulas that automatically update when the PivotTable changes. Use it to create management dashboards, automated reports, and data extraction formulas that maintain accuracy as source data or PivotTable structure evolves.

Common GETPIVOTDATA Examples

Basic Sales Retrieval

=GETPIVOTDATA("Sales",A3)

Returns total sales value from PivotTable referenced at cell A3

Region-Specific Data

=GETPIVOTDATA("Sales",A3,"Region","North")

Extracts sales for North region only

Date and Product Filter

=GETPIVOTDATA("Revenue",$A$3,"Date",DATE(2024,1,1),"Product","Widget")

Gets revenue for Widget product on specific date

OLAP PivotTable Example

=GETPIVOTDATA("Measures",A3,"[Date].[Calendar].[2024]","[Product].[Category].[Electronics]")

Complex OLAP hierarchy path extraction

Frequently Asked Questions

Select PivotTable → PivotTable Analyze tab → PivotTable → Options → Uncheck 'Generate GetPivotData'

Returns data from the most recently created PivotTable

Yes, use DATE() function or serial numbers: DATE(1999,3,5) or 36224

Common Errors and Solutions

#REF! error

Cause: Invalid PivotTable reference or non-existent field/item

Solution: Verify PivotTable location and field names match exactly

Wrong data returned

Cause: Field/item names don't match PivotTable exactly

Solution: Check spelling and use exact field names from PivotTable

Date issues across workbooks

Cause: Locale date format differences

Solution: Use DATE(year,month,day) or serial numbers

Notes

Quick entry: Type = then click PivotTable cell to auto-generate formula
Field names are case-sensitive
Supports blank cells, errors, strings, and numbers
Works with grouped dates when using DATE() format
Turn off auto-generation in PivotTable Options
Most useful for building dynamic summary reports

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365

Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+