3.1 Create & Remove Calculated Field; Practice Exercises 15 & 16 – Intro to Excel 2016 Pivot Tables

Calculated Fields
Calculated fields in a pivot table do not use the same argument source for formulas as they do with standard data tables.  Instead of cell references, the pivot table fields are the arguments when constructing a formula to produce a calculated field; i.e. If we wanted to know our profit, then our calculated field Profit = Sales – Cost (The pivot table fields Sales and Cost are the arguments). A calculated field is an alternative to adding an additional column to the source data.

Calculated fields are mathematical functions that recalculate when the pivot table changes or is refreshed, which is similar to how the default subtotal and grand total calculations occur.
Calculated fields are not added to the source data table; they are only added to a pivot table.

Rules Regarding Calculated Fields and Calculated Items
Order of Operator Precedence Applies
You can use any operator in your calculation formulas, just as you can in regular worksheet formulas, including (+ – * / % ^). The same operator precedence order applies.

Using Cell References and Named Ranges
Calculations in a pivot table use only one source for their data, which is the pivot table cache. This excludes any cell references or named ranges from being used.

You can use any constant in your calculations. Constants are numbers that do not change value. For example, if you had a formula [Units Sold] * 5, then 5 would be a constant. Although the value of Units Sold could change based on the available data, the 5 always has the same value.

Referencing Totals
Your calculations cannot reference the pivot table’s subtotals or grand total values, disqualifying them as candidates as arguments in a calculation.

​Rules Regarding Calculated Fields
Calculated fields are always performed against the sum of your data. It is always the sum since Excel always calculates data fields, subtotals, and grand totals before evaluating your calculated field. 

Practice Exercise 15: Create calculated fields in a pivot table.
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.
STEP 1: Select any cell in the pivot table. Select the PivotTable Tools Analyze tab on the Ribbon (FIGURE 1).
                                                                        FIGURE 1
STEP 2: From the Ribbon Calculations section, select the Fields, Items, & Sets icon (FIGURE 2).
                                                 FIGURE 2
On the drop down menu select, Calculated Field (FIGURE 3).
                                                                  FIGURE 3
STEP 3: In the “Insert Calculated Field” dialog box, in the Name window, enter Profit (FIGURE 4).
After entering Profit, the Modify and Delete buttons will appear.

STEP 4: In the “Formula” window, delete the 0 but let the = symbol remain.
STEP 5: In the Fields section below, select the Field, Sales (you may have to scroll down to see it listed).
STEP 6: Select the Insert Field button, which will insert Sales into the Formula window.
STEP 7: Enter the subtraction symbol after Sales.
STEP 8: From the Fields list, select the Field, Cost.
STEP 9: Select the Insert Field button, which will insert Cost into the Formula window.

STEP 10:  Select the Add button.
STEP 11: Select the OK button.

STEP 12: Our newly created Calculated field has been added. Notice that Excel has named the calculated field, Sum of Profit (FIGURE 5).
The outlining of data in the graphic at the right shows the actual calculated field effect. The other columns displayed; TotalSales, TotalSalesQty, Total Cost, Total Sum of Profit are displayed due to Grand Total action only. 
           FIGURE 5
​Notice that our newly created calculated field has also been added to our PivotTable Fields list (Profit has been added to bottom of list) (FIGURE 6).

                                                        FIGURE 6
There are 3 things we need to correct on our pivot table.
1. Change our field, Sum of Profit to Profit
2. Change our formatting for that field to Currency
​3. Change the decimal to 2 places.
STEP 13: Select the field header, “Sum of Profit” on the pivot table (FIGURE 7).

Right-button mouse click and select Value Field Settings from the shortcut menu (FIGURE 8).

                                                FIGURE 7
                                                       FIGURE 8 
STEP 14: In the Value Field Settings dialog box, for the Custom Name window, enter “Profit”. “Sum” should be the selected as it is the default choice for “Summarize value field by” (FIGURE 9).

STEP 15: Select the Number Format button.

                                    FIGURE 9
STEP 16: From the Format Cells dialog box, select Category Currency. Change Decimal places to 2.(FIGURE 10).
STEP 17: Press OK button on Format Cells dialog box

STEP 18: Press OK button on Value Field Settings box to close it.

                    FIGURE 10
The pivot table result is shown below (FIGURE 11).

Practice Exercise 16: Remove a calculated field in a pivot table
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.

​We want to delete our calculated field, Profit.
STEP 1: Select any cell in the pivot table​. Select the Pivot table Tools Analyze tab (FIGURE 12). 

​STEP 2: From the Customizations section of the Ribbon, select Fields, Items, & Sets, and from the drop down menu select, Calculated Field (FIGURE 13).

                                                                  FIGURE 12
                                               FIGURE 13
STEP 3: In the “Insert Calculated Field” dialog box, enter Profit in the Name window. After entering Profit, the Modify and Delete buttons will appear.(FIGURE 14).

STEP 4: Select the Delete button.
STEP 5: Select the Close button. The Profit column has now been deleted from the pivot table and removed from the pivot table field list.

                            FIGURE 14
This is the end of this section. To continue, go to Module 3 Section 3.2  Create, Remove, and Find a Calculated Item; Practice Exercises 17 – 19