Create calculated fields in an Excel Pivot Table | CLEARIFY

Working with Calculations in Pivot Tables

Excel provides a specific mechanism to add a calculated column to a Pivot Table. The reason for this is to keep the calculation "tethered" to the pivot table, so that if you had 20 rows one time, and 4 the next, the calculation would always reference the appropriate cells.

Problems with Calculated Fields

Calculated Fields have many powerful options, but there are several significant limitations.

Let's consider this example for an IF formula that says "If my account number begins with 05, then I want to return the value in Column C, else give me 0.00". 

What Excel SEES is "If the sum of my account numbers in column A begin with 05, then I want to return the value, else 0.00". 

Obviously this formula would never work. (NOTE: if you wanted to utilize this type of formula, you would need to use a Power Pivot Table, as it is designed to handle these types of "IF" statements using a language called DAX)

However, for simple numeric calculations, it will do the trick.

Remember:

1. Excel ALWAYS sees a text field as 0 (even if it looks like a number).
2. A Pivot Table Calculated Field bases its calculation on the SUM of the items in a column, NOT a particular item in that column - even if your formula appears that it is doing so.

Creating a Calculated Field

With your cursor anywhere in the pivot table area (so that the Excel PivotTable Tools Ribbons show), choose Calculated Field from Fields, Items, & Sets as shown here:

A small dialogue box appears for you to create the formula.

Simply insert the fields, and type in the appropriate numerical operators to create the calculation.  Type in a name, and then click on Add, then OK.

NOTE that the fields you will use in your formulas must already be chosen for the actual pivot table.

The resulting formula appears in your pivot table.