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.