• the data must be entered into consecutive columns - no blank columns
• the data must be entered into consecutive rows - no blank rows

Each column must have a unique column name. Use a clearly descriptive name. Once data is organised in this way, it is called an Excel list. An Excel list consists of consistent data in consecutive columns and rows with unique column headings for each column. Excel has been programmed to be able to pick up a list automatically and perform a wide range of operations on the data within it. One of those functions is a Pivot Table Report.

I want to see the expenditure for each teacher displayed as totals for each teacher and for the Year or Department that they are responsible for.

• Download the example spreadsheet - pivotdata.xls
• Start Microsoft Excel (Office 97). Open the spreadsheet called pivotdata.xls
• Save it as pivotdata_yourname.xls

In the Raw Data worksheet, put the cell marker anywhere within the data list and choose Data | Pivot Table Report. The Pivot Table Wizard will start and walk you through the creation process. There are only four steps.

Step 1:

The data you want is in an Excel spreadsheet and it is the spreadsheet you are in so there are no changes to be made here. Click the NEXT button.

You are being asked where the data is. Because you have an Excel list and you put the cell marker within the list, Excel will select the list automatically. You will see the dotted rectangular area on your worksheet. Click the NEXT button.

The only difficult thing about creating pivot tables is determining how to convert the word problem into a set of choices. In this case, the word problem is:

I want to see the expenditure for each teacher displayed as totals for each teacher and for the Year or Department that they are responsible for.

The gray buttons on the right have to be dragged into the white column/row/data areas. Which ones to drag where – that is the question.

• It is the cost data that we want to deal with so drag the Cost ($) button into the data area.
• The teacher's surname will be across the columns in the resulting display so, drag the Surname button into the column area.
• The cost data should grouped by Year or Department. Click and drag the Year or Department button into the row area.

You have finished entering the pivot information, click the NEXT button.

Now it just a matter of choosing whether to place the pivot tables into a new worksheet or into an existing worksheet. Leave it as it is — New worksheet. Click the FINISH button.

The Pivot Table will be created as a new worksheet. You will see a new worksheet tab at the bottom left hand side of the status bar (at the bottom of the screen).

You can use the Pivot Table Wizard to change the arrangement of the Pivot Table Fields. Click one of the Pivot Table Fields — Year or Department. Click the Pivot Table Wizard button on the Pivot Table toolbar. You will be taken to step three of the Pivot Table Wizard. You can now remove or add fields. Drag the Item button into the row area and click FINISH. You should now see that each item is listed under the Year/Department.

Hiding and revealing data

You can hide entire pivot table fields but you cannot hide totals or subtotals shown in rows and columns. To hide detail within a Pivot Table, select the data you want to hide, right-click it, and choose Group and Outline | Hide Detail. Try it. Click in the Year 1 area and then right-click and hide the details.