MrExcel Publishing
Your One Stop for Excel Tips & Solutions

GetPivotData() Functions in Excel XP

February 04, 2004 - by Bill Jelen

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:


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.

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.