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.
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.
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!
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
