Hands on: Excel Pivot Tables explained

Whether you call it a table, list or flat-file database, a series of records all neatly split into the same fields forces a disciplined order upon a mass of related data. But it doesn’t necessarily help you to analyse it.

That’s where an Excel Pivot Table is so useful. It allows you to look at the same information in many
different ways. You can not only group and summarise the data, but also add depth to it. You can tell Excel which of the fields in the list are to be arranged in rows and columns. You can also designate a page field that seems to arrange items in a stack of pages. You can add a calculated field and twist all the data around, hence the Pivot.

It’s true that with Grouping and automatic Subtotalling, Excel allows you to collapse tables and provide simple summaries on a worksheet, but a Pivot Table lets you drag and drop your columns. It’s much more flexible for analysing information and discovering hidden relationships. The only limitations are that the table of data must include one column that has duplicate values and, in general, some numeric information.

For example, in a table containing a shortlist of winners of pigeon races, there are four fields (Race date, Member, Pigeon and Winnings) and 35 records covering fictional weekend races from 17/5/08 to 26/7/08. To start the Pivot Table Wizard, click within the list and, in Excel versions prior to Excel 2007, choose Pivot Table Report on the Data menu.

After an initial dialogue box that confirms the range to use, you are offered a diagram. If you follow this example, you’ll see the fields from the table above listed.

You can drag Member to where it says Column, Pigeon to where it says Row, and Winnings to where it says Data. This label will automatically change to Sum of Winnings. Click Next and choose whether to have the Pivot Table on the same worksheet or a new one. Click Next and your Pivot Table appears.

More options in Excel 2007
Click within the list as before. Choose the Pivot Table button under the Insert tab. After the initial dialogue box confirming the range, you’ll see the fields listed in a pane called the Pivot Table Field List.

Below are four labelled rectangles. You can drag the fields into these rectangles: Member to Column labels; and Pigeon to Row labels as before. What used to be called Data is now called Values, so drag the Winnings field there. The Winnings will still be summarised in the last rows and columns of the table as before and totalled by default.

The additional rectangle is labelled ‘Report Filter’. This enables you to page through data summaries. Drag the Race date field there. Now you will be able to see the results for a particular race day, or all the results in a particular month.

You’ll see that the Pivot Table Field List box has a number of dropdown arrows. The one at the top right of the box offers you various layouts of the box itself. By default, your table’s fields are listed above four drop zones at the bottom of the box.

The arrow offers other arrangements ­ for example, the drop zones to the right of the field list. The arrows next to the labels for the drop zones allow you to move a field from one zone to another. You can also do this by dragging a field label from one zone rectangle to another.

You can now close the Pivot Table Field List box and examine the Options ribbon of the Pivot Table Tools tab that appears whenever you create a new Pivot Table or click an existing one. At the right-hand end there is a Field List button for displaying the Pivot Table Field List box at any time, a plus and minus (+/-) button to expand or collapse groupings, and a Field Headers button to display or hide the column and row labels.