Microsoft Office Tutorials and References
In Depth Information
Tip 79: Controlling References to Cells Within a Pivot Table
Controlling References to Cells Within a
In some cases, you may want to create a formula that references one or more cells within a pivot
table. Figure 79–1 shows a simple pivot table that displays income and expense information for three
years. In this pivot table, the Month field is hidden, so the pivot table shows the year totals.
Figure 79-1: The formulas in column F reference cells in the pivot table.
Column F contains formulas, and this column is not part of the pivot table. These formulas calculate
the expense-to-income ratio for each year. I created these formulas by pointing to the cells. You may
expect to see this formula in cell F3:
In fact, the formula in cell F3 is
=GETPIVOTDATA(“Sum of Expenses”,$B$2,”Year”,2010)
/GETPIVOTDATA(“Sum of Income”,$B$2,”Year”,2010)
When you use the pointing technique to create a formula that references a cell in a pivot table,
Excel replaces those simple cell references with a much more complicated GETPIVOTDATA function.
If you type the cell references manually (rather than pointing to them), Excel does not use the
The reason? Using the GETPIVOTDATA function helps ensure that the formula will continue to
reference the intended cells if the pivot table layout is changed.