Microsoft Office Tutorials and References
In Depth Information
Controlling References to Cells within a Pivot Table
Controlling References to Cells
within a Pivot Table
If you work with pivot tables, you’ve probably noticed that if you write a formula that refers to a
cell within the pivot table, the cell reference is converted automatically to a GETPIVOTDATA
function with a number of arguments.
Figure 170-1 shows an example. The formula in cell I4 (which I created by pointing) is a simple
reference to cell G4 in the pivot table. But rather than display the simple formula, Excel converts
Figure 170-1: A formula that references a cell in a pivot table might result in a complex formula.
This type of referencing ensures that your formulas still return the correct result if the structure
of the pivot table is changed. If you prefer to avoid this automatic conversion, don’t use the
pointing technique when creating formulas that reference cells in a pivot table. Just enter the cell
reference by typing its address.
You can also turn off the Generate GetPivotData option by choosing PivotTable Tools
The Generate GetPivotData button is a toggle. Click it once, and Excel stops generating
GETPIVOTTABLE formulas. Click it again, and Excel starts generating those formulas again. This
command doesn’t affect existing formulas.