Exceljet

Quick, clean, and to the point

Pivot table calculated item example

Standard Pivot Tables have a simple feature for creating calculated items. You can think of a calculated item as "virtual rows" in the source data. A calculated item will not appear in the field list window. Instead, it will appear as an item in the field for which it is defined. In the example shown, a calculated item called "Southeast" has been created with a formula that adds South to East. The pivot table displays the  correct regional totals, including the new region "Southeast".

Fields

The source data contains three fields: Date, Region, and Sales. Note the field list does not include the calculated item.

Field list does not show calculated item

The calculated item was created by selecting "Insert Calculated Item" in the "Fields, Items, and Sets" menu on the ribbon:

Select "Insert calculated Item" from this menu

The calculated field is named "Southeast" and defined with the formula "=South + East" as seen below:

The Insert Calculated Item window

Note: Field names with spaces must be wrapped in single quotes ('). Excel will add these automatically when you click the Insert Field button, or double-click a field in the list.

After the calculated item is created, the East and South regions must be excluded with a filter to avoid double-counting:

South and East regions excluded to avoid double-counting

Steps

  1. Create a pivot table
  2. Add Region as a Row field
  3. Add Sales as a Value field
  4. Create the Calculated item "Southeast"
  5. Filter Region to exlude East and South

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.