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
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: