Introduction
GETPIVOTDATA function in excel is a lookup function. This function is used to return specific data from a pivot table. It uses pivot table structure instead of reference cell reference for retrieving data.
Syntax
=GETPIVOTDATA (Data_Field, Pivot_Table, [field1,item1])
Argument
- Data_Field: It is the name of the field entered in quotation marks which contains the data to be returned.
- Pivot_Table: It is the reference made to the cell in pivot table which has the data to be returned.
- Field_Item: These are pairs of field and item names which relates to the data to be returned.
Keynotes
- GETPIVOTDATA is used to return specific data from a pivot table.
- Arguments like field and item are used to retrieve data based on the nature of pivot table and acts as filters for the data.
- The arguments Data_Field and field/items should be entered inside quotation marks.
- If the spellings of the fields entered is incorrect then GETPIVOTDATA shows #REF error.
- To ensure error free results, use the same format as used in pivot table.
- It can be combined with other functions like DATE , DATEVALUE, TIME etc. to get accurate results.
Examples
In this example, a complex table of some products with their quantity, cost, amount, tax, total etc. is given. This table is than converted to a simple pivot table. Than GETPIVOTDATA function is used to extract total value of product ‘Fruit’ from the pivot table using the formula =GETPIVOTDATA(“Total”, A11, “Product”, D11) , which gives the value 160.5.
