Microsoft Office Tutorials and References

In Depth Information

**Referencing Cells within a Pivot Table**

This workbook, named
income and expenses.xlsx
, is available on the companion

CD-ROM.

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

=D5/C5

In fact, the formula in cell F5 is

=GETPIVOTDATA(“Sum of Expenses”,$A$3,”Year”,2007)/GETPIVOTDATA(“Sum of

Income”,$A$3,”Year”,2007)

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. Figure 18-41 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.

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 returned 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

➜

Options➜PivotTable ➜Options➜Generate GetPivot Data (this command is a toggle).