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.
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:
- 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.