The setting can also be changed with a VBA macro. Generate GetPivotData is just a tool that helps create the function for NEW formulas we write. However, the existing formulas will NOT change in any workbook when you or someone else toggles the setting. They will have to change the Generate GetPivotData setting on their own computer.

If you send your file to co-workers they will NOT have the same setting applied. Since it is an application level setting, it does NOT travel with the workbook. The setting will be remembered next time you close and re-open Excel. This means the setting will be the same across all files on your computer. It's important to note that Generate GetPivotData is an application level setting. That means you will be able to copy and paste or the fill the formula down multiple cells. This range reference is relative, just like any other range reference. Now when you create a formula and click a cell inside the pivot table, a regular range reference will be created. Click the button to toggle GETPIVOTDATA Off.

You should see a check mark next to the option, Generate GETPIVOTDATA.

Click the small drop-down arrow next to Options.

Go to the Options/Analyze tab in the Ribbon.It has a setting that can be turned on or off. The feature that creates the GETPIVOTDATA formulas is called “Generate GetPivotData”. 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. Instead of getting a cell reference like =B4, we get a long ugly GETPIVOTDATA function added to the formula. This happens when you are creating a formula in an Excel worksheet, and click a cell inside a pivot table. Have you ever experienced the GetPivotData surprise? 🙂 Watch on YouTube and give it a thumbs up.

Pivot table in mac numbers