Besides using calculated fields, Excel enables you to also use calculated items. Just as calculated fields allowed an optional way to add additional columns to a pivot table’s data source, calculated items is an optional way of adding additional rows. The additional row contains a formula to refers to other rows.
Rules Regarding Calculated Items
- You cannot use a page field to create a calculated item, nor can you move any calculated item to the report filter area.
- You cannot add a calculated item to a pivot table that has a grouped field, nor can you group any field in a pivot table that contains a calculated item.
- When building a calculated item formula, you cannot reference items from a field other than the one you are working with.
Practice Exercise 17: Create a calculated item in a pivot table
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Click on any cell in the pivot table. From the PivotTable Tools tab, select the Design tab (FIGURE 1). STEP 2: From the Subtotals drop-down menu, select Do Not Show Subtotals (FIGURE 2).
STEP 3: Make any cell active in the Month column.
STEP 4: From the PivotTables Tools tab, select the Analyze tab (FIGURE 3).
STEP 5: From the Ribbon Calculations section, select Fields, Items, & Sets icon. From the drop-down menu, select Calculated Item (FIGURE 4).
|
FIGURE 4
|
STEP 6: In the Insert Calculate Item dialog box, in the Name window enter: Sales Average (FIGURE 5).
STEP 7: In the Formula window enter: =AVERAGE(Jan,Feb,Mar) STEP 8: Select the Add button. STEP 9: Select the OK button. |
FIGURE 5
|
STEP 1: Make any cell active in the column that the calculated item is in. In this case, it would be the Month column (FIGURE 6).
STEP 3: From the Ribbon Calculations section, select Fields, Items, & Sets icon. From the drop-down menu, select Calculated Item. STEP 4: In the Insert Calculated Item dialog box enter the name of the Calculated Item you want removed. In this example, we want to remove the Calculated Item with the name, so enter, “Sales Average” (FIGURE 7).
STEP 5: After entering the name, the Modify and Delete buttons will appear in the top-right corner of the dialog box. |
FIGURE 8
|
FIGURE 9
|
STEP 3: The List Formulas shows any formulas that are current in a pivot table that are used for creating a calculated field or calculated item (FIGURE 9).
This List Formulas shows 2 entries that are being used for creating calculated fields and calculated items: Profit = Sales-Cost Sales Average = AVERAGE(Jan,Feb,Mar) |
FIGURE 12
|