Excel 2010 Bible

Part V: Analyzing Data with Excel

734

Column F contains formulas, and this column is not part of the pivot table. These formulas calcu-

late 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 func-

tion. 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 35.23 shows the pivot

table after expanding the years to show the month detail. As you can see, the formulas in col-

umn 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 return incorrect results after expanding the

years.

Caution
Using the GETPIVOTDATA function has one caveat: The data that it retrieves must be visible. If you modify
the pivot table so that the value returned by GETPIVOTDATA is no longer visible, the formula returns an
error. n

Tip
If, for some reason, you want to prevent Excel from using the GETPIVOTDATA function when you point to
pivot table cells when creating a formula, choose PivotTable Tools ➪ Options ➪ PivotTable ➪ Options ➪
Generate GetPivot Data. (This command is a toggle.) n