Calculated Fields – Excel Pivot Table Advanced Feature

Insert Calculated Fields feature of Pivot table enable you to add a virtual column, based on some calculation over existing columns, which is physically not present in dataset but gets available in Pivot Table fields.

Calculated Field of Excel Pivot Table is the advanced feature of Pivot Table that helps you in creating calculated fields. A calculated field is the virtual column in Pivot Table that derives its value from other fields by doing some mathematical operations on them.

However, the calculated field in Excel pivot table is like all other fields of pivot table. But they physically do not exist in the source data. They are created by using your own custom formulas in the pivot table.

In below given example, you can see a pivot table with a calculated field which is calculating the profit on sale by 2%.

MS Excel Pivot Table Calculated Field
Pivot Table Data with Calculated Field

However, the source data does not have profit field.

Pivot Table Calculated Field Example

You need to follow these simple steps to insert the calculated field in a pivot table.

1: Select any cell in Pivot table. This will make PivotTable Analyze Tab in excel.

2: Go to Fields, Items & Sets option in PivotTable Analyze tab, select Calculated Field.

Excel Pivot Table Calculated Field Steps

3: In the Insert Calculated Field dialog box, enter name in Name box.

4: Type formula in Formula box, =Sale*2%

Pivot Table Insert Calculated Field Box

5: Click Add.

6: Click OK.

Now you can see that Excel has added “Profit on Sale” column automatically.

Calculated Field Pivot Table

Leave a Reply