Overview

Would you want to learn about how to create summarized information in excel. You can create a Pivot Table in excel and create your own calculated field to perform analysis on the pivot table. You can also use the calculated fields to perform further calculations or analysis.

If you not aware then, Pivot tables are used to Summarize, Sort, Reorganize, and Group, Count, Total or Average data.

Let’s take an example of Employee information which looks as below.

 Let us understand if we need to calculate new Fields Basic, HRA and Bonus component of the salary using calculated fields as per the percentage below we will have to do that using calculated fields

The Basic should be 50 % of Salary

The HRA should be 20 % of Salary

The Bonus should be 7% of Basic

Let’s begin with creating Pivot table with Employee Name and salary.

Step 1: Select the Employee Table (A1:H21).

Step 2: Go to INSERT tab and click on PivotTable in Tables grouping.

Step 3: Create PivotTable Dialog Box will appear.

Click on OK so that PivotTable is placed in the New Worksheet

Tip: You can also use shortcut keys. Press Alt + D. Then press P and then press F

Step 4: Drag the Employee Name Field in ROWS area.

Step 5: Drag the Salary to VALUES area.

Step 6: Go to ANALYZE Table in PIVOTTABLE TOOLS Section.

Step 7: Click on Fields, Items, & Sets in the calculations group.

Step 8: Click on Calculated field

Step 9: Enter the Name. I have mentioned Basic Salary for this example.

Step10: Enter the Formula.

Step 11: Select Salary USD and click on Insert Field and the add * 0.50

Step 12: Click on OK

Step 13: Result

Step 14: Follow the steps from 6 to 8

Step 15: Enter the Name. I have mentioned HRA for this example.

Step 16: Enter the Formula.

Step 17: Select Salary USD and click on Insert Field and the add * 0.20

Step 18: Click on OK

Step 19: Result.

Step 20: Follow the steps from 6 to 8

Step 21: Enter the Name. I have mentioned Bonus for this example.

Step 22: Enter the Formula.

Step 23: Select  Basic Salary and click on Insert Field and the add * 0.07

Step 24: Click on OK

Scope of Usage:

• Can be used to perform calculations on other fields in the pivot table
• Can be used to write your own formula
• Can be used to use derived values for new calculations
• Can be used to compute statistical information on summarized pivot data
• Can be useful when you want to use all data from certain fields in the formula