Adding a calculated field to Excel Pivot table

Pivot tables are designed to help users crunch and analyse large amounts of data. Today we will learn                       a very useful feature in Excel pivot tables, which is adding calculated fields. Calculated fields can be used when we need to create some custom fields which was not there in the base data. In this tutorial we will see how to insert a calculated field in Excel 2007, modifying or deleting it and what are the benefits and drawbacks in using the calculated fields.

Inserting a Calculated Field

We have the following data which shows the sales of some stationary items . We have the quantity and the Selling price. Here the total sales is missing. So we will add a calculated field to add the total sales.

We will create a pivot table for the same. See the completed pivot table below. (Are you new to Pivot table – See the link to learn to create a pivot table)

 

Now we will add the calculated field in the pivot table

1. Select the pivot(when you select the pivot you will notice two more ribbon appearing specific for Pivots)

2. Go to Options – Formulas and select Calculated Field

3. In the calculated filed window , give a suitable name for the field (here i gave the name as Sales value). in the formula , drop the required fields from the fields list to create the formula (Quantity * Rate)

 

4. Press ok . This will add the calculated field to the pivot

 

Removing/ Modifying a Calculated Field

Go to formula and select calculated field . Drop down the required filed from the drop down list

Now delete or modify the calculated field accordingly

 

Benefits of Pivot table calculated field

As explained in the beginning these helps in real data crunching and analysis. Basically these will create a custom calculating field which will become a part of the pivot table, the benefit is the formula will change accordingly on the basis of other pivot table filters applied.
Another benefit is that you can also use the Excel functions inside the calculated fields.

 

Disadvantageous of using Calculated fields

These can show wrong results if you are not using it wisely. For example in the above example if the quantities are of different UOM (unit of measurement) then it will be wrong if we remove the products field from the pivot table. It will calculates with the total quantity with the total selling price the result will be wrong. In those cases you can do a custom calculation in the database itself and add that field to pivot instead of adding a calculated field

Another draw back is about using Names and user defined functions inside calculated field. These are not allowable items inside a calculated field

Refer to Pivot table guide to learn more about pivot table tables

 

Download the sample file

Download file to better understand the tutorial - Creating a Calculated Field in a Pivot table

 

 

 

Tags: Pivot table