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.

calculateditemfield06

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.

calculatedfieldsimple05

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

calculated field or calculated item  http://www.pivot-table.com/

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.

calculatedfieldsimple06

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).

calculatedfieldsimple07

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

calculatedfieldsimple08

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: Pivot Table Calculated Fields

Here is sample video from the Xtreme Pivot Tables course, that shows how to create a calculated field in a pivot table.

And remember, you can get a 25% discount on the Xtreme Pivot Tables course, if you use the coupon code CONTEXTURES, until Friday, November 21, 2014.

Or watch on the the Xtreme Pivot Tables course website.

________________________

This entry was posted in Calculations. Bookmark the permalink.

2 Responses to Create a Calculated Field in a Pivot Table

  1. chandra says:

    excellent. I have learnt a new one. Thanks …. Debra

  2. Colin says:

    Hi
    How do you add in a % variance or Value variance into a pivot table
    i.e.
    Qrt1 14 Qrt1 15 Var $ Var %
    100 114 14 14%

Leave a Reply

Your email address will not be published. Required fields are marked *