Microsoft Office Tutorials and References

In Depth Information

This workbook, named pivot table referencing.xlsx, is available at this topic's website.

Figure 18-42:
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 ref-

erences 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. Figure 18-43 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. Had I used simple cell references, the formula would have returned

incorrect results after expanding the years.