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.
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.

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.
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.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

2 comments

  1. You say that “That’s why direct cell links are in most cases a better solution.” Can you please give examples when GETPIVOTTABLE would be the preferred solution?

    1. Hi Ron,
      Sometimes you want to make sure you grab the “Grand Total”, for example. With each update of the data it could be located in a different cell. So in such case “GETPIVOTTABLE” could be a preferred solution. Although probably a SUMIFS would be a better solution here…
      What do you think?
      BR,
      Henrik

Leave a comment

Your email address will not be published. Required fields are marked *