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 Webinar on the Power Tools
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 a free training webinar on all of the Power Tools in Excel. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.
Hi Jon, hope all is going well. OOOOooohh… GETPIVOTDATA as a customized report eh? I can kinda see that…and I’m very much looking forward to that post. I think that will help solve some of the problems I’m having with some reformated reports I have. =D
Awesome! Thanks for the vote Carrie! 🙂
Yes, GETPIVOTDATA can really help with custom reports. We can use the calculation power of the pivot table to eliminate complex formulas that might be slow to calculate and difficult to maintain.
Hi Jon,
Cannot agree more!
“You can think of GETPIVOTDATA as an advanced lookup formula for pivot tables.”
Indeed GETPIVOTDATA is one of my most frequent used functions.
On the other hand, I think we don’t need a macro to toggle the function on/off. We can do so by adding that to QAT. More convenient.
Happy Friday!
Hi Jon
I have been irritated in past by the getpivot function and I was not aware about the toggle. So thanks for explaning.
As a quick fix, I used to simply type the call address instead of using the mouse and it used to work fine. Do you see any issues in doing that?
Thanks again for the tips, learnings and the help which keeps flowing in. I also look forward to explanation of this particular formula in future.
Cheers and regards
Ashutosh
When you’ve turned off GetPivotData will the formulas move when you change a filter in your pivot table?
Can you link to the post that goes into more detail on how to use the GETPIVOTDATA?