How to add a calculated field to a pivot table in Excel?

You can build calculated fields in a pivot table by using your own personal formulas that are compatible with the total of other pivot fields. Calculated fields can accomplish only so much due to their design, but the pivot tables in your Excel spreadsheet gain access to a potent new tool as a result.

Step 1

At first, we must create a sample data for creating the pivot table.

Step 2

Now, select the data range from A1:J19. Click on the Insert tab on the tool bar ribbon and then select pivot table option to insert pivot table for the selected data range.

Step 3

In the next step, Create Pivot Table window appears, make sure the data range is selected as A1:J19 under select table/range option. Now, choose new worksheet to create the pivot table in a separate sheet, then click the OK button.

Step 4

The pivot table is now created in a separate worksheet, as shown below.

Step 5

A calculated field utilises another field's values. To insert a calculated field, follow the instructions below.

Select a cell from the pivot table.

Step 6

Simply select the field you want to edit in your pivot table and click on it. Then select PivotTable Analyze option, next click the Calculations option and navigate to the Fields, Items, and Sets tab. After that, a choice of possibilities will appear; all you need to do is click on the calculated field.

Step 7

After selecting the calculated field, a pop-up option will appear, as seen below. This popup menu has two input options (name and formula) as well as a selection option. In the Insert Calculated Filed dialog box, enter the following −

• Name - The name of the calculated Field that will be displayed in your pivot table.

• Formula - An input option that allows you to insert a formula for a calculated field.

=InsuredValue/1000

• Fields - A drop-down menu that allows you to select other fields from the source data to calculate a new field.

Step 8

In the next step, you’ll see a new column is added at the end of the pivot table.

Conclusion

In this tutorial, we used an example to demonstrate how custom calculated fields are created in a pivot table in Excel.