Let’s Get Rid of =GetPivotData


If you use pivot tables a lot, then you have run into  the GetPivotData formula. The other day, I was trying to create a formula referencing some pivot table cells and the GetPivotData formula popped up when I did not want it to. Today, I read Debra Dalgleish’s tweet on the same topic and realized that it is a problem for a lot of people. So, I thought some of you might be interested in learning how to get rid of GetPivotData.

GetPivotData has a lot of uses, however, if you want to create another formula in the column next to a pivot table and refer to a cell in the pivot table then you know what happens. First, you get a long long formula referencing the pivot table and the cell which is okay.

getpivotdata formula

But then, you copy it down and suddenly, you have the same answer displaying in every cell.

This drives me nuts and I am sure some of you are shaking your head yes and agreeing with me.




If you do not use GetPivotData and want to remove it all you need to do is click in the pivot table to select it and then do the following:

  1. Click the drop down arrow under Options on the Excel ribbon.






2. Click Generate GetPivotData command and turn it off.
If you do not see the Options choice, make sure your cursor is in the pivot table.

This turns the formula GetPivotData off for all future spreadsheets. So, if you do want to use it at a later point in time, you will have to go back in and turn it back on.

One Response

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue