GetPivotData in Excel

December 17, 2002 - by Bill Jelen

You've just built a pivot table in Excel. You click outside of the pivot table. You build an Excel formula. You copy this formula down to all of the rows in the pivot table. The calculation reports the wrong answer for all but the first row of the pivot table. You've just been stung by the Generate GetPivotData "feature". Let's take a look at what this is, and how to prevent it.

Just Prevent The GetPivotData Problem - The Quick Method

The fastest way to prevent Excel from generating GetPivotData is to type your formula without using the mouse or any arrow keys. If you simply type =E5/D5, then Excel will create a "normal" relative formula that can be copied down to all rows adjacent to the pivot table.

Prevent The GetPivotData Problem - The Permanent Method

There is a hidden setting to stop Excel from generating GetPivotData. It is more hidden in Excel 2003 than in Excel 2007.

In Excel 2007, follow these steps:

• Create a pivot table in Excel 2007
• Make sure the active cell is inside the pivot table
• Look on the left side of the PivotTable Tools Options tab of the ribbon. There is an Options button. Do not click the Options button! At the right side of the Options button is a dropdown arrow. Click on the dropdown arrow. Uncheck Generate GetPivotData.
• You can now enter formulas using the mouse, arrow keys, or typing.

In Excel 2003 and earlier, follow these steps:

• Choose Tools, Customize
• There are three tabs in the Customize dialog. Choose the Commands tab in the center.
• In the left list box, choose the 7th item, Data
• In the right list box, scroll almost all the way to the bottom. The 8th icon from the end of the list is Generate GetPivotData. Drag this icon from the list box and drop it in the middle of any existing toolbar.
• Click the Close button to close the dialog.
• Click the icon you just added to the Excel toolbar. This will turn off the feature. You can now enter formulas using the mouse without generating GetPivotData functions.

Here is a bonus tip: I used to despise this feature and simply wanted it turned off all the time. I added the icon to my toolbar, turned the icon off, then removed it from the toolbar. Now...I've eased up a bit. I can see that the icon occassionally has some good uses. So, I built a pivot table in Excel 2003 and made sure the cell pointer was in the pivot table. I then used the customize dialog box to drag the Generate GetPivotData icon to my Pivot Table Toolbar. Now - when I am in a pivot table, I can choose to turn the feature on or off.

Why Did Microsoft Do This? Is There Any Good Use for GetPivotData?

What is GetPivotData supposed to do? Clearly, Microsoft likes the function since they forced it upon millions of unsuspecting people who use pivot tables.

GetPivotData will return any visible cell from a pivot table. Rather than pointing to a cell address, though, you get to describe the value as an intersection of various field values.

As the pivot table changes, GetPivotData will continue to return the same logical data from the pivot table, provided that data is still visible within the pivot table. This can be important if you are changing from month to month in the page field of a pivot table and you want to always return the sales for a particular customer. As the customer list changes each month, the position of the customer will change. By using =GETPIVOTDATA, you can ensure that you return the proper value from the pivot table.

=GETPIVOTDATA always starts with two particular arguments. It then optionally has additional pairs of arguments.

Here are the two required arguments:

1. The name of a data field. This can be "Sum of Revenue" or simply "Revenue".
2. Any cell that is located "inside" the pivot table. Did you know that your pivot table has a name? You probably did not know this because you can not discover the name in the Excel interface. You would have to go to VBA to learn the name of the pivot table. So - it was easier for Microsoft to allow you to identify the pivot table by pointing to any cell within the pivot table. While you can choose any cell, it is safest to choose the top-left corner cell. There is a chance your pivot table might shrink for a particular combination of page fields. In that case, using the top left corner cell will make sure you keep pointing inside the pivot table.

Then, you continue the function with additional pairs of arguments. Each pair includes a field name and a value.

GetPivotData Can Only Return Values Visible in the Pivot Table

After you see a few GetPivotData functions working, you might think that they are more powerful than they actually are. In fact, the function will only work if the particular value is visible in the pivot table. Consider the image below.

• In cell A2, the GETPIVOTDATA returns January 2004 sales of ABC in the Central Region. This is grabbing the 80003 from cell G19.
• However, the formula in A6 fails. It is asking for sales of ABC across all regions. The pivot table shows total ABC for Central, total ABC for East, total ABC for West, but it never shows total ABC for all regions, so the result is a #REF! error.
• If you would pivot the region field up to the page area, the formula in A6 would start to work, but the formula in A2 & A4 would start returning #REF!. If you chose Central from the Region dropdown, then all four formulas would work.
• Turning off Grand Totals in the PivotTable Options dialog WILL cause many GetPivotData functions to start returning #REF! errors.

Excel Help for GetPivotData Provides This Tip

To build these functions, it is easiest to build the formula using the mouse. Type an equals sign in a cell outside of the pivot table and then use the mouse to click on a cell inside the pivot table. Excel will handle the details of building the references. In the image above, months and years are grouped fields, created from the date field. Excel help doesn't document that the month field should be specified as 1 for Jan, but you can learn this from observing the results from allowing Excel to build the GetPivotData. Of course... to use this feature, you have to re-enable the Generate GetPivotData feature that you might have previously disabled.