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.
- Introduction to Excel Pivot Tables – article , Podcast
- Comprehensive guide to Excel Pivot Tables
- Slicers – Introduction, what are they, advanced scenarios
- Building dashboards with Pivot Tables + Slicers
- Convert regular pivots to GETPIVOTDATA – 3 part tutorial from Mike Alexander, part 2 & part 3
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.
12 Responses to “How to use GETPIVOTDATA with Excel Pivot Tables”
I started using CUBEVALUE
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.
Does anyone know how to use getpivotdata or something similar for powerpivot created pivot tables?
Good lord! I'm working on a project right now that could use this. Thanks for this blogpost!!
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.
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.
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!
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/
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
This is really helpful and easy to understand.
Hi Guys,
Is it possible to return a text instead of only numbers.
=GETPIVOTDATA("Amount",$A$3,"Cust Area","South","Prod Category","Biscuits")
not working