Pivot Tables & Charts

How to use GETPIVOTDATA with Excel Pivot Tables

Last updated on May 8, 2018

Chandoo

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

What is GETPIVOTDATA?

As the name suggests, GETPIVOTDATA gets pivot table data. The best way to understand GETPIVOTDATA is by looking at an example.

Let’s say, you have a pivot table like the one below. And you want to know what is the Amount for Cust Area = Middle & Prod Category = Biscuits combination.

The below GETPIVOTDATA formula should work.

=GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”Middle“,”Prod Category”,”Biscuits“)

As you can see GETPIVOTDATA has below syntax.

GETPIVOTDATA(value field name, any cell reference in pivot table, [field name 1, value1, field name 2, value 2 …])

Few more examples of GETPIVOTDATA:

Check out below examples to understand how various parameters of the GETPIVOTDATA function behave:

GETPIVOTDATA function	What it does	Value
=GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”South”,”Prod Category”,”Biscuits”)	Gets Amount for South & Biscuits combination	$609.50
=GETPIVOTDATA(“Amount”,$A$3,”Prod Category”,”Biscuits”)	Gets grand total for Biscuits	$5,251.10
=GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”South”)	Gets grand total for South	$4,342.20
=GETPIVOTDATA(“Amount”,$A$3)	Gets grand total of all amounts	$41,828.00
=GETPIVOTDATA(“Amount”,$A$1,”Cust Area”,”West”,”Prod Category”,”Snacks”)	Gives an error as $A$1 is not part of the pivot	#REF!
=GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,$P$2,”Prod Category”,$P$3)	Gets Amount for cust area = P2 and pro category = P3 cell values.	depends on variables
=GETPIVOTDATA(“Amount”,$A$3,”Prod Category”,category_name)	Gets grand total for category = category_name value	depends on variables
=GETPIVOTDATA($P$4&””,$A$3,”Cust Area”,$P$2,”Prod Category”,$P$3)	Gets P4 value field for Cust Area = P2 and Prod Category = P3. Note: $P$4 &”” is used to convince GPD that P4 is a string not number.	depends on variables

Using GETPIVOTDATA in dashboards

The idea is simple. Since GETPIVOTDATA can be parameterized with variable cells or named ranges, we can use it in dashboards to get relevant data based on user input.

Sample this:

Or this dashboard powered with GETPIVOTDATA

Things to note when working with GETPIVOTDATA:

GETPIVOTDATA is a very useful function, but it does have a few quirks.

If your pivot table has slicers linked to them, GPD will reflect the results based on slicer selection.
If your pivot table has any items filtered (say category Biscuits is filtered out), then GPD will return #REF error when you try to get any value corresponding to Biscuits.
If you change the pivot table structure, your GETPIVOTDATA functions may not work as you expect.
If you turn off grand totals or sub-totals, you can no longer get them thru GPD.
GPD requires that your original pivot tables remain intact and visible all the time.
If you want to completely get rid of pivot tables and still get answers to questions, then you should use CUBE formulas along with Workbook data model feature (more on this in a future post).
The best & easiest way to write GPD is by pressing = and referencing a cell inside the pivot. This will automatically write the GPD for you. You can then customize the parameters as you need.
You can turn-off GPD by going to Pivot Table Analyze ribbon tab & unchecking “Generating GETPIVODATA” option from PivotTable options area.

Download GETPIVOTDATA Examples workbook

Please click here to download the GETPIVOTDATA example workbook. Refer to various tabs & formulas to learn more. Don’t forget to play with the dashboard powered by GETPIVOTDATA.

Learn more about Pivot Tables

If you are new to Pivot Tables, it’s high time you started using them. Check out below pages and get started.

How do you use GETPIVOTDATA?

Let me be honest. For my dashboards, I usually write direct cell references (=A7) instead of GPD. This keeps my formulas short. For dynamic / parameterized setups, I usually write INDEX / MATCH formulas that talk to Pivot Table data. But occasionally I use GETPIVOTDATA because it is very easy to setup and does what it says on the sticker.

What about you? How do you use GETPIVOTDATA? Please share scenarios in the comments section.