Help:Toggle GetPivotData
Problem
Working with Pivot Tables sometimes require the creation of additional rows or columns of data where you want to reference information inside the pivot table.
By default, Excel uses the GetPivotData function with an absolute reference to the hidden pivot table in order to retrieve your data
Instead of getting a cell reference like =B4, we get a long ugly GETPIVOTDATA function added to the formula.
This means you can't copy and paste your formulas outside the pivot table down or across as you normally would.
Solution
The feature that creates the GETPIVOTDATA formulas is called “Generate GetPivotData”. It has a setting that can be turned on or off.
To turn GETPIVOTDATA off:
- Select a cell inside the pivot table.
- Go to the Options/Analyze tab in the Ribbon.
- Click the small drop-down arrow next to Options
- You should see a check mark next to the option, Generate GETPIVOTDATA. This means the feature is currently On. Click the button to toggle GETPIVOTDATA Off.
Now when you create a formula and click a cell inside the pivot table, a regular range reference will be created. This range reference is relative, just like any other range reference. That means you will be able to copy and paste or the fill the formula down multiple cells.
Caveats
It's important to note that Generate GetPivotData is an application level setting. This means the setting will be the same across all files on your computer. You only have to set it once. The setting will be remembered next time you close and re-open Excel.
Since it is an application level setting, it does not travel with the workbook. If you send your file to someone else they may not have the same setting applied. They will have to change the Generate GetPivotData setting on their own computer if they want to adjust the forumlas.
The existing formulas will not change in any workbook when you or someone else toggles the setting. It does not impact existing formulas. It will impact on any new formulas they write if they are updating the workbook.
