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

___________

You may also like...

5 Responses

  1. dan l says:

    Thanks.

    GetPivotData is really an awesome tool.

  2. Martin says:

    Debra,

    I found out that whenever I need to reflect info in a similar way I would do with a Pivot Table, it was a whole lot easier to use SUMPRODUCT instead.

    is there any advantages of GetPivotData over SUMPRODUCT?

    THanks,

    Martin

  3. @Martin, I’ve found that GetPivotData is faster, when pulling lots of data.

  4. Doug Glancy says:

    @Martin, I’m not sure if you’re asking the advantage of Pivot Tables, or GetPivotData, over Sumproduct.
    If it’s the first a short list would be: Subtotals; Automatically generating the universe of items and adjusting the table size when new ones are added; Displaying values as percents of rows; Drilling down; Doesn’t break when encountering #NA in data; Filtering at the Report and field level.
    If it’s the second a couple of advantages are: Using Sumproduct on a pivot table requires you to turn on Repeat Labels for All Rows (not available in 2003) or create a helper column with the repeated item names, GetPivotData doesn’t; GetPivotData adjusts automatically to changes in the size of the pivot table.

  5. Fred says:

    Is there a way to create a dynamic hyperlink to a cell in a pivot table? I have inserted cell references in the GetPivotData function to pull data for a specific month from the pivot table. Now I would like to be able to click a hyperlink in the same cell that has the GetPivotData formula and go to that part of the pivot table. Maybe I am missing an easier way to do this, but I have had difficulty creating a hyperlink that looks to another field for part of the string.
    Thanks for you help!

Leave a Reply to Martin Cancel reply

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