GETPIVOTDATA: Disable GETPIVOTDATA permanently in Excel

Does this sound familiar to you? You want to refer to a cell within a PivotTable, let’s say cell C6. But instead of getting =B6, Excel does something like =GETPIVOTDATA(“Value”;$A$3;”Name”;”c”). This article shows you how to permanently disable GETPIVOTDATA in Excel.

The problem of GETPIVOTDATA

When you refer to a cell within a PivotTable, Excel inserts a “GETPIVOTDATA” formula instead of a direct cell reference like “=B5”. If you copy and paste it to another cell, let’s say the cell below, you still refer to the same cell. That would be different with direct cell links: =B5 would automatically change to =B6 when copying to the cell below.

Another problem occurs when you change the fields of your PivotTable or rename them. You will easily receive #REF errors in such case.

That’s why direct cell links are in most cases a better solution.

Steps for disabling it permanently and inserting normal cell references instead

The steps for disabling GETPIVOTDATA are quite simple in Excel. You just have to insert a PivotTable and uncheck “Generate GetPivotData” within the PivotTable Options. The steps are shown in the following image.

1. Select any cell within your PivotTable.
2. Navigate to the “Analyze” ribbon within the PivotTable Tools.
3. Click on the small arrow of the “Options” button.
4. Uncheck “Generate GetPivotData”.

From now on, Excel inserts direct cell links (e.g. =B6) and no more GETPIVOTDATA.

More about PivotTables

Are you interested to learn more about PivotTables? Here is a selection of articles.

• Pivot Tables: How to Create a Pivot Table in Excel
• Pivotable: How to Prepare Data for Creating Pivot Tables in Excel
• See and Change the Data Source of a Pivot Table in Excel
• How to Avoid Changing the Column Width With Each Pivot Table Update
• PowerPivot: No More Trouble Working with Big Data in Excel