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
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.