GetPivotData Formula Instead of Cell Link

This week, I was updating the GetPivotData Function page on my website, and remembered how hard it was to turn off that feature, in Excel 2003 and earlier. We won’t even talk about the really olden days (Excel 2000), when you had to type those tricky GetPivotData formulas yourself!

Automatic Formulas

If you try to reference a pivot table cell, a GetPivotData formula may be automatically created, instead of a simple cell reference. This is thanks to the Generate GetPivotData feature, which is turned on by default.
getpivotdata02
The automatic formula can be a helpful feature, but sometimes you’d rather just have the cell link. You could type the link yourself, or find a way to turn off the formula feature.

GetPivotData in Excel 2003 and Earlier

In the old versions of Excel, if you want to stop that automatic formula creation, you have to add the Generate GetPivotData button to the PivotTable toolbar. If you’re nostalgic for the old method, you can see it in the video at the end of this blog.
image

GetPivotData in Excel 2007 and Excel 2010

Now, it’s much easier to turn the Generate GetPivotData feature on and off.

  1. Select any cell in a pivot table.
  2. On the Excel Ribbon, under PivotTable Tools, click the Options tab.
  3. In the PivotTable group, click the drop down arrow for Options
  4. Click the Generate GetPivotData command, to turn the feature on or off.

getpivotdata01

GetPivotData Formulas

There is more information on the GetPivotData Function page, including examples of using cell references within the formula. It’s a great way to pull specific data from your pivot tables.
getpivotdata03

Generate GetPivotData Button in Excel 2003

To see how we changed this setting in the olden days, you can watch this short video.

Or watch on YouTube: Turn Off GetPivotData Formulas for Excel PivotTables

___________