Showing experienced users how to develop their skills on our 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 Level 1:
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.

Allowing expert users to complete their knowledge on our 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.

Enabling beginners to learn the VBA basics on our 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.

Showing experienced VBA users how to develop their skills on our 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.

Allowing expert VBA users to complete their knowledge on our 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.

Best practices 90-minute online courses.

Overview

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.

Duration

90 minutes each

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.

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.

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.

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.