Create a Calculated Field in a Pivot Table

One of the top pivot table fears in our survey was “How do you insert a calculated field?” And no wonder it was near the top of the list – pivot table formulas can be very confusing!

So, let’s take a look at calculated fields, and its close companion, the calculated item.

Calculated Field or Calculated Item

The first challenge is deciding whether you need to create a calculated field, or a calculated item. What’s the difference? Where and how do you use each of those formulas?

In Excel 2013, you’ll find both of those commands on the Analyze tab, if you click on the drop down for Fields, Items & Sets.

You can find a detailed description of the differences on my blog post from earlier this year:  Calculated Field vs. Calculated Item.

To quickly summarize the difference —

• A Calculated Field creates a new Value field in your pivot table
• A Calculated Item creates a new item in an existing field

Create a Simple Calculated Field

You can use complicated formulas to create a calculated field, but to get started, we’ll create a simple one. Each salesperson at our company has sold products, and we will give them a bonus of 3% of their total sales.

There are 2 Value fields in the pivot table – Units and Total.

We will add a 3rd Value field – Bonus – by creating a Calculated field.

1. To start, select any cell in the pivot table, then click the Analyze tab on the Ribbon
2. Click Fields, Items, & Sets, then click Calculated Field

Add the Calculated Field Formula

When the Insert Calculated Field window opens:

1. Type “Bonus” as the name for the new field.
2. Press the Tab key, to move to the Formula box
3. The bonus amount should be 3% of the total sales, so type the formula:  =3% * Total
   • TIP: Instead of typing a field name, you can select it in the Fields list, then click the Insert Field button.
4. Click the OK button, to Add the new Calculated Field, and close the window.

NOTE: The 3% amount is typed into the formula, because a calculated field cannot refer to a worksheet cell, or to a named range or named formula.

See the New Field

When the window closes, you will see the new Calculated Field in the pivot table.

The heading will show as “Sum of Bonus” and you could change that to “Bonus ” (with a space character at the end).

Because you have created a new field, you will also see its name in the PivotTable Field List

Download the Sample File

You can download the sample file for this tutorial from my Contextures website. On the Calculated Fields page, go to the Create a Simple Calculated Field section, and click the download link.

Video: Create Pivot Table Calculated Field

In a pivot table, you can create your own calculated fields. In this video’s example, I show how to calculate each salesperson’s bonus, based on their total sales.

________________________