Every course is tailored to the needs of the group, but this is a typical outline for this level
ADVANCED 1 Day
Pre-requisites
This course is
aimed at delegates who wish to learn the advanced features of Microsoft
Excel. Delegates should have a good working knowledge of Excel, by either
attending the Intermediate 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. Delegates will learn how to create flexible solutions that
can be used by colleagues with limited knowledge of Excel. This course
introduces the delegates to automation by recording macros.
Overview
Delegates will learn how to use the advanced 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
Using Names
Linking workbooks and worksheets
Exporting data
Good design principles
Advanced Pivot Tables
Calculated
fields
Consolidating multiple data sources
Connecting to External data sources
Advanced Functions
IF,
VLOOKUP, HLOOKUP, INDEX, OFFSET, MATCH (Including linking workbooks)
Text Manipulation
Array Formulae
Add-Ins
Analysis
toolpack
Solver
Goal Seek
Introduction to Macros
Recording your actions
5 ways to run your Macro
Using the Visual Basic Editor
Simple editing of the recorded code.
Saving your Macro.