Date and Time:

9th April 2025

9:30am to 4:30pm

Programme Aim:

This course introduces Microsoft Excel participants to deepen their expertise by exploring some of the more specialised and advanced capabilities of Microsoft Excel. Participants will enhance their skill set, gaining insights that go beyond the basics and empowering them to utilise Excel's full potential applying what they have learnt to their job.

Programme Objectives:

Participants will learn how to create V & H Lookups, utilising Drop-down lists and Named Ranges. How to structure Left Lookups using a combination of the Index and Match functions to extract information from a list. Participants will also learn how to create PivotTables to facilitate the professional organisation, display and charting of data and learn the techniques involved to generate Macros to automate multi-step processes.

Programme Structure:

This course is virtually delivered via Zoom Virtual Classroom. Demo excel files will be provided in advance to facilitate 'hands-on' participation.

Prerequisites:

It is recommended that participants have completed the 'Excel Intermediate – Level 2' course or be proficient in applying the features and functions associated with the Level 2 course content.

Programme Outline:

Use Advanced Functions

o Use VLOOKUP

o Nesting VLOOKUP and TRIM

o Use Data Validation

o Use Named Ranges

o The SUMPRODUCT function

o Use INDEX and MATCH

o Deal with Errors

Create Macros

o About Macros

o Recording Macros

o Running Macros

o Editing Macros in the VBE

o Assigning Macros to Buttons

Tables

o Introduction to Tables

o PivotTable connection

Work with Pivot Tables

o Create PivotTables

o Edit PivotTables

o Add Fields to a PivotTable

o Add Currency Style

o Group PivotTable Data

o Drill into Pivot Data

o Create Pivot Charts

o Refresh the Data

o Change the Layout of a PivotTable

o Create Slicers