Getting Values from a Pivot Table using GETPIVOTDATA

Overview

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:

Example Table & Pivot Table

Example Table & Pivot Table

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

The syntax:

The GETPIVOTDATA Syntax

The GETPIVOTDATA Syntax

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):

Return a simple data value using GETPIVOTDATA

Return a simple data value using GETPIVOTDATA

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:

Use GETPIVOTDATA to return data for a value in the Pivot Table

Use GETPIVOTDATA to return data for a value in the Pivot Table

In this example we only have one field to choose from: Country, let’s display another field in the pivot table: Month

Use the GETPIVOTDATA function for multiple table categories

Use the GETPIVOTDATA function for multiple table categories

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:

Usine multiple criteria in GETPIVOTDATA

Usine multiple criteria in GETPIVOTDATA

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:

Picture7.png

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:

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit