The Excel GETPIVOTDATA function can query a pivot table and retrieve specific data based on the pivot table structure, instead of cell references.
Syntax:= GETPIVOTDATA (data_field, pivot_table, [field1, item1], …)
The GETPIVOTDATA function syntax has the following arguments:
- Data_field (required argument) – This is the worksheet information from which we intend to remove nonprintable characters.
- Pivot_table (required argument) – This is a reference to a cell, range of cells, or named range of cells in a pivot table. We use the reference to specify the pivot table.
- Field1, Item1, Field2, Item2 (optional argument) – This is a field/item pair. There are up to 126 pairs of field names and item names that may be used to describe the data that we wish to retrieve.
We need to enclose in quotation marks all field names and names for items other than dates and numbers. We need to keep in mind the following:
- Dates are entered as date serial numbers by using the DATE function so that they are in the proper date format.
- Numbers can be entered directly.
- Time should be entered in decimals or as the TIME function.
Note:
- #REF! error – Occurs when:
- The given pivot_table reference does not relate to a pivot table.
- When we provide invalid fields for the arguments data_field, [field], or [item].
- The field details are not displayed in the specified pivot table.
- Remember that if an item contains a date, it should be in date format or serial number.
- The function can be automatically inserted by enabling the “Use GetPivotData functions for PivotTable references” option in MS Excel.
