ADVANCED COURSE OUTLINE

One day

Keyboard familiarity.  Must have attended Excel 2003 Intermediate Course or have a proficient working knowledge at that level. 

At the end of this course delegates will be able to use and apply advanced features of Excel 2003.

 Keyboard Shortcuts

Organise & Manage Data in a Database

• Using a list as a database
• List management
• Using the Data Form
• Converting an Excel Spreadsheet to Microsoft Access
• Creating Access Forms & Reports from an Excel spreadsheet

Sort and Filter Data in a List

• Sorting Data
• Working with filters
• Filtering using Autofilter
• Filter using Custom Criteria

Create Conditional IF Functions

• Use the IF() Function
• Use the IF() Function Combined with AND()
• Use the IF() Function Combined with OR()
• Use More than one IF() Function and Nest Them

Outline a Worksheet

• Create and Remove an Outline
• Show and Hide Details in an Outline
• Change an Outline’s Organization

Auditing Formulas

• Understand the Audit Features
• Cell Precedents and Dependents
• The Audit Toolbar - Audit the Worksheets Visually, Use Tracer Arrows

Goal Seek & the Solver

• Find a Solution with Goal Seek
• Using the Solver

The Scenario Manager

• Create and View a Scenario
• Change a Scenario
• Remove a Scenario
• The Scenario Summary

Create and Work with a Pivot Table

• Change the View of the Data within the Pivot Table
• Name Fields and Items in the Finished Pivot Table
• Update a Pivot Table
• Customise a Pivot Table
• Use External Data to Create a Pivot Table
• Consolidate Data from Multiple Ranges into a Pivot Table
•  Create a Chart from a Pivot Table

Create Custom Formats

• Create Custom Formats to Emphasise Data on a Worksheet
• Using conditional formatting

Use Styles

• Create and Apply a Style
• Copy Styles between Workbooks
• About the Normal Style

Create a Macro

• Create a Macro using Macro Record
• Execute and Assign a Macro to a Shortcut Key
• Execute and Assign a Macro to a customised Button
• Run a Macro