Pivot Tables - Calculated Items

Pivot Tables - Calculated Items

After putting together my blog on pivot tables yesterday I checked back and realized that a couple of pivot tips have only appeared in my newsletter and were never published here. This one can be very useful and again is one of those "hidden" gems so I thought I would repeat it here.
This pivot table tip is from my December newsletter:
I had someone ask about joining items together in a pivot table. Most people are familiar with calculating values and formulas but not items. The answer is yes- it is very easy to add fields or items together in a pivot table.


Why would you want to add items together?If you recently consolidated sales offices, you might want to show a single consolidated entry in your pivot table. Or, perhaps you have 50 vendors and want to show the top 10 and then have a category called Other Vendors.

In the example below, I have a number of publishers including one called Collins and another one called Harper and Collins. I want to combine or merge the numbers of these two publishers into one for my pivot table.
Below are the steps to do it in a pivot table.


Creating a Calculated Item (Excel 2007)
Select an existing item (in this case one of the publishers)
Select the Pivot Table contextual toolbar and select the Options tab
Click the Formulas drop-down arrow
Select Calculated Item...

A dialog box will appear so that you can name your new item and create the formula

In Name: Type in a new name.
I typed Collins Consolidated 


In Formula: Select the items you want to use in the calculation and the operator.
My formula reads =Collins+"Harper and Collins"
(Excel automatically put the quotes around Harper and Collisn when I inserted them)

Click Add
Click Ok

Then go to your pivot table and click on the down arrow to find your new item. In my case Collins Consolidated.

Now I can deselect Collins and Harper and Collins and the pivot table will only show the newly created item Collins Consolidated.
 

In this way, I can remove the checkmarks from Collins and Harper and Collins and see the summarized information in All Collins in the pivot table.

The nice thing about this is that I can still display the 2 publishers separately if I want.