Use GETPIVOTDATA to integrate pivot tables with dashboards

Posted on August 26th, 2015 in Pivot Tables & Charts - 10 comments

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?

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.

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.

 Summarize only filtered values using SUBTOTAL & AGGREGATE formulas CP043: My favorite time saving features of Excel, Revealed.
 Written by Chandoo Tags: advanced excel, awesome august, dashboards, data validation, downloads, GETPIVOTDATA, Microsoft Excel Formulas, pivot tables, screencasts, slicers Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

10 Responses to “Use GETPIVOTDATA to integrate pivot tables with dashboards”

1. Jeff S says:

I started using CUBEVALUE

2. Jeff S says:

We started using CUBEVALUE (and related) formulas recently with much success. From what I've seen and read, they're much easier to work with than GPD. Philip J. Taylor of http://www.excelcraft.com has some excellent material.

3. Jason says:

Does anyone know how to use getpivotdata or something similar for powerpivot created pivot tables?

4. Oz says:

Good lord! I'm working on a project right now that could use this. Thanks for this blogpost!!

5. Jeff Weir says:

Chandoo: re If you change the pivot table structure, your GETPIVOTDATA functions may not work as you expect.

The actual beauty of GETPIVOTDATA is that you can change the structure, and still get what you expect. The only time that I'm aware of that you'd get something different is if you dragged field in or out of the PivotTable. But move the existing fields around, and you should get the exact same result no matter where they are.

6. Jeff Weir says:

A problem with GETPIVOTDATA is that it can only be used to reference cells in the DATA area of the PivotTable, and that you can only reference one cell at a time and not an entire field.

But that's where my home-made Structured PivotTable References functionality comes in handy:

http://chandoo.org/wp/2014/10/18/introducing-structured-references-for-pivottables/

I'm in the process of revising this code to make it super robust, and will update that article with the revised code in the next week or so.

7. Nikki says:

Thank you for making August so awesome, Chandoo!

I am learning a lot and being reminded of what I have already forgotten 🙂

You are awesome!

8. Great Post!

Here's a time saver for anyone willing to use some VBA code.

Automatically converts your pivottables to GetPivotData formulas - similar to the convert to formulas feature of OLAP pivots.

http://datapigtechnologies.com/blog/index.php/convert-regular-pivottables-to-getpivotdata-formulas-part-3/

9. Ola says:

I agree with Jeff, =CUBEVALUE() adds a certain flexibility.
Much like Smartview formulas, if the data is well structured.
http://www.excelcraft.com/files/CFR.wmv

10. Tom says:

This is really helpful and easy to understand.

 Summarize only filtered values using SUBTOTAL & AGGREGATE formulas CP043: My favorite time saving features of Excel, Revealed.