Pivot tables offer another feature- you can add calculated fields.
These can be added into a pivot table, just like the other fields
The calculations will get updated as you refresh the table or modify the fields shown
This can save you from having to add additional fields into your data table
The exercise is in the Pivot Tables Calculated Fields file. The solution is in the Pivot Tables Calculated Fields Result file.
Here is the pivot table we will start with.
We can demonstrate the calculate field feature to calculate the average amount per line item. To begin, click on any cell in the pivot table, then select Calculated Field from the Insert menu.
This pulls up the Insert Calculated Field dialog box.
First we set up a name for the calculated field, in this case, Average Per Line.
Then we build the first leg of the calculation. Click on a field, then click Insert Field to insert it into the formula.
Follow for the second field in the calculation.
Then click OK to complete the calculation (not Add, you click that to start the setup)
Finally, clean up the field name in the pivot table and the number format, by right clicking on any cell in the field and selecting Field Settings.
You can later go back and modify or delete the calculated field by going into the Calculated Field dialog box from the Insert menu.
You also can keep the calculated field, but remove it from the current row or column pivot table fields shown in view.
Calculated fields are a great way to build in more sophisticated calculations into your analysis, without having to bloat your data tables by having to add in a field for each calculation across the whole table.
Return To Excel Tips Index