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).
➜

Note