Referencing 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 18-35 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 18-35: The formulas in column F reference cells in the pivot table.

On the CD 

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",2004)/ GETPIVOTDATA("Sum of Income",$A$3,"Year",2004) 

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-36 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 references cells are in a different location. Had I used simple cell references, the formula would return incorrect results after expanding the years.

Figure 18-36: After expanding the pivot table, formulas that use the GETPIVOTDATA function continue to display the correct result.

Caution 

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.

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, access the Excel Options dialog box, select the Formulas tab, and remove the check mark from Use GetPivotData Function For PivotTable References.