Creating a calculated field for the pivot table
In addition to selecting among the various summary functions to use on the data, you can create your own calculated fields for the pivot table. Calculated fields are computed by a formula that you create by using existing numeric fields in the data source. To create a calculated field for your pivot table, follow these steps:
- Select any of the cells in the pivot table.
- Click the PivotTable button on the PivotTable toolbar, and then from the pop-up menu, choose Formulas → Calculated Field to open the Insert Calculated Field dialog box.
- Enter the name for the new field in the Name text box.
- Select the Formula text box and then delete the zero (0) after the equal sign (=) and position
the insertion point immediately following the equal sign.
Now you're ready to type in the formula that performs the calculation. - Enter the formula to perform the new field's calculation in the Formula text box, inserting whatever fields you need by selecting the name
in the Fields list box and then clicking the Insert Field button and indicating the operation to be performed on the fields with the appropriate
arithmetic operators (+, -, *, or / ).
For example, I created a formula for the new calculated field called Bonuses that multiplies the values in the Salary field by 2.5 percent (0.025) to compute the total amount of annual bonuses to be paid. To do create this formula, I selected the Salary field in the Fields list box to show =Salary. Then I typed *0.025 to complete the formula (=Salary*0.025). - When you finish entering the formula for your calculated field, click the Add button to add the calculated field to the Pivot Table Field List.
After you click the Add button, it changes to a grayed-out Modify button. If you start editing the formula in the Formula text box, the Modify button becomes active so that you can click it to update the definition. - Click OK to close the dialog box.
As soon as you close the Insert Calculated Field dialog box, Excel automatically adds its name to the PivotTable Field List task pane and assigns it as a Data Item in the data area. The program also adds a new Data Field and makes it the first Column Field in the pivot table.
If you want to hide a calculated field from the body of the pivot table, click the Data Field's drop-down button to open the drop-down list showing all the Data Fields (both calculated and not) and then clear the calculated field's check box before you click OK. To then add the calculated field back into the pivot table, select its field name in the PivotTable Field List task pane and then select Data Area in the dropdown list at the bottom of the task pane before you click the Add To button.
