# How to use GETPIVOTDATA with Excel Pivot Tables

Share

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.

Share this tip with your colleagues

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic)

Bill Jelen is one of my most favorite people on earth. That is why I wanted to have him as my first guest when I restarted the podcast. Even though I recorded this few weeks ago, only now I got around to publishing it. Please enjoy the conversation with Bill.

## Related Tips

Learn Excel

### How to make a pivot table when you have data in multiple sheets [Tutorial]

Charts and Graphs

Learn Excel

### These Pivot Table tricks massively save your time

Cool Infographics & Data Visualizations

Excel Howtos

### Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Charts and Graphs

### 12 Responses to “How to use GETPIVOTDATA with Excel Pivot Tables”

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.

11. Shashidhar says:

Hi Guys,

Is it possible to return a text instead of only numbers.

12. vijay says:

=GETPIVOTDATA("Amount",\$A\$3,"Cust Area","South","Prod Category","Biscuits")
not working

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.Â

#### Downloads

Â© All rights reserved.