Abstract
Transcript
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.
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 the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, and select Calculated Item.
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. For the moment, 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 we 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 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 any other item. To permanently remove a calculated item from a pivot table, visit the Insert Calculated Item 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.
