Using Calculated Items

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 items in a single field.

Solution

Suppose you have a table with fields named Amount and Status, and the Status value can be Pending, Shipped, Returned, or Canceled. You want to create a PivotTable showing the total amount for each status, and include an extra row showing the sum of any refunds—amounts with the status Returned or Canceled—which are then subtracted from the grand total. To achieve this, you can create a PivotTable with a calculated item: a custom formula that refers to one or more items in a single PivotTable’s field.

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

1. Create the PivotTable, and then add the Status field to the Rows section and the Amount field to the Values section.

2. In the PivotTable, select one of the Status field’s values.

3. Choose PivotTable Analyze ⇒ Calculations ⇒ Fields, Items, & Sets ⇒ Calculated Item to open the Insert Calculated Item dialog box for that field.

4. Type the calculated item’s name in the Name box (for example, Refunded); this is the name Excel displays in the PivotTable.

5. Type the custom formula in the Formula box—in this example, =-(Canceled + Returned). You can also add items to the formula by selecting the Region field in the dialog box’s Fields list, then either double-clicking the item in the Items list or selecting ...