EU: +44 (0)20 7234 0380
NA: +1 877 406 6078

Microsoft Excel

With courses from Level 1 through to VBA Level 3 you start as a beginner and end up a spreadsheet master.

HeaderLogo-Excel

Training methods available:

Training available for Microsoft Excel versions:
2003, 2007, 2010 and 2013.

Overview

Optimum has been offering Microsoft Excel training from our central London training facility for nearly 20 years. During that time a series of market-leading UK-wide and city-based brands have approached us to train their staff.

Our Microsoft Excel courses are broken down into flexible chunks, enabling you to learn the basics, on our Level 1 certification, right up to our final VBA Level 3 course. Our brand-new central location will provide the perfect learning environment for our experienced trainers take you through the curriculum of your choice. Afterwards you can practice using your own detailed reference guides or quick cards.

These courses are offered on a closed-course basis, held at a time, date and location to suit you.

Excel Level 1

Overview:

This practical hands-on course is designed for spreadsheet users who need to acquire Excel skills quickly and effectively. Practice tasks and case studies ensure that fundamental techniques can be applied quickly in a demanding financial and business environment

Duration:

1 Day

Requirements:

You need to have basic Windows skills, including opening and saving, copying and moving information, and working with a keyboard and mouse

Objectives:

  • Build effective spreadsheets using a range of efficiency features
  • Perform calculations using formulas and functions
  • Use a range of auditing techniques to verify accuracy
  • Format and print different areas of workbook
  • Create and print a range of different charts

Content:

Getting started:
Elements of the Excel workspace; quick ways to select and navigate; different ways to get help; workbook design – tips and best practice

Building workbooks:
Entering data; quick ways to enter data; managing worksheet; copying and moving data; editing and making changes; calculating with formulas; saving and opening/closing workbooks

Calculating with formulas and function:
Creating a formula; relative and absolute references; precedence/ order of calculation; overview of functions; statistical and mathematical functions; creating and using range names; using data validation

Presentation techniques:
Formatting shortcuts; using Format Cells; changing column widths and row heights; inserting extra rows and columns; hiding rows and columns; freezing panes; conditional formatting; custom number formats

Printing:
Deciding what to print; checking output with page break preview; checking print preview; changing page setup (including headers and footers); printing

Charting:
Creating a chart quickly with the chart wizard; using the chart toolbar; range finder in charts; fine tuning your chart; adding and removing data; changing the scale; setting fill effects and special effects; printing a chart with the data; creating your own custom chart, troubleshooting charts

Excel Level 2

Overview:

This is a task based, practical course designed for Excel users who need to consolidate skills and extend their knowledge. Starting with a review of basic features, participants will explore the capabilities of some of the powerful calculation and analysis facilities.

Duration:

1 Day

Requirements:

You need to understand the basics of Excel, be able to construct formulas and simple functions, and modify workbook structure by inserting/deleting sheets, rows and columns.

Objectives:

  • Organise and manage worksheets and workbooks
  • Link and consolidate data across worksheets and workbooks
  • Use complex and nested formulas and functions
  • Import data from other sources, including text files and web data
  • Manage data lists and text
  • Analyse data using pivot tables

Content:

Reviewing essentials:
A review of formulas and functions; absolute and relative references; using range names.

Working with complex formulas:
Using logical and conditional functions; nesting functions; using lookup and reference functions; working with text functions; using ‘text to columns’.

Linking and consolidating data:
Summarising data from different worksheets and workbooks; consolidating by position or by label; 3D formulas; linking and embedding; importing and exporting data; creating and using hyperlinks.

Managing lists:
List rules and conventions; shortcuts and tips for data entry in list format; using outlines; sorting and filtering a list; extracting data to another location; adding and removing subtotals.

Analysing data:
Creating a pivot table report; modifying and formatting a pivot table; creating charts from pivot tables; using slicers to interactively filter data and charts and sparklines to present data in an understandable way.

Excel Level 3

Overview:

This is a task based, practical course designed for professional Excel users who need to acquire advanced skills and to customise Excel. Participants will explore the capabilities of some of the powerful calculation and analysis facilities.

Duration:

1 Day

Requirements:

To attend this course you must be able to select non-adjacent ranges (such as data from column A, D and F), use logical and lookup functions, create and manage linked formulas, sort and filter data in lists, and create and modify charts.

Objectives:

  • Use modelling and analysis tools
  • Save views, scenarios and report settings to print compound reports quickly
  • Create, protect and use workbook templates
  • Share workbooks with other users and maintain a history of changes
  • Customise Excel to suit your way of working
  • Record and run a macro to automate repetitive tasks

Content:

Data Analysis and Modelling Tools:
Finding an answer with ‘Goal Seek’; using solver; setting up a data table; comparing and summarising results with scenarios; saving different solutions.

Views and reports:
Defining and saving different views of the same workbook; saving scenarios and solver solutions as views; preparing a composite report; showing views; printing reports.

Sharing a workbook:
Saving, updating and reviewing changes; keeping a change history; merging copies of the same workbook; preparing copies of a workbook to be reviewed; troubleshooting merged workbooks.

Creating templates:
Conditions and benefits of templates; where templates are stored; special templates (such as BOOK.XLT and SHEET.XLT); special locations for templates; setting format styles within templates.

Customising Excel:
Customising toolbars and menus; adding and removing commands from toolbars; creating a new toolbar; Excel options; calculation options.

Automating repetitive tasks:
Preparing to record a macro; naming/editing a macro; recording commands; assigning a macro to a keyboard shortcut or toolbar button; recording relative or absolute references.

Excel VBA Level 1

Overview:

This modular hands-on course is aimed at experienced Excel users who need to automate repetitive tasks and customise Excel. Participants will acquire a sound working knowledge of VBA, to develop and automate spreadsheet solutions. The modular design includes practical examples and case studies.

Duration:

1 Day

Requirements:

This course assumes that you have an excellent knowledge of Excel. Previous programming experience is not required.

Objectives:

  • Record and run macros
  • Edit VBA code to make macros more efficient
  • Add user interaction to macros in the form of message boxes and input boxes
  • Understand the Excel Object Model, including objects, properties and methods
  • Apply decision making techniques and use loops to control macro behaviour

Content:

Overview of Macros:
Programming basics; recording and running macros in different modes; where to store macros; different ways to run a macro.

Overview of VBA:
Navigating the Visual Basic Editor; identifying your macro code; reading, editing and interpreting the code; inserting new modules; creating new procedures.

Interactive subroutines:
Displaying message boxes and input boxes; Declaring variables; using variables and constants; calling one procedure from another; passing variables; examining the scope, visibility and lifetime of variables.

Objects, properties and methods:
An introduction to the Excel Object Model; working with collections and containers; examining how to refer to objects; selecting and manipulating  common objects; interrogating and setting object properties; using the Immediate Window.

Decision making and control structures:
Making decisions with IF and Select Case statements; using Do and For loop control structures.

Excel VBA Level 2

Overview:

This practical, hands-on course is aimed at participants who have used Excel VBA. You will enhance your knowledge of VBA, automating spreadsheet solutions. The modular design includes practical examples and case studies.

Duration:

1 Day

Requirements:

This course assumes that you have good knowledge of Excel and experience editing and writing macros in the VBA editor.

Objectives:

  • Explore the object model using the object browser and other tools
  • Design and initialise a user-forms
  • Implement error handling to make your macros more robust
  • Create macros that respond to events, such as when a workbook is opened, or changed

Content:

Review of objects, properties and methods:
How to refer to objects; selecting and manipulating ranges; interrogating and setting object properties; using the Object Browser.

User defined functions:
Using VBA to write custom functions; understanding the structure of functions; creating user-defined functions with single and multiple arguments; testing functions; putting functions to work in VBA or in a worksheet; creating and distributing an add-in.

Responding to ‘events’:
Event handlers; creating code that will respond to application, workbook and worksheet events; using events to trigger macros.

Creating and using forms:
Designing a user-form; form controls and properties; initialising and showing your dialog box; assigning commands to form controls; responding to form events; running a user-form from a worksheet.

Error handling and debugging:
Types of error; setting breakpoints; using the immediate window; stepping through code; resetting; using handling run-time errors; creating error-handling code; ON ERROR and RESUME statements.

Excel VBA Level 3

Overview:

This is a practical, hands-on course aimed at participants who have used VBA in Excel and have a good understanding of Objects, Properties, Methods and Events. You will consolidate and enhance your knowledge of VBA, automating spreadsheet solutions using PivotTable and Chart collections and interacting with other Microsoft examples. The modular design includes practical examples and case studies.

Duration:

1 Day

Requirements:

This course assumes that you have very good knowledge of Excel and experience writing procedures in the VBA editor.

Objectives:

  • Consolidate existing VBA knowledge
  • Build and manipulate charts in VBA
  • Build and modify PivotTables in VBA
  • Interact with other Microsoft applications

Content:

Excel VBA Foundation:
Briefly consolidate VBA foundation skills; VBE tools to edit code efficiently; the object hierarchy; variables and control structures; event programming; error handling; user forms

Creating and manipulating Charts:
Chart locations; using the macro recorder; creating charts that are either embedded or on a separate chart sheet; setting a dynamic data source; activating, formatting and deleting charts; looping through a chart collection

Creating and manipulating PivotTables:
Identifying relevant PivotTable objects; PivotTable compatibility; creating a more complicated PivotTable including using calculated fields; creating a ‘reverse’ Pivot; refreshing PivotTables

Working with other applications:
Use Excel VBA to interact with other Microsoft application; create PowerPoint presentations that display Excel charts; create Word documents that include an Excel range; work with Access recordsets

Online

We have created a series of 90-minute online course modules so you can continue your Microsoft Office learning in the most cost-effective and time-efficient way. These modules are also available on a closed-course basis to your employees only.

EXCEL FUNDAMENTALS:

This session is a springboard for new users to get to grips with Excel, but also provides positive tips and tricks for experienced users. It is a hands-on review of essential Excel techniques with focus on best practice and time-saving strategies, laying the groundwork for many of the subsequent modelling techniques a user is likely to encounter.
Download Full Course Outline

EXCEL MACROS AND AUTOMATION:

This session begins by introducing the concept of macros, an essential aid to the automation to repetitive spreadsheet tasks. The second part of the module will explore some of the key areas of the Visual Basic Editor and explore some VBA editing practices.
Download Full Course Outline

EXCEL LOGICAL, LOOKUP AND REFERENCE FUNCTIONS:

This session is designed for Excel users who need to extend their confidence when building and nesting formulas. The session will explore some of the more advanced decision making and look up functions in Excel that are vital for many spreadsheet models.
Download Full Course Outline

EXCEL MANAGING AND ANALYSING DATALISTS:

This session is designed for users who need to manipulate and analyse large amounts of data within their spreadsheet models whether it be within a single worksheet or across multiple worksheets.
Download Full Course Outline

Excel Video Tutorials

Optimum regularly produce video tutorials for Microsoft Excel.

Below are just two of these helpful tips. A full library is available on our YouTube Microsoft Office playlist.

Using Excel’s VLOOKUP Function

Using Excel’s Text Functions – LEFT, FIND & PROPER

Optimum has worked with many of the UK’s leading organisations to develop bespoke Excel training programmes for graduates and other staff who will encounter complex spreadsheets and financial models during their day-to-day role.

We can effectively stream your graduates according to their ability and deliver an appropriate level of Excel training. This will ensure they have a sound understanding of Excel before proceeding to more advanced Financial Modelling exercises.

We also provide refresher Excel training courses for seasoned professionals.

We have provided Excel graduate training programmes and refresher courses to a variety of different industries including banking, law, retail, professional services and more. We offer:

  • An online assessment and streaming tool
  • Various levels from introductory Excel to bespoke-built advanced financial modelling
  • Training venues: our London Bridge training centre, any suitable hired venue or we can come to you
  • Instructor-led training with task-based practical exercises
  • Self-teach training programmes
  • Supporting training manual to all graduates
  • Post training online evaluation and reporting