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.
- Select any cell within your PivotTable.
- Navigate to the “Analyze” ribbon within the PivotTable Tools.
- Click on the small arrow of the “Options” button.
- 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