If you’re building a formula, and you click on a pivot table value, Excel puts a GetPivotData function into your formula. Here’s how to stop that from happening.
Annoying Problem in Pivot Tables
Usually, when you’re working on a formula, and click on a cell, Excel creates a simple reference with the cell address:
- =B5
But, if you’re building a formula, and click on a pivot table value, Excel might insert a strange-looking formula, instead of a cell reference.
For example, I got this formula when I clicked on the pivot table total for File Folders:
- =GETPIVOTDATA(“Total”,$A$3,”Product”,”File Folders”)
Temporary Fix for GetPivotData Problem
If you don’t want Excel to create a GetPivotData formula automatically, you can turn that feature off. See how to do that, in the next section.
In the meantime, here’s a quick fix for the immediate problem.
- If Excel already created a GetPivotData function in your formula, delete that part of your formula
- Or, press the Esc key on your keyboard, to start over
- Then, in your formula, type the pivot cell address, instead of clicking on the cell
- For example, type this: =B5
- Press Enter to complete the formula
Turn Off GetPivotData Feature
If you don’t want Excel to ever create those GetPivotData formula automatically, you can turn that feature off.
This video shows the easy steps for turning it off, and you can do the same steps again later, if you want to turn GetPivotData back on.
There are written steps in the next section.
How to Turn Off GetPivotData
The GetPivotData function can be useful, so if you’re not familiar with how it works, and what you can do with it, go to the GetPivotData page on my Contextures site.
But, if you decide that you don’t need the GetPivotData formulas created automatically, here’s now to turn it off, so you always get normal cell references.
NOTE: This is an application-level setting, and changing it will affect ALL Excel workbooks that you open on your computer, not just the active workbook.
- Select any cell in a pivot table.
- On the Ribbon, click the PivotTable Analyze tab
- Or, under PivotTable Tools, click the Options tab
- At the left click the arrow on the PivotTable command
- Next, click the drop down arrow for Options
- Click the Generate GetPivotData command, to turn the feature off or on.
Download the Workbook
To get the sample file, and to learn more about this function, go to the GetPivotData page on my Contextures site. The file is in xlsx format, and does not contain macros.
The page shows more GetPivotData examples, and there are fixes for other GetPivotData formula problems too!
___________________
Annoying Pivot Table Problem GetPivotData Formula Fix
___________________