What our clients
are saying:

Clear and relevant
material delivered with
a light touch and a sense
of humour – enjoyable,
and very valuable.
DK, Engineer

more...

Courses

Introduction to Excel | Advanced Topics for Business Users | Excel Reports and Charts

Advanced Topics for Business Users (2 days)

Available in Excel 2010

This course will be extremely useful for those who are comfortable and familiar with at least 90% of the material covered in our “Introduction to Excel”, excluding the “Charts and Graphs” module. (For others we recommend either the “Introduction to Excel”, which is an Entry Level Plus course, or “Excel Reports and Charts”, which is an Intermediate Course aimed at improving information presentation skills). There are 8 modules:

Not-So-Common Knowledge

This is a multi-topic warm-up, including:

• conditional formatting;
• grouping;
• formula writing essentials including nesting;
• INDIRECT;
• OFFSET;
• types of errors and how to avoid them;
• formula auditing;
• re-mapping the keyboard to avoid accidental mis-keying; and
• good spreadsheet development practices

Data Management

• absolute and relative references;
• the use and value of ranges, and how to manage them;
• sorting data and using filters;
• data validation and getting around some of Excel’s restrictions;
• using scenarios;
• pivot tables; and
• data consolidation

Looking Up Data

Essential techniques for retrieving data based on user-specified conditions:

• VLOOKUP and HLOOKUP;
• INDEX;
• MATCH
• A complex case study illustrates the advantages and disadvantages of each

Building Excel Workbooks

We work through the building of a business forecast, showing the techniques which can be applied to:

• avoid errors
• capture data only once;
• allow for assumptions to be changed without causing any revisions to the final report structure; and
• delivering a clear and appealing presentation

Testing What You Build

Testing is an often-overlooked, and typically “short-changed” aspect of building Excel applications. We cover the importance of testing, and describe proven testing techniques.

Excel VBA

• essential jargon in everyday terms
• guided tour of the VBA module in Excel
• the macro recorder, and its strengths and weaknesses
• how to use the macro recorder to save time when writing VBA code
• how to assign a macro to a toolbar button
• how to include a macro button within a spreadsheet
• the actual language of VBA
• how program code is structured and how it flows
• how to lay code out on the page to make it readily understandable.
• ranges, and their immense value in VBA.

Functions and Subs

• what is a Function?
• what is a Sub?
• building a Function from scratch – we build a new Excel Function
• Subs – more on program flow – looping, conditional execution
• building a Sub from scratch – we take user requirements, and build a complete application
• errors – why they arise, and how to handle them

Further Exploration

We finish the course with a look at some of the excellent publications available to supplement the course.

The course is packed with examples, and a comprehensive course manual is provided.

The first three modules of this course may be taken together as a separate course (time – 4 hours).