Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off
When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.
In the first picture, both examples are shown. In the first formula scenario, clicking into a pivot table cell while composing a formula in an outside cell invokes the GETPIVOTDATA function. The second formula shows the cell address (F5 in the example) that was “pointed and clicked” to help build the formula involving a pivot table cell.
There are two ways to avoid Excel forcing the GETPIVOTDATA function upon your formulas. One way is to manually type the formula without using your mouse to select the pivot table cell.
Another way is to toggle (on or off) the GETPIVOTDATA function default action. How you do that depends on your version of Excel.
In Excel versions 2003 or before, as the next picture sequence shows:
(1) From the worksheet menu, click Tools > Customize.
(2) In the Customize dialog box, select the Commands tab.
(3) In the Categories pane, select Data.
(4) In the Commands pane, select Generate GetPivotData.
(5) Use your mouse to drag the Generate GetPivotData icon to your command bar.
(6) Close the Customize dialog.
(7) As indicated by the red arrow next to the Generate GetPivotData icon you can click the icon to toggle the GetPivotData behavior on and off.
In Excel versions 2007 and after, the process is much simpler as shown below:
(1) Select any cell in the pivot table.
(2) Under PivotTable Tools, click Options > PivotTable > Options > Generate GetPivotData
I have excel 2007 and I am keeping the option checked but still when i am copying the data only values are getting pasted getpivotdata is not coming
On the contrary when I am referencing the pivot data cell to a fresh cell it is getting generated.
I am getting baffled.
Thanks for following my blog.
I cannot duplicate that behavior but perhaps someone else reading this might have expereienced the same thing and can shed light as to why. If you do fix it, please post back with what the fix was.
Why avoid GETPIVOTDATA? It is so useful. Better that it is learnt.
If you read my narrative, I explained in the first paragraph why both methods could be preferred, and then I showed how to do both methods, so it is an individual preference depending on the situation. Many users avoid GETPIVOTDATA sometimes and they use it sometimes.