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

➜

Options

➜

Calculations

➜

Fields, Items & Sets

➜

Calculated Field.

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

=Sales/’Units Sold’

4.
Click Add to add this new field.

5.
Click OK to close the Insert Calculated Field dialog box.