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:
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.