Part IV: Managing and Analyzing Data
This workbook, named pivot table referencing.xlsx, is available on this book’s website at
http://www.wiley.com/go/excel2019bible.
Column F contains formulas, and this column is not part of the PivotTable. These formu-
las calculate the expense-to-income ratio for each year. You can create these formulas by
pointing to the cells in the PivotTable. You may expect to see this formula in cell F3:
=D3/C3
In fact, the formula in cell F3 is
=GETPIVOTDATA("Sum of Expenses",$B$2,"Year",2017)/GETPIVOTDATA("Sum
of Income",$B$2,"Year",2017)
When you use the pointing technique to create a formula that references a cell in a
PivotTable, Excel replaces those simple cell references with a much more complicated
GETPIVOTDATA function. If you type the cell references manually (instead of pointing to
them), Excel doesn’t use the GETPIVOTDATA function. Why use the GETPIVOTDATA func-
tion? Using the GETPIVOTDATA function helps to ensure that the formula will continue to
reference the intended cells if the PivotTable layout is changed.
Figure 30.24 shows the PivotTable 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 ref-
erenced cells are in a different location. Had we used simple cell references, the formula
would return incorrect results after expanding the years.
FIGURE 30.24
After expanding the PivotTable, formulas that use the GETPIVOTDATA function continue to
display the correct result.
