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:

If we wanted to use 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 "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 - learn more here.)

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

**Remember:**

- Excel ALWAYS sees a text field as 0 (even it looks like a number).
- 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.

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.

## Comments (no comments yet)