Midlands Microsoft Office Provider

3.2 Create and Remove a Calculated Item; Practice Exercises 17, 18, & 19 – Intro to Excel 2016 Pivot Tables

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 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 1
                      FIGURE 2
                       FIGURE 3
                                                      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.
FIGURE 5
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).
FIGURE 6
Practice Exercise 18: Remove a calculated item from a pivot table.
Still 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 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.
 
STEP 6: Select the Delete button.
 
STEP 7: Select the Close button.
 
The Calculated Item “Sales Average” has been deleted from the pivot table. 
                       FIGURE 7
                              FIGURE 8
                                        FIGURE 9
Practice Exercise 19: Find a calculated field or calculated item in a pivot table.
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.

 
STEP 1: From the PivotTables Tools tab, select the Analyze tab.
 
 
STEP 2: From the Ribbon Calculations section, select Fields, Items, & Sets icon. From the drop-down menu, select List Formulas ​(FIGURE 8).
        FIGURE 10
                                                                              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 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
Module 3 completed. Before moving to Module 4/Section 4.1, take the Module 3 quiz here.