Making Calculated Field in Pivot Table in Excel

In the earlier blogs, we did the usage of Pivot Tables in Excel. In pivot Tables, we can add the pivot fields ( Columns ) from the Pivot table’s source data fields. But sometimes, we need to have some different data analyses be directly done in the pivot table, without doing any change in the source data. This is why we have got the Calculated Fields in Pivot Tables.

Let us see how they work.

What are Calculated Fields in Pivot Tables?

As the name suggests to you, Calculated Fields are a kind of derived fields, that are derived from the existing fields in our pivot table’s source data. We can define a formula and then take the existing fields as the formula arguments to get the calculated field.

infographics making a calculated field in pivot table in excel

Making Calculated Fields in Pivot Table

Let us say we have a pivot table containing the sales of different products for the years 2021 and 2022 as follows:-

making a calculated field in pivot table in excel step 1

This given pivot table has two existing fields ( values ) as Sum of 2021 and Sum of 2022 which are sales. Column B contains the Product names i.e. A, B… E.

We want to get the difference in sales for both of the years in a separate column in this pivot table. To do this, we would now make a calculated field.

  • Click on any cell in the pivot table and then go to the PivotTable Analyze tab on the ribbon.
  • Click on the Fields, Items & Sets button in the Calculations Group and then Hit on the Calculated Field option.
making a calculated field in pivot table in excel step 2
  • Consequently, this opens the Insert Calculated Field Dialog box. It has two fields Name and Formula. Type “Difference” as the new field name.
making a calculated field in pivot table in excel step 3
  • In the Formula field, we have no formula by this time (=0) . We need to type an equals sign (=) and then double click on 2021 ( Fields Section ).
D:\Documents\Excel Unlocked\Example Excel unlocked\calculated fields in pivot tablw\making a calculated field in pivot table in excel step 4.png
  • Now type a minus sign (-) and then double click on 2022 in Fields Section.
making a calculated field in pivot table in excel step 5

Once your formula becomes this:-

='2021'- '2022'

Click on the Add button and then OK to close the dialog box. As a result, a new field named “Difference” will be added to the pivot table which will contain the difference in sales for 2021 and 2022. A negative difference means that the sales of 2022 were greater than 2021 for that product.

making a calculated field in pivot table in excel step 6

How to Modify or Delete a Calculated Field in Pivot Table

Once we have added a new calculated field into our pivot table, it is very easy to modify its formula or just delete it once we don’t need it.

To modify or delete a field in a pivot table, all you need to do is:-

  • Click on any cell in the pivot table and go to the PivotTable Analyze tab. Go to the Fields Items and Sets button and then go to the Calculated Field option in the shortcut menu.
making a calculated field in pivot table in excel step 2
  • Click on the Arrow Button next to the Field name. This opens the list of all calculated fields we have made for our pivot table. We can choose “Difference” from the list.
modifying a calculated field in pivot table in excel step 1
  • Now you can change the formula for the Difference field, change its name or even delete it.
modifying a calculated field in pivot table in excel step 2

This brings us to the end of blog.

Thank you for reading.

Leave a Comment