Excel 2010 Bible

Part V: Analyzing Data with Excel

726

FIGURE 35.14

This pivot table was created from the sales data.

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 (derived from other fields) 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.

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. Enter a descriptive name in the Name box and specify the formula in the Formula
   box (see Figure 35.15). The formula can use worksheet functions and other fields from