Excel 2010 Bible

Part V: Analyzing Data with Excel

728

FIGURE 35.16

This pivot table uses a calculated field.

Inserting a calculated item

The preceding section describes how to create a calculated field. Excel also enables you to create a

calculated item for a pivot table field. Keep in mind that a calculated field can be an alternative to

adding a new field to your data source. A calculated item, on the other hand, is an alternative to

adding a new row to the data source — a row that contains a formula that refers to other rows.

In this example, you create four calculated items. Each item represents the commission earned on

the quarter’s sales, according to the following schedule:

l Quarter 1: 10% of January, February, and March sales

l (^) Quarter 2: 11% of April, May, and June sales
l Quarter 3: 12% of July, August, and September sales
l (^) Quarter 4: 12.5% of October, November, and December sales
Note
Modifying the source data to obtain this information would require inserting 16 new rows, each with formulas.
So, for this example, creating four calculated items may be an easier task. n
To create a calculated item to compute the commission for January, February, and March, follow
these steps:

1. Move the cell pointer to the Row Labels or Column Labels area of the pivot table
   and choose PivotTable Tools ➪ Options ➪ Calculations ➪ Fields, Items & Sets ➪
   Calculated Item. Excel displays the Insert Calculated Item dialog box.