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

field:

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

=Sales/'Units Sold'

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.