Preview
Create and Remove a Calculated Item; Practice Exercises 16, 17, and 18
Calculated Items
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 16: Create a calculated item in a pivot table
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Click on any cell in the pivot table. 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).
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.
The pivot table now has the Calculated Item, “Sales Average”. The calculated item calculated the average for the months of Jan, Feb, Mar for both the Boston and New York markets for Years 2014 and 2015.
STEP 10: Pivot table with calculated item “Sales Average” added in Month column. Notice that it calculated the average for both year 2014 and year 2015 (FIGURE 6).
Practice Exercise 17: Remove a calculated item from a pivot table.
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2
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 2: Select the PivotTables contextual tab, “Analyze” (FIGURE 7).
STEP 3: From the Ribbon Calculations section, select Fields, Items, & Sets icon. From the drop-down menu, select Calculated Item (FIGURE 8)
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 9).
STEP 5: After entering the name, the Modify and Delete buttons will appear in the top-right corner of the dialog box.
STEP 6: Select the Delete button.
STEP 7: Select the Close button.
The Calculated Item “Sales Average” has been deleted from the pivot table.
Practice Exercise 18: Find a calculated field or calculated item in a pivot table.
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Select the pivot table Analyze tab (FIGURE 10).
STEP 2: From the Ribbon Calculations section, select Fields, Items, & Sets icon. From the drop-down menu, select List Formulas (FIGURE 11).
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 12).
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)
END OF LESSON
Leave a Reply