Excel Headlines

Today's author: David Gainer, a Program Manager on the Excel team.

PivotTables are designed to help users make sense of large amounts of data by providing an easy way to build a summarized report. In addition, PivotTables can be rearranged easily, so that once you have some summary data in a PivotTable, you can look at the same information in many different ways with only a few mouse clicks. For a slightly less brief overview of PivotTables as well as a list of the improvements made to the application in Excel 2007, please check out this blog post.

Today I wanted to take a look at calculated items in PivotTables … specifically, what they are, how to create them, and some examples of how they can be useful. I find them to be very helpful, so I wanted to do a little evangelism, as well as point out some little-known features.

Let's start with what they are. According to Microsoft Knowledge Base Article 161882, "A calculated item is a user-defined item in a PivotTable field that can perform calculations using the contents of other fields and items in the PivotTable." While that's technically accurate, it may not be clear. Perhaps a simpler way to think about calculated items is that it is a feature that allows you to create new items (that are not part of the original source data) that can appear in fields in your PivotTable. Here's an example (lifted from the aforementioned KBA) – a picture may be worth 1000 words.

Assume that you have some sales data that you want to summarize in a PivotTable. You have data for four different sales regions (North, South, East, West) for a few months sales.

With a few clicks, you could create a summary PivotTable that looks like this.

So far so good, but let's now pretend there has recently been a reorganization, and the North and East regions are now the NorthEast region. How can you reflect that in your PivotTable without having to modify all the source data? By creating a calculated item which represents the NorthEast region, of course. To do that, you need to first click one of the "Regions" in the PivotTable report. This gives Excel the context of what you are going to add a calculated item to … specifically, that you are going to add an item to "Regions". Next, on the PivotTable "Options" contextual ribbon, select the "Formulas" drop-down and then select "Calculated Item …".

This should present you with a dialog thus:

This dialog is used to create calculated items (unsurprisingly enough). While it may look a bit cryptic, it is pretty straightforward. The bottom-left control shows you the fields available in your PivotTable, the bottom-right control shows you the items in those fields, and the top controls allow you to create your calculated items. For example, to create a new NorthEast region, you simply need to type "NorthEast" in the Name control, = North + East in the Formula control …

… press the Add button, and then press OK to dismiss the dialog. Now you should see your PivotTable with a new item – NorthEast – listed with the rest of the Regions. You can see that Excel simply aggregated the values for the two regions in questions, which is what we asked Excel to do with our "= North + East" formula. Essentially, you have added new information to your PivotTable, and that information behaves just like all the other data in the PivotTable … you can sort it, filter it, pivot it, etc., all without restriction.

If I now use standard PivotTable filtering functionality to filter out the North and East regions, I have a simple three-line PivotTable that reflects the reorganized sales regions, not the underlying source data.

The fun is not quite over yet, though. Once you have created a calculated item, you can continue to modify it without necessarily returning to the dialog we just looked at. For starters, you can type over the caption "NorthEast" and Excel will remember the new caption. Additionally, if you click on one of the calculated fields, you will see that you can see the formula you entered in the dialog is also available (and editable) in the Excel formula bar. Here is a shot, for example, of what happens when I select one of the cells reporting data for the new NorthEast region and hit F2 (the hotkey to edit a formula).

From here I could change the formula (=North+South), add a constant (=North+East+100), or use any of Excel's native functions, and the value in the cell would update when I pressed Enter. It is worth bearing in mind that this changes the formula for that intersection only (in this case, "Apr" of the "NorthEast" region only, so if you want to change all the cells in one fell swoop, best to return to the Calculated Items dialog.

So that is an overview of the feature set. Now let's look at a few examples of more interesting usage cases that were created by one of my co-workers. First, let's look at an example where calculated items are used to show relationships between different classes of products.

As you can see the PivotTable above summarizes sales, cost of sales, and margin for different classes of products – alcoholic beverages, dairy, beverages, etc. In this case, the report author wanted to add another piece of information – the percentage of overall sales that were related to alcoholic beverages. They accomplished this by adding a calculated item to the "Product Department" field that looked like this: =IF('Alcoholic Beverages'=0,NA(),'Alcoholic Beverages'/SUM('Alcoholic Beverages',Beverages,Dairy)). You can see this calculation uses a few Excel functions (IF, NA, SUM) in addition to referencing other items in the field in order to calculate the percentage in question. The reason for the IF and the NA functions is to return #NA if there are no alcoholic sales instead of a divide by 0 error.

Here is another interesting example. It turns out you can use a special syntax to refer to "next" and "previous" items in a field.

In this example, the PivotTable author had actual financial results for FY02 – FY04, and wanted to create a forecast for the next fiscal year based on the actual for those three years. You can see in the screenshot above that they used the following formula: =IF(Year[-1]=0,Year[-2],2*Year[-1]-Year[-2]). While at first this may look pretty cryptic, it is actually fairly straightforward. The formula says "if last year's sales (which is what "Year[-1]" means) is 0, the projection is sales from 2 years ago, else the projection is two times last year's sales minus the sales from two years ago." The [-1] modifiers are simply used to tell Excel to refer to previous and next values in that field, and it is particularly (but not exclusively) useful for financial calculations involving time periods.

That summarizes the calculated items feature. There is a closely related feature called "calculated fields" which should be simple enough to figure out after reading this post (and there are other articles out there on MSFT and other web sites you can refer to if you are interested). If you have any feedback on this feature, we are all ears. Thanks!