Pivot tables are a great tool to summarise data.
Sometimes we might want to get a value from a Pivot Table so we can either display it (in a dashboard for example) or use it in another formula. We can do this using the GETPIVOTDATA function.
It might seem straightforward enough to just use an ‘=A2’ formula or similar, but because the location of a value in a pivot table can change as they are refreshed, this could result in incorrect results. GETPIVOTDATA solves this problem.
How to get Values
Let’s take the following data and pivot table as an example to explain:
We want to get the sales for France from the pivot table.
To get a value from a pivot table in excel we use the function: GETPIVOTDATA
data_field – this is the field in the pivot table that we want to retrieve, in our example this is Sales
pivot_table- here we will specify which pivot table we want to get the data from.
We will do this by entering here the cell reference of one of the cells in the pivot table. Use the first cell on top left of the pivot table for consistency and it will make it easier to find the pivot table should someone else view or amend your function.
In our example this will be: $F$2
field1, item1 – this pair of arguments are optional. If we were to use the function without this optional argument, it will give us the total sales (the Grand Total):
But we want the sales specifically for France.
We will do this by adding the optional argument which I would describe it as a filter:
In the field1 argument we will specify what filter we are using: Country
In item1 we will specify what value we want this filter to take: France
Our function will look like this:
In this example we only have one field to choose from: Country, let’s display another field in the pivot table: Month
Adding a new field to the pivot table as you can see will not change the result of your formula, still the sales for France are 1200.We have displayed another field in the pivot table and we can decide if to use this new field in our formula or not.
If now we want to retrieve the Sales for France for the month of March, we will add another one of the optional arguments to the formula:
When you add this optional argument to the formula, you need to make sure it’s always a pair. You cannot just add Month without specifying what month, the formula will evaluate to error.
You can add up to 126 optional arguments to this formula (but please try to not use all 126!)
There is an even quicker way to get this function without having to write everything.
We can start in an empty cell, enter equal sign: = and click in any value in the data_field you want to retrieve:
Here I entered = and clicked on cell G9
You will notice that the GETPIVOTDATA function will come up by its self.
Now all we need to do is change the Country and the Month in this function so we can get the data that we want: