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

4 comments

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

  • 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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly