Tag Archives: getpivotdata

Let’s Get Rid of =GetPivotData

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

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott

  • “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

  • “Excellent Regulatory Review Course. The self-study format (E-Book) provided an excellent overview of the regulatory requirements for California CPAs. I will be using the E-Book as  a reference material. Thank you for providing a very comprehensive yet affordable option for this required CPE.”

    - Constance