Using Calculated Fields

Contributed by: Dawn Griffiths

Difficulty level: Beginner

Problem

You have a PivotTable and want to add a custom formula that refers to one or more fields.

Solution

Suppose you have a table with columns named Salesperson, Units, and Amount. You want to create a PivotTable showing each salesperson’s bonus, which is 2% of their total amount if they sell more than 5,000 units. To solve this problem, you can create a PivotTable that uses a calculated field: a custom formula that uses one or more of the PivotTable’s fields.

You create the PivotTable, including the calculated field, as follows (see Figure 1):

1. Create a PivotTable and add the Salesperson field to the Rows section.

2. Select a cell in the PivotTable and choose PivotTable Analyze ⇒ Calculations ⇒ Fields, Items, & Sets ⇒ Calculated Field to open the Insert Calculated Field dialog box.

3. Type the calculated field’s name in the Name box (for example, Bonus).

4. Add the custom formula in the Formula box. For example, to return 2% of the amount if more than 1,000 units are sold, you would use the formula =IF​(Units>5000, Amount*2%, 0).

Tip

You can add a field name to a calculated field formula by double-clicking it in the dialog box’s Fields list instead of typing it. This helps avoid any typing errors, and it’s particularly helpful for field names containing spaces, which need to be put in single quotes, such as 'Unit Price'.

Figure 1. The Insert Calculated Field dialog box

When you click ...