Preview
Create and Remove a Calculated Field; Practice Exercises 14 & 15
Continue to use the data file, RANGE TO TABLE TO PIVOT TABLE2.
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.
Constants
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 14: Create calculated fields in a pivot table
STEP 1: Select any cell in the pivot table. Select the Analyze tab on the Ribbon (FIGURE 1).
STEP 2: From the Ribbon Calculations section, select the Fields, Items, & Sets icon (FIGURE 2).
On the drop down menu select, Calculated Field (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.
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).
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).
STEP 14: In the Value Field Settings dialog box, for the Custom Name window, enter “Profit”. Add a space after you 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.
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.
We see our field header, “Sum of Profit” has changed to “Profit”.
We also see that our field header, “Total Sum of Profit” has changed to “Total Profit”.
The pivot table result is shown below (FIGURE 11).
Practice Exercise 15: Remove a calculated field in a pivot table
Continue to 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 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).
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.(Remaining steps apply to FIGURE 14).
STEP 4: In the “Formula” window, delete the 0 but let the = symbol remain.
STEP 5: In the “Fields” section, select the field, Sales (you may have to scroll to see it).
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 Delete button.
STEP 11: Select the Close button. The Profit column has been deleted from the pivot table and Field List.
END OF LESSON
END OF LESSON
Leave a Reply