Pivot Table Add a Calculated Field

When you create a pivot table, you can see pivot fields from your original source. If you want, you can also create your own fields, called calculated fields.

A calculated field is a field that uses data from the other pivot table fields and makes calculations on them.

Let’s create a very simple pivot table, using this very simple table. It has 10 names with the number of units sold and revenue made from the sales.

Create a pivot table from the table and check all the pivot table fields.

Navigate to PivotTable Tools >> Calculations >> Fields, Items, & Sets >> Calculated Field to add a calculated field.

After you choose this option, a window will appear. Here, you can add a calculated field.

We would like to reward salespeople for the revenue they made for the company. Let’s add a 5% bonus to their revenue.

Click the Revenue field and add Insert Field (or just double-click). Multiply this value by 5%. Here, you can add a space for more readability, but it’s not necessary. Then change the name of the field to Bonus. The data should look like this.

Click OK to add the field.

Now, we have a new calculated field, called Sum of Bonus, and fields called Sum of Sales, and Sum of Revenue.

You can change the names, but they should be different than that of pivot table fields.

In this example, we just take one field from a pivot table and multiply it by 5 percent.

This is an example of a very simple calculated field, but you can create more complicated ones and use more than one pivot table field in your calculated field.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.