Microsoft Office Tutorials and References
In Depth Information
Creating a Calculated Field or Calculated Item
The examples that follow create
h A calculated field, to compute average sales per unit
h Four calculated items, to compute the quarterly sales commission
Creating a calculated field
Because a pivot table is a special type of range, you can’t insert new rows or columns within the
pivot table, which means that you can’t insert formulas to perform calculations with the data in a
pivot table. However, you can create calculated fields for a pivot table. A calculated field consists
of a calculation that can involve other fields.
A calculated field is basically a way to display new information in a pivot table. It essentially
presents an alternative to creating a new column field in your source data. In many cases, you may
find it easier to insert a new column in the source range with a formula that performs the desired
calculation. A calculated field is most useful when the data comes from a source that you can’t
easily manipulate — such as an external database.
Calculated fields can be used in the Values section of a pivot table. They cannot be
used in the Column Labels, Row Labels, or Report Filter sections of a pivot table.
In the sales example, for example, suppose that you want to calculate the average sales amount
per unit. You can compute this value by dividing the Sales field by the Units Sold field. The result
shows a new field (a calculated field) for the pivot table.
Use the following procedure to create a calculated field that consists of the Sales field divided by
the Units Sold field:
1. Select any cell within the pivot table.
2. Choose PivotTable Tools
Fields, Items & Sets
Excel displays the Insert Calculated Field dialog box.
3. Type a descriptive name in the Name box and specify the formula in the Formula box
(see Figure 18-33).
The formula can use worksheet functions and other fields from the data source. For this
example, the calculated field name is Average Unit Price, and the formula is
4. Click Add to add this new field.
5. Click OK to close the Insert Calculated Field dialog box.