Excel GetPivotData Function

This Excel tutorial explains how to use Excel GetPivotData Function to get Pivot Table Data.

You may also want to read:

Create Pivot Table using Excel VBA

Excel VBA refresh all Pivot Table or Pivot Cache

Excel GetPivotData Function

Excel GetPivotData Function is used to get data in Pivot Table.

excel getpivotdata 01

For example, in a Pivot Table above, if you type =G3 in a Cell, a formula will generate automatically.

=GETPIVOTDATA("Salary",$E$1,"Department","IT","Name","John")

If you drag the formula down the Cell, you will not get the value of 400, 200, etc because of the dollar sign in between $E$1, known as absolute cell reference.

We will explain the arguments of the above GetPivotData formula.

Syntax of Excel GetPivotData Function

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
Data_field Required. The name of the data field to get, enclosed in quotation mark
Pivot_table Required. Any Range within the Pivot Table that you want to get information from
Field1, Item1, Field2, Item2 Optional. 1 to 126 pairs of field names and item names that describe the data that you want to retrieve. The pairs can be in any order. Field names and names for items other than dates and numbers are enclosed in quotation marks. If omitted, return grand total.

For OLAP PivotTable reports, items can contain the source name of the dimension and also the source name of the item. A field and item pair for an OLAP PivotTable might look like this: “[Product]”,”[Product].[All Products].[Foods].[Baked Goods]”

Example of Excel GetPivotData Function

Using the below Pivot Table as example.

excel getpivotdata 01

To look for Mary’s salary (200)

=GETPIVOTDATA("Salary",E1,"Name","Mary")

You can also specify “Sum of Salary” instead of “Salary”

=GETPIVOTDATA("Sum of Salary",E1,"Name","Mary")

 

If there is another Mary in IT department, then we need to specify the department as well

=GETPIVOTDATA("Salary",E1,"Department","HR","Name","Mary")

 

To get the Grand Total of Salary (900), do not put any Field Item

=GETPIVOTDATA("Salary",E1)

 

Note that if you hide the the desired data field such as Grand Total or the criteria does not match anything, the result will return #REF!

 

excel getpivot 02

To find the subtotal of Department, simply don’t specify the employee name, just specify the department.

=GETPIVOTDATA("Salary",E1,"Department","IT")

 

Outbound References

https://support.office.com/en-us/article/GETPIVOTDATA-function-8c083b99-a922-4ca0-af5e-3af55960761f

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *