MS-Office-logo

As an alternative to the standard Introductory, Intermediate and Advanced courses, why not consider our additional courses offering a fast-paced, packed programme in one day covering multiple levels as detailed below:-

  • Excel the Basics (covering topics over Introduction and Intermediate levels)
  • Excel Beyond the Basics (covering topics over Intermediate and Advanced levels)
  • Excel Above and Beyond (covering topics over Advanced and further Advanced)
  • Excel Automating Reporting (similar to Excel Above and Beyond but offering more features on Macros and introducing Forms)
  • New course coming soon …. Excel – Power User Data Analysis – a great new course for Power Excel users! – ask our team for more information

Microsoft Power BI (Business Intelligence) Courses

  • Learn how Power BI can transform your company data into rich visuals so you can focus on what matters to you
  • Create stunning interactive reports to communicate your message effectively
  • Live dashboards provide a 360 degree view of your business
  • Important metrics in one place
  • Use the Power BI Desktop to combine data from databases, files and web services

For all the Power BI courses available, see the Standard Courses + Power BI section of this website

Visual Basic for Applications (VBA) Courses for Excel

  •  Learn how to exploit the power of Excel and its programming capabilities to automate tasks and increase productivity

All versions of Microsoft Excel are supported – if you require a Course Outline for specific version, please contact our Sales Team – see our Contact Page for more information

  • Excel 2003 – Excel 2007 – Excel 2010 – Excel 2013 – Excel 2016 Excel 2019 – Office 365

Excel The Basics

Target Audience

Aimed at new users of Excel or users who want to ‘fill in the gaps’ in their knowledge, this will give delegates the rudiments with topics at Introduction to Intermediate levels.

Delegates will learn how to create, layout and manage spreadsheets incorporating the use of formulae, absolute cell references and links across sheets and workbooks.

pdf download

DOWNLOAD

Delegate Pre-Requisites

No prior knowledge of Excel is required

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Excel Workplace

  • The Ribbons
  • Customising Quick Access Toolbar
  • Customising Status Bar
  • Office Backstage View
  • Using Autocalculate

Data Editing

  • Adding/Editing Data/ Undo/Redo
  • Cut & Paste & Clipboard
  • Using Smarttags
  • Using Autofill & Flashfill techniques
  • Autocomplete
  • Adding Comments
  • Saving options, File Formats & Compatibility Mode

Getting Around & Data Selection

  • Moving around a worksheet
  • Finding & Replace
  • Quick tips for selecting areas
  • Moving & copying

Formatting your Sheet

  • Adding / removing / resizing columns & rows
  • Manipulating multiple columns & rows
  • Changing Font & Attributes with Live Preview
  • Format Painter
  • Alignment icons
  • Cell orientation
  • Indent icons
  • Wrapping text and forcing a new line
  • Merging cells or centering across selection
  • Borders and shading
  • Format Painter
  • Using Autocorrect
  • Quick Formatting using Tables & Styles

Multiple Worksheets

  • Selecting / Rearranging Worksheets
  • Renaming a Worksheet
  • Deleting & Copying  Worksheets
  • Hiding Worksheets
  • Hiding Columns and Rows
  • Grouping Worksheets

Using Formulae

  • Adding Operator Icons to Quick Access Toolbar
  • Building formulae—tips & tricks & BODMAS
  • Workshop session with lots of formulae
    building exercises
  • AutoSum / Average / Maximum / Minimum
  • Working with percentages
  • Working with dates
  • Linking formulae across worksheet & workbooks
  • Editing links
  • Using absolute cell references
  • Formulae Auditing
  • Moving & copying options with formulae
  • Converting a formula to a value

Numeric Formatting

  • Using the icons
  • The menu options
  • Working with dates
  • Creating a custom format

Sorting & Filtering

  • Introduction to Filters
  • Single level sorting

Preparing to Print

  • Page Layout View
  • Page Break Preview
  • Controlling Page Breaks
  • Headers & Footers
  • Repeating Headings

Excel Beyond the Basics

Target Audience

Aimed at regular users of Excel, this one day event will look at a wide range of features, functions and tricks to help you manage large and complex workloads, be more productive and save time.

pdf download
DOWNLOAD

Delegate Pre-Requisites

All delegates should have a sound knowledge of building formulae using operators and functions together with the use of range names.

Delegates should be able to use absolute and relative referencing and be familiar with autofill techniques together with linking formulae across worksheets and workbooks

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

Visual Basic

If you want to learn more about creating and editing macros, we offer several Visual Basic courses – see our Excel course outlines page from our website for more information and links to the Visual Basic courses available!

The Content

Absolute Rows & Columns

  • Partial Absolutes – Row & Column
  • The Formulae Auditing Toolbar

Range Names

  • The benefits of using range names
  • Rules for creating range names
  • An alternative to using absolute referencing
  • Creating range names from headings
  • Using range names to navigate
  • Pasting range names into a formulae
  • Tips & Tricks

Data Consolidation

  • Consolidating data across worksheets
  • Consolidating data across workbooks
  • Creating links

Sorting

  • The pitfalls of sorting – tips & tricks
  • Removing empty columns / rows quickly using select blanks feature
  • Sorting Data using Custom Lists
  • Multi-level sorting

Subtotalling & Outlining

  • The Functions available
  • Multiple Sub-Totals
  • Using the Outline feature

Data Tables

  • The Table Design Tools
  • Using the total row feature
  • Filters in tables
  • Calculated Columns

Removing Duplicates

Conditional Formatting

  • An introduction to Conditional Formatting
  • Highlight cell rules
  • Top & Bottom rules
  • Data Bars
  • Colour Scales
  • Icon Sets

Pivot Tables

  • The Pivot Table Design Tools
  • Changing the Structure / Pivoting
  • Using SUM, AVERAGE, MAX, MIN, COUNT
  • Subtotals & Grand Total
  • Grouping data by Date
  • Grouping data by Value
  • Grouping data by Text
  • Running Totals
  • Percentages
  • Pivot Table Styles for quick formatting
  • Report Layouts – Compact, Outline & Tabular
  • Filtering Pivot Tables using Slicers
  • Seeing the Details using Drill Down
  • Calculated Fields
  • Using Show Report Filter Pages to offset
    reports to separate sheets

Creating a Pivot Chart

  • The benefits of Pivot Charts
  • The Pivot Chart Design Tools

Lookups

  • VLOOKUP and HLOOKUP
  • Error trapping using IFERROR
  • Nesting IFERROR with VLOOKUP and HLOOKUP

Index Match

  • An alternative to VLOOKUP
  • Nesting IFERROR with INDEX MATCH

Data Validation

  • What is data validation?
  • Date validation options
  • List validation options
  • Using Tables & Range Names to create a dynamic list where additional items added to the end of a list are automatically included

Logical Functions

  • The IF Function
  • Nesting multiple IF’s
  • Nesting IF with AND, OR  Functions
  • SUMIF
  • COUNTIF

Excel Above and Beyond

Target Audience

Aimed at high level users of Excel, this will look at more advanced features including techniques for nesting formulae using a variety of functions, with a workshop feel to the session, as well as working with array formulae and exploring more advanced features in Conditional Formatting.  It includes an informative introduction into using PowerPivots, part of the Power BI tools within Excel, as well as an introduction into recording Macros to automate reporting.

pdf download

DOWNLOAD

Delegate Pre-Requisites

Aimed at delegates with a high level of experience using Excel, preferably having attended the Excel Beyond the Basics course.

All delegates should have a sound knowledge of building formulae using various functions but who require to further their skills by using more complex formulae building.

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

Visual Basic

If you want to learn more about creating and editing macros, we offer several Visual Basic courses – see our Excel course outlines page from our website for more information and links to the Visual Basic courses available!

The Content

Using Index Match

  • INDEX MATCH vs VLOOKUP
  • Nesting with IFERROR

Nesting Functions

  • Tips and Tricks when nesting functions
  • Understanding Nesting in more detail using a variety of functions
  • Combining IF’s with VLOOKUP
  • Nesting MATCH and INDEX functions
  • Nesting text functions LEFT, RIGHT, MID PROPER, FIND, SEARCH
  • Nesting INDIRECT
  • Nesting workshop to explore other options

Auditing Worksheets

  • Evaluating Formulae

Using Array Formulae

  • Why use Array Formulae?
  • Incorporating other functions

Offset Function

  • Creating the OFFSET formula
  • Incorporating other functions
  • Creating the Combo Box
  • Using Concatenate
  • Insert a chart & linking the title to the Concatenate formula

Useful Date & Time Functions

  • Using variety of date functions including NETWORKDAYS, WORKDAY
  • Working with time in formulae
  • Using Elapsed Time

Advanced Filters

Dependent Data Validation

Conditional Formatting – More Features

  • Custom conditional formatting rules
  • Utilising formulae within conditional formatting
  • Applying formatting to entire row
  • Creating a dynamic record highlight
  • Comparing values
  • Creating banded columns
  • Comparing data on different sheets
  • Formatting values using LARGE, SMALL
  • Using dates in conditional formatting
  • Separating dates within a list
  • Using stop if true
  • Conditional formatting workshop

Introduction to PowerPivot

  • What is PowerPivot?
  • How to access data from multiple sources
  • How to generate a PowerPivot Table & Chart
  • Demonstration of features

Introduction to Macros

  • How to record a Macro
  • How to access recorded Macro
  • Tips & Tricks on Macro Recorder

Excel Creating Dashboards

Target Audience

Excel Dashboards provide insight, analysis and even alerts. They are interactive and dynamic, and have a wide range of applications, providing help with project management, customer service, KPI management and forecasting displaying key trends, comparisons and data in small graphs or tables.  They are also often interactive, which allows the user to filter data and switch views easily.

This course will help you to create advanced workbooks and worksheets and graphical features that you then use to create dashboards giving delegates the fundamentals to analyse data, extract actionable intelligence from it and present that information to decision makers is in a clear visual and dynamic manner.

pdf download

Delegate Pre-Requisites

Attendees should be regular users of excel, with a good knowledge of working with excel functions and formulas. Previous use of Excel Charts and Pivot Tables would be advantageous.

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Dashboard Fundamentals

  • What is a dashboard
  • Dashboard Key Points
  • Do’s and Don’ts

Data Preparation

  • Specialized Functions
  • VLOOKUPS
  • INDEX MATCH
  • OFFSET
  • Nested Ifs
  • Combine Functions

Automating Workbook Functionality

  • Applying Conditional Formatting
  • Working with Form Controls

Visualizing Data with Charts and Graphics

  • Creating Charts
  • Modifying and formatting Charts
  • Using Advanced Chart Features
  • Creating Sparklines

Analysing Data with PivotTables, Slicers, and PivotCharts

  • Creating a PivotTable
  • Analysing PivotTable Data
  • Presenting Data with PivotCharts
  • Filtering Data by using Slicers

Excel VBA The Basics

Target Audience

This course is intended for people who have experience in using Excel and wish to exploit its programming capabilities to automate tasks and increase productivity

pdf download

DOWNLOAD

Delegate Pre-Requisites

Aimed at delegates with a good working knowledge of Excel preferably having attended the Excel Beyond the Macro Recorder course

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Introduction

  • Creating and recording macros
  • The Developer ribbon
  • Objects and object models
  • Calling a macro
  • Using buttons and hotspots
  • Security and trusted locations

Getting started with VBA

  • The Visual Basic environment
  • Adding a module
  • Writing a Sub Procedure
  • Calling the Macro Sub
  • Introducing Variables
  • Declaring Variables
  • InputBox and MessageBox

Referencing Cells & Objects

  • Referring to cells in a worksheet
  • Using Range
  • Selections and Ranges
  • Using Cells
  • Cell Properties
  • Cells and Ranges within Ranges
  • Offset

Conditionals

  • IF, ELSE, ELSEIF
  • Select Case

Loops

  • FOR Loop
  • DO Loop

Excel VBA The Next Level

Target Audience

This course is a follow-on from out Excel VBA  The Basics course.  It takes interaction with the Excel environment further, allowing the dynamic creation and population of worksheets and charts, and moves onto a comprehensive coverage of application-designed forms and dialogs.  It also takes a closer look at the VBA language which is essential for this interaction.

pdf download

DOWNLOAD

Delegate Pre-Requisites

Aimed at delegates with experience of using Excel VBA preferably having attended the Excel Beyond the Macro Recorder and VBA The Basics course

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Exploiting Excel

  • Workbooks and Worksheets
  • Collections and accessor methods
  • Adding to collections
  • Names arguments
  • For Each and Count
  • Adding charts
  • Events
  • Timers

Further Visual Basic

  • Variable scope
  • Variable lifetime
  • Module and public variables
  • Arrays

Functions and UDF’S

  • Functions
  • Using a function on a worksheet

Error Handling

User Forms

  • Controls and Forms
  • Check boxes and spinners
  • Linking to cells
  • User forms as custom dialogs
  • The toolbox
  • Designing forms
  • Showing forms
  • Handling events for a form
  • Closing a form
  • Using control properties
  • Text, list and combo boxes
  • Interaction techniques

Excel & Other Applications

You’ve been there before. you are tearing your hair out trying to finish the presentation due for a monthly performance meeting. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course the changes means you have to start all over with the copy and pastes…

There is always a better way! Excel VBA makes it easy to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!

Learn how to interact with other applications using Excel Macros

  • Excel to PowerPoint
  • Excel to Word
  • Excel to Outlook (Demonstration on trainer laptop only)

Excel VBA Advanced

Target Audience

This course is the last in our series of courses exploiting Visual Basic programming within Excel.  It covers the major subject of interacting with databases, including Access and others, using many practical examples.  It also covers how your work can be used as a distributable and reusable solution, including making it an Excel Add-In.

pdf download

DOWNLOAD

Delegate Pre-Requisites

Aimed at delegates with experience of using Excel VBA preferably having attended the Excel VBA The Next Level course

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Databases and ADO

  • What is a database, and how can it interact with Excel?
  • Microsoft’s database technologies
  • Active Data Objects – ADO
  • Adding references
  • Connecting to a database – using connection strings
  • Returning data using Recordsets
  • Reading column values
  • Adding data
  • Building and executing SQL
  • Taking it further

Creating an Add-In

  • Project properties
  • Protection and security
  • Using macros from shortcut menus
  • Creating an Add-In
  • Saving Add-Ins
  • Using Add-Ins
  • Auto_Open and Auto_Close
  • Distributing Add-Ins

Excel Beyond the Macro Recorder

Target Audience

This course is a great step into using macros where delegates are shown how to record the majority of the macro using keystrokes and then guided into understanding the basics of the VBA coding behind a macro and have a go at basic macro editing with lots of tips and tricks to avoid run-time errors!

pdf download

DOWNLOAD

Delegate Pre-Requisites

Aimed at delegates with a good working knowledge of Excel.  No previous knowledge of Macros or VBA is required.

Course Duration

This is a One Day Course

Course Details

Courses commence at 9:30 a.m. through to 4:30 p.m.

A laptop computer is provided for each delegate attending on this packed programme to try out the tips and techniques demonstrated

All delegates will receive comprehensive Training Courseware to refer to during the course, together with a Course Attendance Certificate

The Content

Accessing and Running an Existing Macro

  • From the Ribbon
  • From a Keyboard shortcut
  • From a Quick Access Toolbar icon
  • From the Developer Tab
  • From a Button

Recording a Macro

  • How to record a basic Macro
  • Macro planning
  • The Macro Recorder

Macro Storage locations

  • This Workbook
  • New Workbook
  • Personal Macro Workbook

Understanding Macro limitations

  • Understanding the limitations of a basic recorded macro
  • How to avoid some basic run-time errors

Define a Trusted Location

  • Understanding Trusted Locations and how to they can be created

Getting more from the Macro Recorder

  • Using keyboard shortcuts to extend macro flexibility
  • Creating a Relative Reference Macro

Take a closer look at the macro

  • Understand basic recorded macro syntax
  • Basic macro editing
  • Basic debug tools

Running Multiple Macros together

  • How to create a set of macros to complete a task
  • How to allow one macro to run another