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
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:
In fact, the formula in cell F5 is
=GETPIVOTDATA(“Sum of Expenses”,$A$3,”Year”,2007)/GETPIVOTDATA(“Sum of
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).