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
3. Calculate running totals in Pivot Tables
Supriya says
Thank you soo much. this tip has been really helpful for me. as you mentioned one had to use the first option of typing the cell reference or pasting the pivot table in diff sheet with paste special as value and then do the entire calculations.
this is a great help.
thank you
Pascal says
Thanks Babara, very good tip.
If you need to keep the formula I can suggest you to modify the formula GETPIVOTDATA by replacing parameter(….., “Widget type”, “A”) by (….., “Widget type”, $A4) this way it’s possible to drag down and have the right value.