Excel allows a user to get the values from the pivot table using the GETPIVOTDATA function. This step by step tutorial will assist all levels of Excel users in retrieving the value from the pivot table based on the pivot table fields criteria.
Figure 1. The result of the GETPIVOTDATA function
Syntax of the GETPIVOTDATA Formula
The generic formula for the GETPIVOTDATA function is:
=GETPIVOTDATA(data_field, pivot_table, [field1,item1], ...)
The parameters of the GETPIVOTDATA function are:
- data_field – a name of the pivot table value field from which we want to get the data
- pivot_table – a reference to a cell or a cell range in a pivot table
- field1 – a field to refer to
- Item1 – an item of the field to refer to
Setting up Our Data for the LOOKUP Function
The first table is a pivot table and consists of: “Quarter” (Column field in the pivot table), “Store” (Row field in the pivot table) and “Sales” (Value field in the pivot table). The second table has data for “Quarter” and “Store” that exist in the pivot table and an empty space for the “Sales” value. Based on the “Quarter” and “Store” values from the second table we want to retrieve “Sales” value from the pivot table and to place the result in the cell J4.
Figure 2. The tables for the GETPIVOTDATA function example
Get a Sales Value for Quarter and Store with GETPIVOTDATA Function
In our example, we want to get a sales value from the pivot table based on the quarter and the store from the second table.
The formula looks like:
The parameter data_field is “Sales”, the name of the pivot table value field while the pivot_table is the cell B2. The field1 and the field2 are the names of the pivot table fields “Store” and “Quarter”. Item1 and Item2 are the cells J3 and J2, field items Q1 and Store A for which we want to get the sales value.
To apply the GETPIVOTDATA function, we need to follow these steps:
- Select cell J4 and click on it
- Insert the formula:
- Press enter
Figure 3. Using the GETPIVOTDATA function to get a Sales value based on the Quarter and the Store
As we want to get the sales values from the pivot table, the data_field argument will be “Sales” value field from the pivot table. The pivot_table argument can be the reference to any cell in the pivot table.
Field arguments are the names of the pivot table fields “Store” and “Quarter” based on which we want to get the sales data. Item fields are defined in the cells J3 and J2 because we want to get the data for the Store A and the quarter Q1. Formula result is $3,000, a sales value for the Store A and Q1