Microsoft Office Tutorials and References
In Depth Information
A calculated field is basically a way to display new information in a pivot table: 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 area of a pivot table. They cannot be used in
the Columns, Rows, or Filter areas 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 calcu-
lated 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
1. Select any cell within the pivot table.
2. Choose PivotTable Tools ⇒ Analyze ⇒ Calculations ⇒ Fields, Items & Sets ⇒ Calculated Field.
Excel displays the Insert Calculated Field dialog box.
3. Type a descriptive name in the Name field and specify the formula in the Formula field (see Figure 18-34).
The formula can use worksheet functions and other fields from the data source. For this example, the calcu-
lated field name is Average Unit Price, and the formula is
Figure 18-34: The Insert Calculated Field dialog box.
4. Click the Add button to add this new field.
5. Click OK to close the Insert Calculated Field dialog box.