Using the Excel GETPIVOTDATA Function


The Excel GETPIVOTDATA function returns data that is stored in a pivot table.


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

Syntax Breakdown

Data Field
Required. Name for the data field that contains the data you wish to retrieve. The name must be enclosed in quotation marks.

Pivot Table
Required. Reference to any cell, range, or named range in a pivot table. This information is used to determine which pivot table contains the data that you wish to retrieve.

Field1, Item1, Field2, Item2
Optional. Field and item pairs that describe the data you wish to retrieve. The function accepts up to 126 field and item pairs. Field names and names for items other than dates and numbers must be enclosed in quotation marks.

If item contains a date, the date should be entered in serial number format or by using the DATE function.

Usage Notes

GETPIVOTDATA is used to query a pivot table and retrieve data based on the pivot table structure. Field and item pairs can be used to filter and limit data retrieved based on the structure of the pivot table. If pivot_table is a range that contains two or more pivot tables, the function retrieves data from whichever report was most recently created.

The function is automatically generated when you reference a value cell in a pivot table. You can avoid this in two ways:

1) Type the address of the cell you want as opposed to clicking

2) Completely disable the feature by disabling "Generate GETPIVOTABLE" in the menu at Pivot Table Tools > Options > Options.

The GETPIVOTDATA function returns the following errors:

1) #REF! if the pivot_table argument is not a range in which a pivot table is found

2) #REF! if the arguments do not describe a visible field, or if they include a report where data isn't displayed due to a filter.