calculated field in Excel Pivots

Ever since I published the article on how to display both values and percentages in a pivot table & How to Get Running Totals in Pivot Tables, I am getting more and more emails asking about the other features and tricks within Pivot Tables in Microsoft Excel.

One such useful feature is the ability to add a new calculated field, which is not even present in the base data set, but can be derived from the existing fields.

We can call such a field as Adding a New Calculated Field in the Pivot Table. Once added, it is available to you in a pivot table as a regular field, and can be summed, counted, averaged, etc. with ease.

To Add a Calculated Field, go to the Pivot Table Options Menu, and Find the “Fields, Items & Sets” drop down menu in the ribbon.

Click and select the Calculated Field from the drop down menu.

Type a field name in the Name: field.

We will key in the formula in the next blank cell. Do note that Excel already provides you with a Zero there. You need to remove the Zero first. And then begin to type your formula.

All the existing Fields are listed in the Field List below the formula.

Let’s say we have to calculate Bonus, which is 10% of the Sales Commission.

So the field Name we would give would be called Bonus.

The Formula would be =Sales Comm * 0.10

Click OK to close this popup menu.

You will now notice that a new field called Bonus is active in the Field List.

Double Click or Drag it to the Values Section of the Pivot Table Layout.

Voila! You have now created an new calculation called Bonus, based on existing values within the Pivot table, and it is now available for analysis in the Pivot Table.

You can use this simple technique to create as many pivot table fields, and enhance your analysis.

If you would like more such tips, you may subscribe to the ExcelChamp Excel Tips Newsletter or Excel Tips RSS Feed.

Cheers,
Vinai Prakash

PS: If you liked the tip, please do Like us, Stumble Upon on the Left Socials Bar, or Digg it!