How to add a calculated item to a pivot table

A calculated item is an alternative to adding new rows to the data source.

Lets take a look.

Here we have a pivot table that is summarizing sales data by region.

One option is to modify the source data to replace all instances of East and South with Eastern.

However, we can achieve the same result using a calculated item.

To create a calculated item, first select an item in the row or column field youre working with.

After you select Calculated Item, the Insert Calculated Item dialog box appears.

Note the name of this window: insert calculated item in Region.

For calculated items, the name is very important, since it will appear in the pivot table.

The formula for our new item, Eastern, is very simple.

Its just East + South.

When you click OK, the pivot table is updated to include a new region named Formula1.

However, it’s possible for you to see that the name, Formula1, is clearly not helpful.

Theres just one more thing to do.

Because Eastern combines East and South, we are now double-counting sales from these regions.

With East and South excluded, the pivot table is now correct.

If you try, youll get an error.

To temporarily remove a calculated item from the pivot table, just filter it out like any other item.

To permanently remove a calculated item from a pivot table, visit the Insert Calculated Item dialog box.

Once there, grab the calculated item from the name drop-down, and then hit the Delete button.