Getpivotdata – an easier and better way to reference a pivot table in Excel

From my other blog I have illustrated how to create a pivot table.

How to read information from a pivot table

Now I am going to mention how to refer information from a pivot table.

There are two ways to do the job

  1. refer to the corresponding cells  (e.g. = a10)
  2. using the “GetPivotData” function

GetPivotdata function

Description

Th GetPivotdata function returns data stored in a pivot table. You can use GETPIVOTDATA to retrieve summary data from a pivot table, provided the summary data is visible in the report.

Syntax

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

Example

Suppose I have the following pivot table

GetPivotData Pivot Table
GetPivotData Function VS Direct Reference

Note: The table is filtered by region!

And I am going to fill-in information for the following table

Sales table to fill-in

Row 1

RegionShip ModeCategory
EastAllAll
Data FieldSales
Pivot Table$A$9
Field 1“Region”
Item 1“East”
Formula=GETPIVOTDATA(“Sales”,$A$9,”Region”,”East”)
Direct ReferenceF11

Row 2

RegionShip ModeCategory
EastStandard ClassAll
Data FieldSales
Pivot Table$A$9
Field 1“Region”
Item 1“East”
Field 2“Ship Mode”
Item 2“Standard Class”
Formula=GETPIVOTDATA(“Sales”,$A$9,”Region”,”East”,”Ship Mode”,”Standard Class”)
Direct ReferenceF15

Row 3

RegionShip ModeCategory
EastStandard ClassFurniture
Data FieldSales
Pivot Table$A$9
Field 1“Region”
Item 1“East”
Field 2“Ship Mode”
Item 2“Standard Class”
Field 3“Category”
Item 3“Furniture”
Formula=GETPIVOTDATA(“Sales”,$A$9,”Region”,”East”,”Ship Mode”,”Standard Class”,”Category”,”Furniture”)
Direct ReferenceC15

Following is the Formula

GetPivotData Vs Direct Reference Formula

And this is the result.

GetPivotData Vs Direct Reference Same Result

They looks exactly the same, the only difference is GetPivotData does not format according to the pivot table.

Using GetPivotData Vs Direct Reference

From the example above, it appears that using GetPivotData function only returns the same result as direct referencing the corresponding cell. However, is it always true?

Of course not! Otherwise, we don’t need to have the GetPivot Data function.

Let’s see what is the difference, by remove the filter of the pivot table.

Remove Pivot Table Filter

After removed the filter, this is the full pivot table

Full Pivot Table

Let’s have a look at the result again

getpivotdata vs direct reference - difference

Oh! Direct reference give us an incorrect result.

This is because the direct reference, simply reference to the value displayed in the corresponding cells while the getpivotdata function is reference to the value of the record in the pivot table underneath. Therefore, the result from the getpivotdata function does not change.

This is the result

GetPivotData Vs Direct Reference Cell reference

Leave a Comment