Excel automatically generates GETPIVOTDATA formulas when you click on a cell inside a PivotTable. This feature is designed to create stable references for PivotTable data. However, it can be disruptive when you want to write a simple cell reference or a different formula.
This behavior is controlled by a single setting in Excel’s options. This article explains why this happens and provides the steps to disable the automatic insertion of GETPIVOTDATA.
Key Takeaways: Disable GETPIVOTDATA Auto-Generation
- File > Options > Formulas > Uncheck ‘Use GetPivotData functions for PivotTable references’: This is the master switch that stops Excel from creating these formulas when you click on PivotTable cells.
- PivotTable Analyze > Options > Uncheck ‘Generate GetPivotData’: This ribbon button provides a quick toggle for the same setting on the active PivotTable.
- Typing the formula manually: You can still use the GETPIVOTDATA function by typing it yourself, even with the auto-insert feature turned off.
Why Excel Creates GETPIVOTDATA Formulas Automatically
The GETPIVOTDATA function retrieves specific data from a PivotTable report. Its primary advantage is that its references are based on the PivotTable’s structure, not on cell addresses. This means the formula continues to work correctly even if you filter, sort, or expand the PivotTable, which would break a standard cell reference like =B5.
To make building these robust formulas easier, Excel has a default setting that activates when you create a PivotTable. When this setting is on and you type an equals sign (=) and then click on a cell inside a PivotTable, Excel does not insert a simple cell reference. Instead, it writes a complete GETPIVOTDATA function with all the necessary field and item arguments. This automation is intended to help users, but it often gets in the way of other formula work.
Steps to Disable Automatic GETPIVOTDATA Creation
You can turn off this feature globally for all workbooks or quickly toggle it for a single PivotTable. Use the method that best fits your needs.
Method 1: Disable the Feature for All Workbooks
This method changes Excel’s application-level setting. It will affect all PivotTables in all workbooks you open on this computer.
- Open Excel Options
Click the File tab in the ribbon, then select Options from the left-hand menu. - Navigate to the Formulas section
In the Excel Options dialog box, click on the Formulas category in the left pane. - Find the PivotTable setting
In the Working with formulas section, locate the option labeled ‘Use GetPivotData functions for PivotTable references’. - Uncheck the box
Click the checkbox to remove the checkmark. Click OK to save the change and close the dialog.
Method 2: Use the Quick Toggle on the Ribbon
This method toggles the setting only for the currently selected PivotTable. It is faster and does not affect other PivotTables.
- Select your PivotTable
Click anywhere inside the PivotTable you are working with. This will activate the PivotTable Analyze contextual tab on the ribbon. - Open the PivotTable Options
On the PivotTable Analyze tab, find the PivotTable group. Click the small arrow on the Options button to open a dropdown menu. - Toggle the Generate GetPivotData command
In the dropdown menu, click on the Generate GetPivotData command. A checkmark next to it means the feature is on. Clicking it will remove the checkmark and turn the feature off for this specific PivotTable.
Common Issues and Things to Avoid
GETPIVOTDATA Formulas Still Appear After Turning the Setting Off
If you disabled the feature but Excel still inserts the function, first ensure you clicked OK in the Excel Options dialog. Then, close and reopen your workbook for the global setting to take full effect. If using the ribbon toggle, make sure you have the correct PivotTable selected, as the setting is applied per table.
Needing GETPIVOTDATA After Disabling It
You can still use the GETPIVOTDATA function manually. Simply type =GETPIVOTDATA( into a cell. Excel’s formula AutoComplete will guide you through the required arguments. This gives you full control over when to use the function.
GETPIVOTDATA Formulas Returning #REF! Errors
A #REF! error in a GETPIVOTDATA formula usually means the referenced field or item no longer exists in the PivotTable. This can happen if you renamed a field, removed it from the PivotTable layout, or refreshed the data and the underlying item values changed. Edit the formula to point to the correct field and item names.
Global Setting vs. Ribbon Toggle: Key Differences
| Item | Global Setting (File > Options) | Ribbon Toggle (PivotTable Analyze) |
|---|---|---|
| Scope of Change | Affects all PivotTables in all workbooks on your computer | Affects only the currently selected PivotTable |
| Persistence | Setting is saved in Excel and remains off until you turn it back on | Setting is saved with the specific workbook and PivotTable |
| Best Use Case | When you never want automatic GETPIVOTDATA formulas | When you need to quickly switch behavior for one table in a workbook |
| Access Path | File > Options > Formulas | PivotTable Analyze > Options > Generate GetPivotData |
You have now disabled Excel’s automatic GETPIVOTDATA insertion. You can click on PivotTable cells to create standard cell references in your formulas. If you need a stable reference later, remember you can type the GETPIVOTDATA function manually. For more control over PivotTable data extraction, explore the CUBE functions which work with Power Pivot data models.