How to Add Calculated Field to Pivot Table?

by

Pivot tables in Excel provide a powerful tool to group and summarize large datasets, helping you get a bird’s eye view of your data.

They come equipped with a variety of commonly used summary functions, making it quick and easy for you to gather meaningful insights.

However, these built-in functions are not exhaustive, so you might sometimes come across cases where you would need to use a custom formula in the pivot table. This can be accomplished using a calculated field.

In this tutorial, we will explain with a simple example, what a calculated field is, why we need it and how to add one to your pivot table.

What is a Calculated Field in Pivot Table?

Pivot tables in Excel come with a number of built-in summary functions like Count, Sum, Average, Min, Max, etc.

However, if you require a summary function that is not included in the built-in functions, then you can define a calculated field for it.

A calculated field is one that helps you use customized functions and formulae in your Pivot table.

They derive the data for the calculations from other fields and display the results in the form of summary results in the Pivot table.

How to Add and Use Calculate Field in an Excel Pivot Table

To understand how to add and use calculated fields in pivot tables, let us take a simple example.

Let’s create a very basic pivot table first.

We are going to base our pivot table on the following dataset:

dataset for pivot table

The above dataset contains office supply sales data for a company (not real data, I made it up for this tutorial).

Note that the fields included are:

  • OrderDate: Date when the item was ordered
  • Region: Geographical region where the order was made
  • Rep: Name of the sales rep who processed the order
  • Item: Name of the item ordered/sold
  • Units: Number of units ordered/sold
  • UnitCost: Cost of one unit of the item
  • Total: Total sales amount

Please note that we kept the dataset simple to make it easy for you to follow. Pivot tables are usually used to analyze much more complex datasets.

Let’s say we want to create a Pivot table that analyzes this data and displays:

  • The region-wise total sales.
  • The region-wise total sales after deducting a 5% tax amount on items sold.

To accomplish the above two tasks, we need to follow these steps:

  1. Create the Pivot Table to display data by Region
  2. Populate the Pivot Table with required rows, columns, and values.
  3. Add a summary column that displays the total sales amount by Region
  4. Add a calculated field that displays the total sales amount (by Region) after deducting a 5% tax amount.

It’s the fourth step (where we need to calculate total sales after deducting 5%) where we would have a need to insert a calculated field in the Pivot Table. Using that calculated field, we will be able to do this calculation.

Let us go over each of these steps one by one.

Step 1: Create the Pivot Table to display data by Region

To create the pivot table, follow the steps shown below:

  1. Select the range of cells containing your dataset
  2. Navigate to Insert->PivotTable.
Insert a pivot table
  1. From the dialog box that appears, select the radio button next to ‘Existing worksheet’ if you want to display your Pivot table on the same sheet. If you want to display it on a new sheet, then keep the ‘New worksheet’ option selected. We recommend choosing this option.
Select New Worksheet option
  1. Click OK.

This should create a blank Pivot table in your specified location. 

Blank Pivot Table would be inserted

You should also be able to see the PivotTable Fields sidebar to the right of your Excel window.

PivotTable Fields sidebar

Step 2: Populate the Pivot Table with Required Rows, Columns, and Values

We need to now fill in the Pivot table with the basic fields and values. Since we want to summarize by Region, drag the Region field to the Rows area, as shown below:

Add regions to the Rows area

Step 3: Add a Field that Displays Total Sales Amount by Region

Since we also want to display the total sales amount by from each Region, drag the Total field to the Values area, as shown below:

Add Total to the Values area

By default, the Pivot Table should display the ‘sum’ of Total. 

The resulting pivot table

If it does not, then you can do the following:

  1. Click on the drop-down arrow next to the Total field in the ‘Values’ area of the sidebar.
  2.  Select ‘Value Field Settings’.
 Select Value Field Settings
  1. Under ‘Summarize Value Field By’, you can see the list of built-in summarizing functions that come with every Excel Pivot table. Select the Sum function from this list.
Select Sum option in the Value Field Settings dialog box
  1. Click OK.

You should now see the total sales amount by Region in your Pivot table.

Step 4: Add a Calculated Field that Displays Total Sales Amount (by Region) after Deducting a 5% Tax Amount.

To deduct a 5% tax amount, we need to do the following computation:

Total Sales Amount-(Total Sales Amount * 5%)

We have to compute this for each record and then sum it up by Region.

A computation like this is not available in the default Pivot table functions. So we will need to add a calculated field to perform this computation.

To add the calculated field, follow these steps:

  1. Click on any cell of the Pivot Table.
  2. Click on the PivotTable Analyze tab.
  3. From the Calculations group, click on Fields, Items & Sets.
Click on Fields, Items & Sets
  1. From the dropdown that appears, select Calculated Field.
Click on Calculated Field
  1. This opens the ‘Insert Calculated Field’ dialog box.
  2. In the input box next to ‘Name:’, type in the field name you want to give this new calculated field. Let us name this field ‘Total Amount After Tax’.
  3. In the input box next to ‘Formula:’, type in the formula for your calculated field: =Total – ( Total * 5% ).
Enter the formula for the calculated field

Note: At the bottom of the dialog box, there’s a list of fields that you can use in your formula. If you don’t want to type out the field names in your formula, you can simply double-click on the field name in the list and it will get automatically inserted into your formula.

Once you’re done entering the formula click OK.

You should now see your new calculated field displayed in your Pivot Table as shown below:

Calculated field is added to the Pivot Table
Also read: Change Count to Sum in Excel Pivot Table

Important Notes about Pivot Table Calculated Fields

Calculated fields can help you customize Pivot tables according to your requirement. However, there are a few things that one should remember when using them:

  • You cannot use Pivot table totals or subtotals in your calculated field formula.
  • You cannot refer to reference worksheet cells by address or name in your calculated fields.
  • Calculated fields are not available in a pivot table that is OLAP-based.

In this tutorial, we used a simple example to show you how to add a calculated field to a Pivot table. We hope you found the tutorial useful and easy to follow.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment