How to get rid of GetPivotData in Microsoft Excel

By SpreadCheaters
Page last updated: 09/06/2023 | Next review date: 09/06/2025
close

Author bio


SpreadCheaters
View More north_east

GETPIVOTDATA is an Excel function designed to fetch data from a pivot table based on specified conditions. Its primary purpose is to extract summarized details from a pivot table and transfer them to a cell located outside the pivot table itself. Sometimes, it really becomes annoying when you want to use a value from inside a pivot table in a formula outside the pivot table. GetPivotData uses an absolute reference to the data cells inside pivot table and therefore, you can’t simply copy down the formula as well. 

In this tutorial, we will learn how to get rid of GetPivotData in Microsoft Excel. To streamline workflow and eliminate the reliance on the GetPivotData function, we need to turn off the GetPivotData function. This can be done by utilizing the PivotTable Analyze tab.

Following are the steps to be followed in order to turn off GetPivotData.

Method 1: Utilizing the PivotTable Analyze Tab

Step 1 – Perform a Click Anywhere on the PivotTable

  • Perform a click anywhere on the PivotTable to activate the PivotTable Analyze tab.

Step 2 – Locate the PivotTable Analyze Tab

  • Locate the PivotTable Analyze tab in the menu bar.

Step 3 – Perform a Click on the “Options” Drop-Down Arrow

  • Perform a click on the drop-down arrow with the “Options” located at the leftmost of the ribbon.

Step 4 – Deselect the GetPivotData Option

  • Deselect the GetPivotData option in the drop-down menu.

Step 5 – Check whether GetPivotData is Turned Off

  • Check whether GetPivotDta is turned off by selecting any cell of the PivotTable within a formula.

Method 2: Getting Rid of GetPivotData utilizing a VBA Code

Step 1 – Press the ALT+F11 Keys

  • Press the ALT+F11 keys to open the Visual Basic editor.

Step 2 –  Insert a New Module

  • Insert a new module by performing a right-click on the tab name.
  • Then, choose the Insert option and perform a click on the Module option.

Step 3 – Enter the VBA Code

  • Enter the VBA code in the editor: 
Sub Toggle_GetPivotData()
    ‘ Check the current state of Generate GetPivotData
    Dim isGenerateGetPivotDataOn As Boolean
    isGenerateGetPivotDataOn = Application.GenerateGetPivotData
   
    ‘ Toggle the state of Generate GetPivotData
    Application.GenerateGetPivotData = Not isGenerateGetPivotDataOn
   
    ‘ Display a message indicating the new state
    If Application.GenerateGetPivotData Then
        MsgBox “Generate GetPivotData is now turned ON.”, vbInformation
    Else
        MsgBox “Generate GetPivotData is now turned OFF.”, vbInformation
    End If
End Sub

Step 4 – Run the Code

  • Run the Code.

Step 5 – Check whether GetPivotData is Turned Off

  • Check whether GetPivotDta is turned off by selecting any cell of the PivotTable within a formula.