MrExcel.com - Your One Stop for Excel Tips & Solutions

GetPivotData() Functions in Excel XP

February 04, 2004 - by

Samantha W writes with an annoying problem in Excel XP:

I recently upgraded to Excel XP from Excel 2000. There is one behaviour that is driving me crazy. I create a pivot table. Next to the pivot table, I enter a formula that points at a cell inside the pivot table. When I then copy this formula down to the next row(s), the formula keeps pointing at the original cell.

Yes! This is incredibly annoying. If you enter a formula that seems like it should be =H10, Excel replaces it with this formula:

=+GETPIVOTDATA(“Sales”,$F$3,”Region”,”central”,”Product”,”b”)

Of course, as you copy this formula, it keeps pointing at the original cell.

I tried a few variations:

  • If I type an equals sign and use the mouse to touch H10, I get the strange formula.
  • If I type an equals sign and use the arrow keys to navigate to H10, I get the strange formula
  • If I just type =H10, then I get a formula which can copy

Samantha later found a setting to turn off this annoying behavior: You can choose Tools>Customize, and from the Data category on the Commands tab, add the Generate GetPivotData command to one of your toolbars. Use this button to toggle the feature on and off.