Site icon Professor Excel

GETPIVOTDATA: Disable GETPIVOTDATA permanently in Excel

Disable GETPIVOTDATA permanently in Excel.

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

The GETPIVOTDATA formula has several problems.

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.


Have you ever built an app based on Excel?

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.

Steps for disabling GETPIVOTDATA in Excel permanently.
  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.


Have you ever built an app based on Excel?

Exit mobile version