
GetPivotData in ExcelYou'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 MethodThe 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 MethodThere 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:
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 topleft 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 TableAfter 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.
Excel Help for GetPivotData Provides This TipTo 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 reenable the Generate GetPivotData feature that you might have previously disabled. 



Excel is a registered trademark
of the Microsoft® Corporation. All contents Copyright 19982008 by MrExcel Consulting. 