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