MS Excel Level 3 Advanced

Learning Outcomes:    When you have completed this course, you will be able to create more protect worksheet data, perform advanced data operations using summarising, data consolidations, filters and Pivot Tables, macros and much more.

Course Pre-Requisites:        This level assumes the learner is already familiar with Excel features and operations and is now ready to explore more of the advanced analysis and automation tools in Excel.  We recommend that users complete MS Excel level 2 prior to level 3.

Duration: 3 hours

Lookup Functions

The CHOOSE Function


Using Counting Functions

The ROUND Function

Rounding Up and Down

Manipulative Functions

The MOD & TODAY Function

NOW,DATE & PMT Function


Goal Seeking

Goal Seeking Components

Using Goal Seek


Protecting Data

Understanding Data Protection

Providing Total Access to Cells

Protecting a Worksheet

Working With a Protected Worksheet

Disabling Worksheet Protection

Providing Restricted Access to Cells

Password Protecting a Workbook

Opening a Password Protecting a Workbook

Removing a Password from a Workbook


Importing and Exporting Data

Understanding Data Importing

Importing From an Earlier Version

Understanding Text File Formats

Importing Tab Delimited Text

Importing Comma Delimited Text

Importing Space Delimited Text

Importing Access Data

Working With Connected Data

Unlinking Connections

Exporting to Microsoft Word

Exporting Data as Text

Inserting a Picture

Modify an Inserted Picture


Data Linking

Understanding Data Linking

Working With an Outline

Creating A Manual Group

Grouping by Columns


Summarising and Subtotalling

Creating Subtotals

Using a Subtotalled Worksheet

Creating Nested Subtotals

Copying Subtotals

Using Subtotals With AutoFilter

Creating Relative Names for Subtotals

Using Relative Names for Subtotals


Data Consolidation

Understanding Data Consolidation

Consolidating With Identical Layouts

Creating a Linked Consolidations

Consolidating From Different Layouts

Consolidating Data Using SUM Function


Data Tables

Understanding Data Tables And

What If Models

Using A Simple  What If Model

Creating A One-Variable Data Table

Using  A One-Variable Data Table

Creating A Two-Variable Data Table



Understanding Scenarios

Creating a Default Scenario

Creating Scenarios

Using Names in Scenarios

Displaying Scenarios

Creating a Scenario Summary Report

Merging Scenarios



Pivot Tables

Understanding PivotTables

Recommended PivotTables

Creating Your Own PivotTable

Defining the PivotTable Structure

Filtering A Pivot Table

Clearing A Report Filter

Switching Pivot Table Fields

Formatting A Pivot Table

Understanding Slicers

Creating Slicers

Inserting A Timeline Filter

Challenge Exercise


Pivot Table Features

Using Compound Fields

Counting in a PivotTable

Formatting PivotTable Values

Working With PivotTable Grand Totals

Working With PivotTable Subtotals

Finding the Percentage of Total

Finding the Difference from Grouping in

Pivot Table Reports

Creating Running Totals

Creating Calculated Fields

Providing Custom Names

Creating Calculated Items

Pivot Table options

Sorting in A Pivot Table


Pivot Charts

Inserting a PivotChart

Defining the PivotChart Structure

Changing the PivotChart Type

Using the PivotChart Filter Field Buttons

Moving Pivot Charts to Chart Sheets


Advanced Filters

Understanding Advanced Filtering

Using Advanced Filtering

Extracting records with Advanced Filtering

Using Formulas In Criteria

Understanding  Data Base Functions

Using Data Base Functions

Using DSUM

Using the DMIN Function

Using the DMAX Function

Using the DCOUNT Function


Validating Data

Understanding Data Validation

Creating A Number Range Validation

Testing A Validation

Creating An Input Message

Creating An Error Message

Creating A Drop Down List


Using Formulas as Validation Criteria

Circling Invalid Data

Removing Invalid Circles

Copying Validation Settings



Understanding Types of Controls

Understanding How Controls Work

Preparing a Worksheet for Controls

Adding a Combo Box Control

Changing Control Properties

Using the Cell Link to Display the


Adding a List Box Control

Adding a Scroll Bar Control

Adding a Spin Button Control

Adding Option Button Control

Adding a Group Box Control

Adding a Check Box Control

Protecting A Worksheet With Controls


Sharing Workbooks

Sharing Workbooks Via the Network

Sharing Workbooks Via One Drive

Saving to OneDrive

Sharing Workbooks 

Opening Shared Workbooks

Enabling Tracked Changes

Disabling Tracked Changes

Adding Worksheet Comments

Navigating Worksheet Comments

Editing Worksheet Comments

Deleting Comments


Recorded Macros

Understanding Excel Macros

Setting Macro Security

Saving a Document as Macro Enabled

Recording a Simple Macro

Running a Recorded Macro

Relative Cell References

Running a Macro with Relative Cell References

Viewing a Macro

Editing a Macro

Assigning a Macro to the Tool Bar

Running a Macro from the Tool Bar

Assigning a Macro to the Ribbon

Assigning Key Board Short Cut to A  Macro

Deleting a Macro

Copying a Macro