Tuesday, June 7, 2011

Create Calculated Field in Pivot Table [Excel 2010]

Pivot Table being the most celebrated feature of Excel allows multitude of options to manipulate the data in desired way. Once pivot table is created from the source data, you can also add fields & items without modifying source data, which comes in handy for quick calculations. In this post we will demonstrate adding a Calculated field in Pivot table.
To begin with, launch Excel 2010 spreadsheet containing pivot table. For illustration purpose, the data source of Pivot table we created contains fields; Course Names, Studied By (number of students), Total Marks Obtained, and Total Marks.
table 1
The Pivot table created out of above mentioned data source seems much like same, except of pre-evaluated Grand Total.
pivot table
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. For quick inclusion of field name in Formula input pane, double-click field names 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.
percentage 1


Anonymous said...

WOW just what I was looking for. Came here by searching for esthetician average salary
Here is my homepage ; nys esthetician license

Anonymous said...

Creating custom pivot table via PivotGrid

Share It

Related Posts Plugin for WordPress, Blogger...