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

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

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 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.

Modern Excel Blueprint Training Webinar Excel Campus Jon Acampora

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.

Click Here to Register for the Free Webinar

6 comments

Your email address will not be published. Required fields are marked *

  • 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

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter