How to Turn Off GETPIVOTDATA Formulas for Pivot Tables
Bottom line: Learn how to turn the GETPIVOTDATA formulas on/off when you create a formula that references a cell inside a pivot table.
Skill level: Beginner
The GetPivotData Surprise
Have you ever experienced the GetPivotData surprise? 🙂
This happens when you are creating a formula in an Excel worksheet, and click a cell inside a pivot table. Instead of getting a cell reference like =B4, we get a long ugly GETPIVOTDATA function added to the formula.
The GETPIVOTDATA function can actually be quite useful for creating customized reports, and I’ll explain that in a future post, but we can also turn this feature off.
Turn Generate GetPivotData On/Off
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.
Application Level Setting
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 co-workers they will NOT have the same setting applied. They will have to change the Generate GetPivotData setting on their own computer.
However, the existing formulas will NOT change in any workbook when you or someone else toggles the setting. Generate GetPivotData is just a tool that helps create the function for NEW formulas we write. It does NOT impact existing formulas.
Toggle Generate GETPIVOTDATA with VBA
The setting can also be changed with a VBA macro. The GenerateGetPivotData property is a member of the Application object.
Sub Toggle_GetPivotData() 'Turn Generate GetPivotData Off Application.GenerateGetPivotData = False 'Set the property to True to turn it on End Sub
Don’t Kick It To The Curb
Although GETPIVOTDATA can be annoying and slow us down, it can also be a very useful function for creating more flexible reports. The GETPIVOTDATA function is used to return data from the Values area of a pivot table based. The function contains arguments for the pivot field and pivot items so we can tell it exactly which value from the pivot table we want to return to the cell.
You can think of GETPIVOTDATA as an advanced lookup formula for pivot tables. This frees us from the layout and formatting options of the pivot table, so we can build more customized reports with additional formulas.
I will do another post that goes into more detail on how to use the GETPIVOTDATA. It’s just good to know that it can be a useful tool.
Please leave a comment below with questions or suggestions on how you use GETPIVOTDATA. Thank you! 🙂
Free Training on Pivot Tables
If you are currently using pivot tables, but feel like you aren’t getting the most out of them, then I have a free training just for you.
Right now I’m running my free training webinar on “The 5 Secrets to Understanding Pivot Tables”. During this 60 minute training I explain the critical steps to building pivot tables to prevent errors and truly understand how they work.
If you’ve been struggling to build pivot tables with your own data, getting error messages, or just not sure which fields to put in each area, then get registered for the webinar. We also talk about getting the source data in the right layout, which is the most critical step to building a pivot table.
The webinar is running at multiple days and times, so click the link below to register for a day & time that works for you.