How to use Excel GETPIVOTDATA function

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.

Leave a Reply

Close Menu
×

Cart