GETPIVOTDATA function

Category: Lookup and reference functions

What does this function do?

Extract data stored in a PivotTable.

Syntax:

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

• Data_field (Required): The name, enclosed in quotation marks, for the data field that contains the data that you want to retrieve.
• pivot_table (Required): A reference to any cell, range of cells, or named range of cells in a PivotTable report. This information is used to determine which PivotTable report contains the data that you want to retrieve.
• Field1, Item1, Field2, Item2: (Required): 1 to 126 pairs of field names and item names that describe the data that you want to retrieve. 

Remarks:

• Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.

• If pivot_table is a range that includes two or more PivotTable reports, data will be retrieved from whichever report was created most recently in the range.

• If the field and item arguments describe a single cell, the value of that cell is returned regardless of whether it is a string, number, error, and so on.

• If an item contains a date, the value must be expressed as a serial number or populated by using the DATE function so that the value will be retained if the worksheet is opened in a different locale. For example, an item referring to the date March 5, 1999 could be entered as 36224 or DATE(1999,3,5). Times can be entered as decimal values or by using the TIME function.

• If pivot_table is not a range in which a PivotTable report is found, GETPIVOTDATA returns #REF!.

• If the arguments do not describe a visible field, or if they include a report filter in which the filtered data is not displayed, GETPIVOTDATA returns #REF!.

Examples:

=GETPIVOTDATA("Purchases"; $C$4) = Will extract the value in cell $C$4 from the data field Purchases in a PivotTable.