Pivot Tables and Analysing Data - SS07

General Description

Microsoft Excel contains many excellent data analysis tools. Pivot tables give you an incredibly easy-to-use tool to summarise large amounts of data in a clear and concise manner. Anyone who deals with large lists of data needs the power of Pivot tables at their fingertips.

In addition, the built in goal seek, scenario and solver tools can help to optimise results subject to constraints specified by the user.

Target Audience

This session is intended for participants wishing to improve on their existing basic working knowledge of Microsoft Excel.

Pre-requisites

Participants should be able to use standard features of Microsoft Excel including how to enter, select and format data, move around a worksheet, use multiple sheets, delete and rename sheets and create basic formulas.

Duration

3 hours

Learning Outcomes

• Identify when to use Pivot Tables
• Set up data for use in a Pivot Table
• Create a simple Pivot Table
• Display multiple summary fields in a Pivot Table Report
• Format data within a Pivot Table Report
• Group data by dates or ranges of numbers
• Use different functions to subtotal data in a Pivot Table
• Display data from within a set of data on separate sheets
• Drill down to the data underlying a Pivot Table Report
• Create your own calculated fields
• Create a chart based on a Pivot Table Report
• Consolidate data from multiple worksheets or workbooks
• Create one and two variable data tables
• Use the Goal Seek tool to find an optimal value for a cell
• Use the Scenario tool to compare several possible outcomes
• Use the Solver tool to set criteria and optimise the end result of a formula