Microsoft Office Tutorials and References

In Depth Information

**Tip 79: Controlling References to Cells Within a Pivot Table**

Controlling References to Cells Within a

Pivot Table

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:

=D3/C3

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

GETPIVOTDATA function.

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.