Microsoft Excel Advanced Course

Classroom or Online Learning

Harness the power of Excel

Unlock the more advanced features of Microsoft Excel with our hands-on classroom course or online Excel Advanced training course.

Designed for experienced Excel users, the course covers advanced topics such as Scenarios, PivotTables and data protection.

Learning Outcomes - Click to view

At the completion of this Microsoft Excel Advanced training course participants will be able to:

modify Excel options
use logical functions to test whether a statement is true or false
use a range of lookup and reference functions
use data linking to create more efficient workbooks
group cells and use outlines to manipulate the worksheet
create summaries in your spreadsheets using subtotals
use the Data Consolidation feature to combine data from several workbooks into one
create, use and modify data tables
create and work with scenarios and the Scenario Manager
understand and create simple PivotTables
construct and operate PivotTables using some of the more advanced techniques
create and edit a PivotChart
use advanced filters to analyse data in a list
use a variety of data validation techniques
protect data in worksheets and workbooks
import data into Excel and export data from Excel
share workbooks with other users
create recorded macros in Excel

Course Contents - Click to view

Setting Excel Options

Understanding Excel Options
Personalising Excel
Setting the Default Font
Setting Formula Options
Understanding Save Options
Setting Save Options
Setting the Default File Location
Setting Advanced Options
Challenge Exercise
Challenge Exercise Sample

Logical Functions

Understanding Logical Functions
Using IF With Text
Using IF With Numbers
Nesting IF Functions
Using IFERROR
Using TRUE and FALSE
Using AND
Using OR
Using NOT
Challenge Exercise
Challenge Exercise Sample

Lookup Functions

Understanding Data Lookup Functions
Using CHOOSE
Using VLOOKUP
Using VLOOKUP for Exact Matches
Using INDEX
Using Match
Data Linking
Understanding Data Linking
Linking Between Worksheets
Linking Between Workbooks
Updating Links Between Workbooks
Challenge Exercise
Challenge Exercise Sample

Grouping and Outlining

Understanding Grouping and Outlining
Creating an Automatic Outline
Working With an Outline
Creating a Manual Group
Grouping by Columns
Challenge Exercise
Challenge Exercise Sample

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
Challenge Exercise
Challenge Exercise Sample

Data Consolidation

Understanding Data Consolidation
Consolidating With Identical Layouts
Creating a Linked Consolidation
Consolidating From Different Layouts
Consolidating Data Using the SUM Function
Challenge Exercise
Challenge Exercise Sample

Data Tables

Understanding Data Tables and What-If Models
Using a Simple What-If Model
Creating a One-Variable Table
Using One-Variable Data Tables
Creating a Two-Variable Data Table
Challenge Exercise
Challenge Exercise Sample

Scenarios

Understanding Scenarios
Creating a Default Scenario
Creating Scenarios
Using Names in Scenarios
Displaying Scenarios
Creating a Scenario Summary Report
Merging Scenarios
Challenge Exercise
Challenge Exercise Sample

PivotTables

Understanding PivotTables
Recommended PivotTables
Creating Your Own PivotTable
Defining the PivotTable Structure
Filtering a PivotTable
Clearing a Report Filter
Switching PivotTable Fields
Formatting a PivotTable
Understanding Slicers
Creating Slicers
Inserting a Timeline Filter
Challenge Exercise
Challenge Exercise Sample

PivotTable 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 PivotTable Reports
Creating Running Totals
Creating Calculated Fields
Providing Custom Names
Creating Calculated Items
PivotTable Options
Sorting in a PivotTable
Challenge Exercise
Challenge Exercise Sample

PivotCharts

Inserting a PivotChart
Defining the PivotChart Structure
Changing the PivotChart Type
Using the PivotChart Filter Field Buttons
Moving PivotCharts to Chart Sheets
Challenge Exercise
Challenge Exercise Sample

Advanced Filters

Understanding Advanced Filtering
Using an Advanced Filter
Extracting Records With Advanced Filter
Using Formulas in Criteria
Understanding Database Functions
Using Database Functions
Using DSUM
Using the DMIN Function
Using the DMAX Function
Using the DCOUNT Function
Challenge Exercise
Challenge Exercise Sample

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
Challenge Exercise
Challenge Exercise Sample

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 Protected Workbook
Removing a Password From a Workbook
Challenge Exercise
Challenge Exercise Sample

Importing and Exporting

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
Modifying an Inserted Picture
Challenge Exercise
Challenge Exercise Sample

Sharing Workbooks

Sharing Workbooks via the Network
Sharing Workbooks via OneDrive
Saving to OneDrive
Sharing Workbooks
Opening Shared Workbooks
Enabling Tracked Changes
Accepting or Rejecting Changes
Disabling Tracked Changes
Adding Worksheet Comments
Navigating Worksheet Comments
Editing Worksheet Comments
Deleting Comments
Challenge Exercise
Challenge Exercise Sample

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 References
Viewing a Macro
Editing a Macro
Assigning a Macro to the Toolbar
Running a Macro From the Toolbar
Assigning a Macro to the Ribbon
Assigning a Keyboard Shortcut to a Macro
Deleting a Macro
Copying a Macro
Challenge Exercise
Challenge Exercise Sample

View Full Course Outline

Training manual and comprehensive resources included with Microsoft Excel course

The course includes online learning materials that can be accessed again and again. Packed full of high-quality content, step-by-step instructions with plenty of visuals and practice exercises, the course materials offer exceptional value to the participant, including:

- Microsoft Excel Advanced 2016 course manual
- Handy Tips guide (key tips and tricks for using Excel 2016)
- Quick Reference Guide
- Exercise Files to practise with

Study Options

Online Learning

Start any time and study at your own pace

$99

Affordable and convenient

Log in at any time to access the course material, so you can study the course at a time that fits in with your lifestyle or other commitments.

Instructor-demonstrated video tutorials.

Electronic manual included, plus 12 months unlimited access to course material.

Follow along with the sample exercises provided.

6 CPD hours per course

Classroom Workshop (Perth)

Hands-on classroom training in the Perth CBD

$339

Hands-on classroom environment led by an expert trainer

Full-day workshop with refreshments and lunch included

All resources and computers provided

Talk to the course trainer, ask questions and receive handy tips to aid with your use of Excel.

12 months access to electronic materials after completion of the class

6 CPD hours per course

Get the Course Guide

Excel Advanced - Online Course

Instructor-demonstrated video tutorials

Pause, rewind and re-watch as many times as you need to assist in understanding the course material. Here's some sample videos:

250-part course

Microsoft Excel 2016 Advanced Online Training is a convenient, effective and affordable way to learn. You can complete the online training at your own pace and your own leisure. You’ll find valuable knowledge that can be accessed again and again at a time and pace that suits you.

Learning Outcomes Chapters 1 to 10 - Click to View

Chapter 1

Chapter 1-1 Personalising Excel
Chapter 1-2 Setting The Default Font
Chapter 1-3 Setting Formula Options
Chapter 1-4 Setting Save Options
Chapter 1-5 Setting The Default File Location
Chapter 1-6 Setting Advanced Options

Chapter 2

Chapter 2-1 Using IF With Text
Chapter 2-2 Using IF With Numbers
Chapter 2-3 Nesting IF Functions
Chapter 2-4 IF ERROR
Chapter 2-5 Using TRUE And FALSE
Chapter 2-6 Using AND
Chapter 2-7 Using OR
Chapter 2-8 Using NOT

Chapter 3

Chapter 3-1 The CHOOSE Function
Chapter 3-2 The LOOKUP Function
Chapter 3-3 The VLOOKUP For Exact Matches
Chapter 3-4 Using Index Page
Chapter 3-5 Using MATCH

Chapter 4

Chapter 4-1 Linking Between Worksheets
Chapter 4-2 Linking Between Workbooks
Chapter 4-3 Updating Links Between Workbooks

Chapter 5

Chapter 5-1 Creating An Automatic Outline
Chapter 5-2 Working With An Outline
Chapter 5-3 Creating A Manual Group
Chapter 5-4 Grouping By Columns

Chapter 6

Chapter 6-1 Creating Subtotals
Chapter 6-2 Using A Subtotalled Worksheet
Chapter 6-3 Creating Nested Subtotals
Chapter 6-4 Copying Subtotals
Chapter 6-5 Using Subtotals With AutoFilter
Chapter 6-6 Creating Relative Names For Subtotals
Chapter 6-7 Using Relative Names For Subtotals

Chapter 7

Chapter 7-1 Consolidating With Identical Layouts
Chapter 7-2 Creating A Linked Consolidation
Chapter 7-3 Consolidating From Different Layouts
Chapter 7-4 Consolidating Data Using The SUM Function

Chapter 8

Chapter 8-1 Using A Simple What-If Model
Chapter 8-2 Creating A One-Variable Table
Chapter 8-3 Using One-Variable Data Table
Chapter 8-4 Creating A Two-Variable Data Table

Chapter 9

Chapter 9-1 Creating A Default Scenario
Chapter 9-2 Creating Scenarios
Chapter 9-3 Using Names In Scenarios
Chapter 9-4 Displaying Scenarios
Chapter 9-5 Creating A Scenario Summary Report
Chapter 9-6 Merging Scenarios

Chapter 10

Chapter 10-1 Recommended Pivot Tables
Chapter 10-2 Creating Your Own PivotTable
Chapter 10-3 Defining The PivotTable Structure
Chapter 10-4 Filtering A PivotTable
Chapter 10-5 Clearing A Report Filter
Chapter 10-6 Switching PivotTable Fields
Chapter 10-7 Formatting A PivotTable
Chapter 10-8 Creating Slicers
Chapter 10-9 Inserting A Timeline Filter

Learning Outcomes Chapters 11 to 18 - Click to View

Chapter 11

Chapter 11-1 Using Compound Fields
Chapter 11-2 Counting In A PivotTable
Chapter 11-3 Formatting PivotTable Values
Chapter 11-4 Working With PivotTable Grand Totals
Chapter 11-5 Working With PivotTable Subtotals
Chapter 11-6 Finding The Percentage Of Total
Chapter 11-7 Finding The Difference From
Chapter 11-8 Grouping In PivotTable Reports
Chapter 11-9 Creating Running Totals
Chapter 11-10 Creating Calculated Fields
Chapter 11-11 Providing Custom Names
Chapter 11-12 Creating Calculated Items
Chapter 11-13 PivotTable Options
Chapter 11-14 Sorting In A PivotTable

Chapter 12

Chapter 12-1 Inserting A Pivot Chart
Chapter 12-2 Defining The PivotChart Structure
Chapter 12-3 Changing The PivotChart Type
Chapter 12-4 Using The PivotChart Filter Field Buttons
Chapter 12-5 Moving PivotCharts To Chart Sheets

Chapter 13

Chapter 13-1 Using An Advanced Filter
Chapter 13-2 Extracting Records With Advanced Filters
Chapter 13-3 Using Formulas In Criteria
Chapter 13-4 Using Database Functions
Chapter 13-5 Using DSUM
Chapter 13-6 Using The DMIN Function
Chapter 13-7 Using The DMAX Function
Chapter 13-8 Using The DCOUNT Function

Chapter 14

Chapter 14-1 Creating A Number Range Validation
Chapter 14-2 Testing A Validation
Chapter 14-3 Creating An Input Message
Chapter 14-4 Creating An Error Message
Chapter 14-5 Creating A Drop Down List
Chapter 14-6 Using Formulas As Validation Criteria
Chapter 14-7 Circling Invalid Data
Chapter 14-8 Removing Invalid Circles
Chapter 14-9 Copying Validation Settings

Chapter 15

Chapter 15-1 Providing Total Access To Cells
Chapter 15-2 Protecting A Worksheet
Chapter 15-3 Working With A Protected Worksheet
Chapter 15-4 Disabling Worksheet Protection
Chapter 15-5 Providing Restricted Access To Cells
Chapter 15-6 Password Protecting A Workbook
Chapter 15-7 Opening A Password Protected Workbook
Chapter 15-8 Removing A Password From A Workbook

Chapter 16

Chapter 16-1 Importing From An Earlier Version
Chapter 16-2 Importing Tab Delimited Text
Chapter 16-3 Importing Comma Delimted Text
Chapter 16-4 Importing Space Delimted Text
Chapter 16-5 Importing Access Data
Chapter 16-6 Working With Connected Data
Chapter 16-7 Unlinking Connections
Chapter 16-8 Exporting To Microsoft Word
Chapter 16-9 Exporting Data As Text
Chapter 16-10 Inserting A Picture
Chapter 16-11 Modifying An Inserted Picture

Chapter 17

Chapter 17-1 Saving To OneDrive
Chapter 17-2 Sharing Workbooks
Chapter 17-3 Enabling Tracked Changes
Chapter 17-4 Accepting Or Rejecting Changes
Chapter 17-5 Disabling Tracked Changes
Chapter 17-6 Adding Worksheet Comments
Chapter 17-7 Navigating Worksheet Comments
Chapter 17-8 Editing Worksheet Comments
Chapter 17-9 Deleting Comments

Chapter 18

Chapter 18-1 Setting Macro Security
Chapter 18-2 Saving A Document As Macro Enabled
Chapter 18-3 Recording A Simple Macro
Chapter 18-4 Running A Recorded Macro
Chapter 18-5 Relative Cell References
Chapter 18-6 Running A Macro With Relative References
Chapter 18-7 Viewing A Macro
Chapter 18-8 Editing A Macro
Chapter 18-9 Assigning A Macro To The Toolbar
Chapter 18-10 Running A Macro From The Toolbar
Chapter 18-11 Assigning A Macro To The Ribbon
Chapter 18-12 Assigning A Keyboard Shortcut To A Macro
Chapter 18-13 Deleting A Macro
Chapter 18-14 Copying A Macro

Instant Access to Online Learning -
Sign Up & Start Now

$99

Enrol & Start Now

Paying by direct debit or cheque? - Click here. Access granted once funds cleared for this method.

Complete Online Course Bundle
All 10 Courses $295 $495 - RRP $1090

55% Saving off RRP of $1090
Over 50 hours on-demand tutorials
Detailed training manuals e-book
Access anytime
Certificates of completion

All our Microsoft Online Learning - Excel, Word, PowerPoint, Project, Access & Outlook

Hands-on classroom training in the Perth CBD

The Advanced Microsoft Excel course in Perth is designed to teach the experienced spreadsheet user how to use Excel's advanced features.

Learn in a hands-on environment where you can interact in real time with the course instructor. Ask questions, receive handy tips and see real examples to learn how to use the more complex features correctly and effectively.

Classroom students are given access to reference the electronic course materials for 12 months after attending the class.

1-day intensive workshop

Location: Level 1, 524 Hay St, Perth WA 6000

Duration: 9am-4pm (1 day)

$339

View Dates & Enrol

Customised Group Training - Perth

Custom Group Training is available in Perth for Microsoft Excel 2010, 2013 or 2016.

Customised 1-day group session for 5 or more attendees
Complete a pre-course questionnaire and needs analysis for us to ascertain your group's specific training requirements
We can come to you, onsite with up to 12 laptops and projector
Alternatively use our computer training room venue in the Perth CBD
Group booking minimum 5 attendees up to a maximum of 12 students
Electronic manual and resources included for each attendee (hard copies additional)

Please email us for comprehensive details and a proposal on how we can make your next corporate training event a success.

Get the Course Guide

Microsoft Excel Advanced Course

Classroom or Online Learning

Harness the power of Excel

Training manual and comprehensive resources included with Microsoft Excel course

Study Options

Online Learning

Start any time and study at your own pace

$99

Classroom Workshop (Perth)

Hands-on classroom training in the Perth CBD

$339

Excel Advanced - Online Course

Instructor-demonstrated video tutorials

250-part course

Instant Access to Online Learning -
Sign Up & Start Now

$99

Complete Online Course Bundle
All 10 Courses $295 $495 - RRP $1090

Excel Advanced - Classroom Workshop

Hands-on classroom training in the Perth CBD

1-day intensive workshop

$339

Customised Group Training - Perth

Home

Courses

Students

Contact

Get a detailed course guide

Get a detailed Microsoft
course guide

Get a detailed Microsoft
course guide

Tax Agent Pathway
Course Guide & Study Tips

Microsoft Excel Advanced Course

Classroom or Online Learning

Harness the power of Excel

Training manual and comprehensive resources included with Microsoft Excel course

Study Options

Online Learning

Start any time and study at your own pace

$99

Classroom Workshop (Perth)

Hands-on classroom training in the Perth CBD

$339

Excel Advanced - Online Course

Instructor-demonstrated video tutorials

250-part course

Instant Access to Online Learning - Sign Up & Start Now

$99

Complete Online Course Bundle All 10 Courses $295 $495 - RRP $1090

Excel Advanced - Classroom Workshop

Hands-on classroom training in the Perth CBD

1-day intensive workshop

$339

Customised Group Training - Perth

Home

Courses

Students

Contact

Instant Access to Online Learning -
Sign Up & Start Now

Complete Online Course Bundle
All 10 Courses $295 $495 - RRP $1090