Use calculated field in Excel Pivot Table

Excel Pivot Table is an excellent tool for analyzing business data. I am talking here to discuss on a facility provided by Microsoft Excel. If you use Excel Pivot Table frequently, sometimes you may require data that can be obtained only from figures of pivot table and not from source data. Consider an example shown below.

 

It simply shows the actual amt, plan amt, change amt and change percentage amt in different expense heads on month wise basis.

Head	Month	Actual_amt	Plan_amt	Change_amt	Change_per
Salary	January	2,000.00	3,000.00	(1,000.00)	(33.33)
Salary	February	1,000.00	1,000.00	-	-
Stationary	January	4,000.00	2,000.00	2,000.00	100.00
Stationary	February	3,000.00	2,000.00	1,000.00	50.00
Marketing	January	3,000.00	2,000.00	1,000.00	50.00
Marketing	February	2,500.00	2,100.00	400.00	19.05

Now, from pivot table you require following output. Please, note that the change_per field below is not the same as in previous table. It is actually calculated from field values from pivot table, i.e. change_per = (sum of change_amt / sum of plan_amt) * 100. This field cannot be calculated from field values from above table easily. So, this can be achieved by using Calculated Field in the pivot table.

Month	Sum of Actual_amt	Sum of Plan_amt	Sum of Change_amt	Change_per
January	9000	7000	2000	28.57
February	6500	5100	1400	27.45
Grand Total	15500	12100	3400	28.10

At first draw a pivot table from first table (source table) without any calculated fields as shown below.

 

Now, in the PivotTable toolbar, go to Pivot Table -> Formulas -> Calculated Field. In the pop up window, type the formula as shown below and name for new field and click add.

The formula used is as below.

Change_per_recalc =  Change_amt / Plan_amt * 100.

 

Then a new field is added automatically in the pivot table in Excel. If not added automatically, you can go to wizard and add it. Only sum function is available for it. Now, finally you will get required output like this.