Add a Calculated Field in a Pivot Table in Excel & Google Sheets
This tutorial demonstrates how to add a calculated field in a pivot table in Excel and Google Sheets.
Add Pivot Table Calculated Field
Say you have a pivot table created from the data table below.
- The pivot table is set up with the Product field as the filter, Salesperson as the rows, and the Total as the values.
- Now, add a calculated field that gives each salesperson a 10% bonus based on the total.
In the Ribbon, go to PivotTable Analyze > Calculations > Fields, Items & Sets > Calculated Field…
- Type in a Name for the field, and then in the Formula box, type in your custom formula. Click Add to add your field to the Fields list below. Then, click OK to add the field to the pivot table.
The additional field is added as the last column in this pivot table.
- Your formula can be as simple or as complicated as necessary. For example, say you want to give a 10% bonus for sales over $5,000 but only a 5% bonus otherwise. Modify the Bonus formula to do this with an IF statement.
- In the Insert Calculated Field dialog box, choose the calculated field (here, Bonus) from the Name drop down, and then edit the formula accordingly.
- Click Modify and then OK to update your pivot table.
Pivot Table Calculated Field in Google Sheets
You can also add a calculated field as an additional column when you have a pivot table in Google Sheets.
- Click in your Pivot table to show the Pivot table editor pane on the right hand side of your screen. If the pane does not show, click on the Edit button below your pivot table to make the pane visible.
- In the Pivot table editor, click the Add button in the values section, and then click Calculated Field.
- Type the calculation in the Formula box. The pivot table updates automatically.
- You can, optionally, rename the column for the calculated field. Just type the new name in the relevant cell in the pivot table.