Chapter 35: Analyzing Data with Pivot Tables
727
the data source. For this example, the calculated field name is Average Unit Price, and the
formula is
=Sales/’Units Sold’- Click Add to add this new field.
- Click OK to close the Insert Calculated Field dialog box.
FIGURE 35.15The Insert Calculated Field dialog box.
Note
You can create the formula manually by typing it or by double-clicking items in the Fields list box. Double-
clicking an item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds sin-
gle quotes around the field name. n
After you create the calculated field, Excel adds it to the Values area of the pivot table (and it also
appears in the PivotTable Field List). You can treat it just like any other field, with one exception:
You can’t move it to the Row Labels, Column Labels, or Report Filter areas. It must remain in the
Values area.Figure 35.16 shows the pivot table after adding the calculated field. The new field displayed Sum
of Average Unit Price, but I shortened this label to Avg Price. I also changed the style to display
banded columns.Tip
The formulas that you develop can also use worksheet functions, but the functions can’t refer to cells or named
ranges. n