Excel is not often thought of as a very good Reporting Tool with users often favouring other programs to summarize lists of information but Excel has a very powerful Pivot table tool ideal for this purpose.
PivotTables are a means of grouping, analysing and summarising data from an existing database. A PivotTable can be created from an Excel database or an external source, such as Oracle. The PivotTable has greater flexibility and power than a Data Table, with 'drag and drop' capabilities to re-arrange and summarise data.
The source data can be:
• An Excel worksheet database/list or any range that has labelled columns.
• A collection of ranges to be consolidated. The ranges must contain both labelled rows and columns.
• A database file created in an external application such as Access.
• Multiple Sheets of data
The data in a pivot table cannot be changed as it is the summary of other data. The data itself can be changed and the pivot table recalculated. The pivot table can be reformatted.
If you are experiencing problems analysing list data check the following:-
Your list is correctly set up with the first row containing the column labels identifying data in each of the columns and no blank rows between the headings and the first row of data.
Your column headings are not ambiguous - i.e. they cannot be confused with function names or range names.
Your column headings are formatted to make them stand out from the data.
Your column headings ideally should not contain spaces - you can remove the spaces completely or replace them with an underscore (_) character.
Your criteria range should only contain a row of headings and blank rows below. The headings must exactly match the headings at the top of your list.
Problems sometimes occur if the criteria range looks blank but perhaps has a space in it.
Ok We are ready to Go!
To Create a Pivot Table
1. Select a cell within your list.
2. Choose Data from the menu bar, then PivotTable and Pivot Chart report.
3. Choose Microsoft Excel List or Database.
4. Choose Pivot Table
5. Choose the Next button to go to step 2 of the wizard.
This step verifies where your list data is. Provided the active cell was within your list when you launched the pivot table wizard, the worksheet range will be your list. Click Next.
Step 3 is where your will select the destination for you Pivot Table and decide on the layout of the Table. Click on Layout.
This screen will enable you to define your Pivot Table. A list of column labels will appear to the right of the screen. Drag and drop the field buttons for the ROWS, COLUMNS and PAGE labels. Each row, column or page can have more than one label.
Drag column labels to the DATA area for the fields to summarise. Excel assumes SUM as the calculation method for numeric fields and COUNT for non-numeric fields. You can change the calculation method by double-clicking the field in the DATA area.
Format your summary field in the DATA area by double-clicking it and choosing Number. Click OK, and then Click OK on the Layout screen.
Select a destination for your pivot table and click Finish.

John Caulfield
http://www.mousetraining.co.uk