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.
- To start, select any cell in the pivot table, then click the Analyze tab on the Ribbon
- Click Fields, Items, & Sets, then click Calculated Field
Add the Calculated Field Formula
When the Insert Calculated Field window opens:
- Type “Bonus” as the name for the new field.
- Press the Tab key, to move to the Formula box
- 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.
- 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: 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.