Creating a Calculated Field or Calculated Item

Perhaps the most confusing aspect of pivot tables is calculated fields versus calculated items. Many pivot table users simply avoid dealing with calculated fields and items. However, these features can be useful, and they really aren't that complicated after you understand how they work.

First, some basic definitions:

• A calculated field: A new field created from other fields in the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated field is to add a new column to the table and then create a formula to perform the desired calculation. A calculated field must reside in the Values area of the pivot table. You can't use a calculated field in Column Labels, Row Labels, or a Report Filter.

• A calculated item: A calculated item uses the contents of other items within a field of the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculate item is to insert one or more rows and write formulas that use values in other rows. A calculated item must reside in the Column Labels, Row Labels, or Report Filter area of a pivot table. You can't use a calculated item in the Values area.

The formulas used to create calculated fields and calculated items aren't standard Excel formulas. In other words, you don't enter the formulas into cells. Rather, you enter these formulas in a dialog box, and they're stored along with the pivot table data.

The examples in this section use the worksheet table shown in Figure 18-28. The table consists of five fields and 48 rows. Each row describes monthly sales information for a particular sales representative. For example, Amy is a sales rep for the North region, and she sold 239 units in January for total sales of $23,040.

Figure 18-28: This data demonstrates calculated fields and calculated items.

On the CD 

A workbook that demonstrates calculated fields and items is available on the companion CD-ROM. The file is named  calculated fields and items.xlsx.

Figure 18-29 shows a pivot table created from the data. This pivot table shows Sales (Values area), cross-tabulated by Month (Row Labels) and by SalesRep (Column Labels).

Figure 18-29: This pivot table was created from the sales data.

The examples that follow create

• A calculated field, to compute average sales per unit

• Four calculated items, to compute the quarterly sales commission

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 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 Tools Formulas 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 18-30). The formula can use worksheet functions and other fields from the data source. For this example, the calculated field name is Avg Unit Price, and the formula is

    =Sales/'Units Sold' 

   
   Figure 18-30: The Insert Calculated Field dialog box.

4. Click Add to add this new field.

5. Click OK to close the Insert Calculated Field dialog box.

Note 

You can create the formula manually by typing it or by double-clicking items in the Fields list box. Double-clicking an item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds single quotes around the field name.

After you create the calculated field, Excel adds it to the Values area of the pivot table (and it also appears in the PivotTable Field List). You can treat it just like any other field, with one exception: You can't move it to the Row Labels, Column Labels, or Report Filter areas. It must remain in the Values area.

Figure 18-31 shows the pivot table after adding the calculated field. The new field displayed Sum of Avg Unit Price, but I changed this label to Avg Price. I also changed the style to display banded columns.

Figure 18-31: This pivot table uses a calculated field.

Tip 

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 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 new rows to the data source-rows that contains 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

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.

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 Tools Formulas Calculated Item. Excel displays the Insert Calculated Item dialog box.

2. Enter a name for the new item in the Name box and specify the formula in the Formula box (see Figure 18-32). 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) 

   
   Figure 18-32: The Insert Calculated Item dialog box.

3. Click Add.

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

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

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

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

5. Click OK to close the dialog box.

Note 

A calculated item, unlike a calculated field, does not appear in the PivotTable Field List. Only fields appear in the field list.

Caution 

If you use a calculated item in your pivot table, you may need to turn off the Grand Total display for columns to avoid double counting. In this example, the Grand Total includes the calculated item, so the commission amounts are included with the sales amounts. To turn off Grand Totals, choose PivotTable Tools Design Layout Grand Totals.

After you create the calculated items, they appear in the pivot table. Figure 18-33 shows the pivot table after adding the four calculated items. Notice that the calculated items are added to the end of the Month items. You can rearrange the items by selecting the cell and dragging its border. Another option is to create two groups: One for the sales numbers and one for the commission calculations. Figure 18-34 shows the pivot table after creating the two groups and adding subtotals.

Figure 18-33: This pivot table uses calculated items for quarterly totals.

Figure 18-34: The pivot table, after creating two groups and adding subtotals.