Tag Archives: pivot tables

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.

  • I wanted to compliment you on the Accounting Fraud – Recent Case Studies (AUCASEMC) course I just took. The ebook was fantastic! Concise, easy to understand, very well structured, and very interesting. Exactly the way I like it. Thank you for putting it together. I feel it took me enough time to be worth 2 CPEs, but it was fantastic nonetheless. Thank you.

    - Mark R.

  • ” I was able to get a head start on my credit losses research through your CPE self study this past weekend.  Excellent quality format – so much better than some of the other resources I’ve been using lately.”  Dec 12, 2018

    - Andrea Ll.

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom