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!
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.
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.
GetPivotData in Excel 2007 and Excel 2010
Now, it's much easier to turn the Generate GetPivotData feature on and off.
- Select any cell in a pivot table.
- On the Excel Ribbon, under PivotTable Tools, click the Options tab.
- In the PivotTable group, click the drop down arrow for Options
- Click the Generate GetPivotData command, to turn the feature on or off.
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.
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