Microsoft Office Tutorials and References

In Depth Information

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

(column) to your data source. A calculated item, on the other hand, is an alternative to adding new rows to the

data source — rows that contain formulas that refer 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:

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

•
Quarter 2:
11% of April, May, and June sales

•
Quarter 3:
12% of July, August, and September sales

•
Quarter 4:
12.5% of October, November, and December sales

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.

To create a calculated item to compute the commission for January, February, and March, follow these steps:

1.
Move the cell pointer to the Rows area of the pivot table and choose PivotTable Tools
⇒
Analyze
⇒
Calcula-

tions
⇒
Fields, Items, & Sets
⇒
Calculated Item. Excel displays the Insert Calculated Item dialog box.

2.
Type a name for the new item in the Name field and specify the formula in the Formula field (see Figure

18-36).

The formula can use items in other fields, but it can't use worksheet functions. For this example, the new

item is named Qtr1 Commission, and the formula appears as follows:

=10%*(Jan+Feb+Mar)