Microsoft Office Tutorials and References
In Depth Information
Tip 79: Controlling References to Cells Within a Pivot Table
Figure 79-2 shows the pivot table after expanding the years to show the month detail. As you can
see, the formulas in column F still show the correct result even though the referenced cells are in a
different location. For example, the summary row for 2011 was originally row 4. After expanding the
years, the summary row for 2011 is row 16. Had I used simple cell references, the formula would have
returned incorrect results after expanding the years.
Figure 79-2: After expanding the pivot table, formulas that used the GETPIVOTDATA function continue to
display the correct result.
Using the GETPIVOTDATA function has one caveat: The data that it retrieves must be visible in the
pivot table. If you modify the pivot table so that the value used by GETPIVOTDATA is no longer
visible, the formula returns an error.
You may want to prevent Excel from using the GETPIVOTDATA function when you point
to pivot table cells when creating a formula. If so, choose PivotTable Tools Analyze ➜ ➜
PivotTable Options Generate GetPivot Data (this command is a toggle). ➜