4

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

The GetPivotData Surprise

Have you ever experienced the GetPivotData surprise? 🙂

How to turn off GETPIVOTDATA formulas in Excel Pivot Tables

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.

GETPIVOTDATA formulas are absolute and can't be copied down

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.

How to Turn Generate GetPivotData On or Off for Excel Pivot Tables

To turn GETPIVOTDATA off:

  1. Select a cell inside the pivot table.
  2. Go to the Options/Analyze tab in the Ribbon.
  3. Click the small drop-down arrow next to Options
  4. 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.

Create Normal Formulas with Cell References to Pivot Tables with Generate GetPivotData Off

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.

Pivot Tables Webinar Banner 550

The webinar is running at multiple days and times, so click the link below to register for a day & time that works for you.

Click here to register for the Free webinar on Pivot Tables

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 4 comments
Ashutosh - March 2, 2018

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

Reply
MF - March 1, 2018

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!

Reply
Carrie - March 1, 2018

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

Reply
    Jon Acampora - March 1, 2018

    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.

    Reply

Leave a Reply: