Pivot Table, being the most celebrated feature of Excel, includes multitude of options to manipulate the data in desired way. Once pivot table is created from the source data, you can add fields & items without modifying source data, which comes in handy for doing quick calculations. In this post we will demonstrate adding a Calculated field in Pivot table.
To begin with, launch Excel 2010 spreadsheet that contains pivot table. For illustration purpose, the data source of Pivot table contains fields; Course Names, Studied By (number of students), Total Marks Obtained, and Total Marks.
The Pivot table created out of above mentioned data source seems much like same, except of pre-evaluated Grand Total.
Now in Pivot table, we need to insert a new field without inserting field in data source which will evaluate percentage of total marks obtained by students. For this, head over to PivotTable Tools Options tab, from Calculation group, under Fields, Items, & Sets options, click Calculated Fields
It will bring up Insert Calculated Field dialog, enter an appropriate name of Calculated field. From Formula, enter formula for evaluating calculating field.
As we are calculating percentage, we will enter a simple percentage formula that’s include Total Marks obtained field, and Total Marks field. To quickly add field name in Formula input pane, double-click field name under Fields box. Once formula is entered, Click Add and then Close the dialog.
This will add a Percentage field in Pivot table, containing percentages of corresponding total marks obtained.
You can also check our previously reviewed guides on How to calculate working days in Excel 2010 and How to create custom Conditional Formatting rule in Excel 2010.