Every course is tailored to the needs of the group, but this is a typical outline for this level
INTERMEDIATE 1 Day
Pre-requisites
This course is
aimed at delegates who wish to learn more of the functionality of Microsoft
Excel. Delegates should be familiar with the basic operation of Excel, by
either attending the Introduction course, or from personal experience.
Objective
To provide delegates with the confidence, knowledge and practical experience to
be able to use Excel for numerical analysis & presentation of
information.
Overview
Delegates will learn how to use more features in Excel. The emphasis of
the course is on creating flexible solutions that need minimum maintenance.
Independent exercises and troubleshooting are an integral part of the course.
It emphasises hands-on experience through use of sample course files and, if
appropriate, delegates' own worksheets.
Core Topics
Review of Core Skills
Entering
Formulae.
Using Autosum.
Relative and Absolute references.
Using Named Ranges
Naming
Cells
Using Names in Formulae
Apply new names to existing formulae
Automatic creation of named ranges
Creating a list of all Named ranges in the workbook
Database Features
Database Functions
Advanced Filter
Using the Subtotal Function
Pivot Tables
Creating from an Excel list
Modifying source data and range
Changing Pivot Table Fields
Changing the Pivot Table Layout
Adding Multiple Data Fields
Hiding and Unhiding Items
Renaming a Field
Creating a Page Field Report
Creating a Pivot Table from an external Database
Advanced Formatting
Techniques
Validating Cells
Highlighting Invalid data ranges
Conditional Formatting
Management Tools
Auditing
Using Comments
Goal Seek
Scenarios
Linking Data
Linking data between workbooks
Refreshing linked data
Linking data within a workbook
Copying and Moving data between workbooks
Using 3D Formulae
Templates
Creating templates
Saving templates
Using templates
Security
Protecting your workbooks
Locking and unlocking Cells
Using passwords
Creating Read-Only Workbooks