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.
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
PS: If you liked the tip, please do Like us, Stumble Upon on the Left Socials Bar, or Digg it!