Excel 2019 Bible

Chapter 30: Analyzing Data with PivotTables

3030

The formulas that you develop can also use worksheet functions, but the functions can’t refer to cells or named

ranges.

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

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 (four

formulas for each sales rep). 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. Select any cell in the Row Labels or Column Labels area of the PivotTable and
   choose PivotTable Tools Analyze ➪ Calculations ➪ Fields, Items, & Sets ➪
   Calculated Item. The Insert Calculated Item dialog box appears.


2. Enter a name for the new item in the Name field, and specify the formula in
   the Formula field (see Figure 30.17). 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:
   = (Jan+Feb+Mar)*10%


3. Click Add.


4. Repeat steps 2 and 3 to create three additional calculated items:

Qtr2 Commission: = (Apr+May+Jun)*11%

Qtr3 Commission: = (Jul+Aug+Sep)*12%

Qtr4 Commission: = (Oct+Nov+Dec)*12.5%

5. Click OK to close the dialog box.