HOW TO CREATE AND USE PIVOT TABLE CALCULATED FIELDS

Pivot table and Calculated Fields

A Pivot Table is a tool in Microsoft Excel that allows you to quickly summarize huge datasets. PivotTables are great ways to summarize, analyze, explore, and present summary data, and in Excel. On the other hand, a Pivot Table Calculated Field is a feature that allows you to create a new field within your Pivot Table to do and display calculations based on values of fields in your dataset.

How to Create a Pivot Table

1. Select the cells you want to create a PivotTable from.
2. Select Insert > PivotTable.
3. Under Choose the data that you want to analyze, select Select a table or range.
4. In Table/Range, verify the cell range.
5. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.
6. Select OK.
7. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.
8. To move a field from one area to another, drag the field to the target area.

How to Create a Calculated Field in a Pivot Table and where to Use it

Adding a Calculated Field to the Pivot Table
Let’s see how to add a Pivot Table Calculated Field in an existing Pivot Table.
Suppose you have a Pivot Table and you want to calculate the profit margin for each retailer:

Here are the steps to add a Pivot Table Calculated Field:
• Select any cell in the Pivot Table.
• Go to Pivot Table Tools –> Analyze –> Calculations –> Fields,Items, & Sets.
• From the drop-down, select Calculated Field.
• In the Insert Calculated Filed dialog box:
• Give it a name by entering it in the Name field.
• In the Formula field, create the formula you want for the calculated field. Note that you can choose from the field names listed below it. In a case where, the formula is ‘= Profit/ Sales’. You can either manually enter the field names or double click on the field name listed in the Fields box.
• Click on Add and close the dialog box.
As soon as you add the Calculated Field, it will appear as one of the fields in PivotTable Fields list.

Now you can use this calculated field as any other Pivot Table field (note that you can not use Pivot Table Calculated Field as a report filter or slicer).