GETPIVOTDATA when you don’t want to?….


Have you found when you try to reference a cell in a Pivot Table that Excel will sometimes automatically creates a cell reference with the prefix GETPIVOTDATA? ……even when you just want a simple straightforward cell reference like B2 or B3? Just like the screen shot below.

 

 

 

 

 

 

This setting is turned on by default with MS Excel. If you find this a nuisance, for example if you want to do extra calculations at the side of your Pivot Table, using the autofill option to drag the formula down- you will find that this just does not go to plan at all.

So, what can we do?.

We could manually type the cell reference that refers to the cell that is in the Pivot Table by physically typing B2 or, we can turn on off the GETPIVOTDATA feature altogether. Let’s go for the second of the two options.

An example?- of course- let’s use the Pivot Table with Widget Sales data as above.

  • Select any cell in your Pivot Table
  • Under Pivot Table Tools select Options
  • In the Pivot Table Group select the drop down Options
  • Turn Off (or On) the GeneratePivotData

 

 

Change the settings….

 

 

 

 

 

 

 

And finally we get just a straightforward cell reference.

 

More Excel Tips ….

1. Delete only numbers from cells

2. Fast Fun Formulas

3. Calculate running totals in Pivot Tables

 

 

 

 

 

 

 

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts