Excel Advanced Course

 

​

Duration

One Day

 

Course Description

This advanced Excel course is perfect for Excel users who want to further develop their Excel skills. The course includes plenty of hands-on exercises to help you retain and start applying what you learn straight away in the workplace. You will learn advanced Excel skills like how to use advanced functions, PivotTables, Goal Seek & Scenarios, and basic automation with Macros.

​

Course Pre-requisites

Be familiar Excel or with with the topics covered in the Basic and Intermediate courses.

​

​

Course Objectives

Consolidate Data

• Combine data from different worksheets and workbooks

 

Outline a Worksheet

• Auto Outline a worksheet

• Group data

 

Range Names

• Creating Range Names

• Navigating Named Ranges

• Using Range Names in formulas

• Managing names

 

Use Data Validation

• Restrict cell entries

• Create custom input and error messages

• Copy Validation setting

• If allowed, see invalid entries

 

Protect

• Lock the whole worksheet

• Protect part of a worksheet

• Protect a workbook

 

What IF Analysis Tools

• Create Scenarios

• Use Goal Seek

 

Sparklines

• Create Sparklines

• Format Sparklines

• Add and format makers

 

Functions

• Statistical functions to include MaxIfs, MiniIfs

• Working with text functions

• Date and time functions

• Database functions

​

Use the Auditing Tools

• Overview of Auditing tools

• Tracer, Dependent and Precedent arrows

• Removing tracer arrows

• Tracing cells causing errors

• Understanding error messages

  • #div/0; #na; #name; #ref!, #value

• Overcoming error messages

 

Macros

• Record a Macro

• Use Relative References

• Assign a Macro to a button

​

​