In addition to calculated fields, which behave like new columns in the data source, you can add a calculated item to a pivot table. A calculated item is an alternative to adding new rows to the data source, and these rows can contain calculations which refer to other rows in the data source.
Let’s take a look.
Here we have a pivot table that is summarizing sales data by region. As you can see, there are four regions total: East, Midwest, South, and West.
Let’s assume that there has been a reorganization, and the East and South regions are now combined into a new region called Eastern.
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 you’re working with. In this case, we want to add an item to the Region field, so we’ll select an item in that field.
Then, on Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, and select Calculated Item.
When you select Calculated Item, the Insert Calculated Item dialog box appears. Note the name of this window: insert calculated item in Region.
If you have selected a value in the table, you’ll see the Calculated Item menu item is grayed out.
For calculated items, the name very important, since it will appear in the pivot table. For now, let’s leave the name as Formula1, so you can see how that works.
The formula for our new item, Eastern, is very simple. It’s just East + South.
When you click OK, the pivot table is updated to include a new region named Formula1. If we check the total sales by control clicking the subtotals for East and South, we see that the total for the new item correctly equals East + South. However, you can see that the name, Formula1, is clearly not helpful. To fix this problem, simply select the new region and type the correct name - Eastern in this case.
Now if navigate back to the Insert Calculated Item dialog box, we’ll see the name of our calculated item has been updated there as well.
There’s just one more thing to do. Because Eastern combines East and South, we are now double-counting sales from these regions. To fix this problem, we need to manually filter the pivot table to exclude the East and South regions.
With East and South excluded, the pivot table total is now correct.
Be aware that once you add a calculated item to a field, you won’t be able to move that field into the report filter area. If you try, you’ll get an error.
To temporarily remove a calculated item from the pivot table, just filter it out like other items. To permanently remove a calculated item from a pivot table, visit the Insert Calculated Field dialog box. Once there, select the calculated item from the name drop-down, and then click the delete button.
Keep in mind that there is no undo for all changes you make to calculated items, so be careful.
Also note that once you add a calculated item to a field, you won’t be able to move that field into the report filter area. You can however create a slicer for the field.