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

it to

=GETPIVOTDATA(“Score”,$F$2,”Gender”,”Female”,”District”,”District 1”)

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

➜

Options

➜

PivotTable

➜

Options

➜

Generate GetPivotData.

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.