List Handling and Pivot Tables

Aim

• To raise awareness of the facilities available in Excel for handling and analysing large quantities of data.
• To develop the skills needed to set up data tables and analyse them using Pivot Tables.

Prerequisites

• This module assumes a good working knowledge of Excel. A knowledge of how databases work would also be an advantage but is not essential.

Content

• Functional overview – what can be achieved with an Excel database and when should you consider using Access or alternative packages?
• What is a database? – overview of common terms such as table, record, field, join, relational database, query
• Preparation of the data – steps required before an Excel database can be created
• Using named ranges – assigning a reserved name to the data for ease of analysis and sorting
• Sorting your data – how to run basic and compound sorts, common pitfalls to avoid
• Working with Autofilters – filtering records which meet certain criteria, (e.g. those within a given range or the largest, smallest, most expensive, etc)
• Using query cells and lookup functions – advantages over the Autofilter and how to set them up
• Obtaining filtered statistics – why the common functions won’t give you a total for a filtered list and what to do about it
• Pivot tablesoverview – what they do and how they can be used to reveal complex data trends
• Working with pivot tables – creating a pivot table report, modifying its appearance, refreshing the data source, creating a graphical analysis