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.
|
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. |
 FIGURE 4
|
|
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
|
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
|
|
Still use the data file, RANGE TO TABLE TO PIVOT TABLE2.
We want to delete our calculated field, Profit. 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).
|
 FIGURE 14
|