Microsoft Excel 2007 - Pivot Tables

Rearrange Your Data

An Excel pivot table is a special kind of table that assembles and summarises information from a designated data source. That data source can be an external file or it can be a selection of cells in a worksheet.

When creating the pivot table, you specify what fields need to appear and how to organise the data in the table. Once created, you can change the way that the data is summarised to give you a different view of your information, and it is this feature that puts the "pivot" in pivot table.

You may be wondering exactly what benefit is derived from using pivot tables. The following example will help to demonstrate the analytic power you wield when you use them.

A Worked Example Using Pivot Tables

The data in the worksheet below represents the sales figures for a number of salespeople located in different regions around the country for the year 2008, financial periods 1 to 4. If you want to play along at home, you can download the sample Excel Workbook here. We are going to take that data and use it to create a pivot table and this pivot table will allow us to view the data from different perspectives.

Place the cursor anywhere in the table and and then click Insert > Tables > Pivot Table (click the top part of the button as we aren't interested in inserting a pivot chart at this point.) When the Create Pivot Table window is displayed, Excel knows that you want to select the whole table and checks the Select a table or range button. It also displays the table's name in the Table/Range input box. That's fine, leave it in there.

Further down the window we need to decide where we want to put the pivot table: in a new worksheet or in the existing worksheet. In this example, let's choose New Worksheet and click OK. A new worksheet is created in your workbook and a diagrammatic representation of a pivot table appears on the left with all the fields from the table on the right. Don't worry about the diagram, it's going to disappear shortly.

In the Pivot Table Field List panel on the right, check the boxes next to our fields to select them to appear in the table. Take a quick look at the pivot table you've just created. Well done, but we're not finished yet. Back over on the right hand side, you may notice that the year, financial period and sales fields have been placed in the Sum Values panel when you selected them. The sales value is fine in that panel but it doesn't make sense to sum the year or financial period. Drag the Sum of Year value from the sum panel into the bottom area of the Row Labels panel and then do the same with the Sum of Financial Period.

We now have a hierarchical strucure in our pivot table. At the top of the structure in my pivot table I have the region and inside that I have salesperson, then year, then period. As the year remains constant throughout all the entries yet in the pivot table it is repeating, we need to reposition it to occupy a higher level. In the Row Labels panel drag the year box up to the top and release it. As you drag it upwards, a dark horizontal line indicates where the insertion point will be when you release the mouse. For this manoeuvre, the insertion point will be at the very top. Now the year appears at the top of our pivot table structure and it appears only once. Much better. Let's do the same with financial period. Drag it upwards and drop it under the year.

You should now have something like this. Moving on. In the pivot table, beside each field that has subfields within in it, there is a toggle button (a plus or minus sign) that you can use to expand or collapse the display of those subfields. If we collapse period 1, we reduce clutter and can see at a glance what the total sales were for period 1. This is the total for all salespeople in all regions.

When you have all periods expanded, you can see that each salesperson appears in every period. Let's rearrange the pivot table so that we can quickly see a year to date total for each salesperson. Drag salesperson above financial period. Now each salesperson appears only once in the pivot table, and there are period sales figures for each salesperson. Collapse each salesperson so you can clearly see his or her total sales for the year.

The boss now wants to see period totals for each region. What's that Sootie? That's right we need to drag the region value up to a point between the year and financial period.

There will be a video tutorial for this coming soon!