Microsoft Excel Advanced Course – Classroom or Online Learning

Harness the Power of Excel

Enrol Now

Download A Course Guide

One day hands on classroom course
Online Excel Advanced Course with 250 tutorials

Study Options

Online Excel Advanced Course

Start Anytime

Over 250 Online Tutorials - Instructor demonstrated
Course manual - Electronic copy included
Unlimited 12 months course access
6 Hours CPD
$79

Enrol Now

Classroom Excel Advanced Course

Hands On Classroom

Instructor led - Hands on classroom workshops
Course manual - Electronic copy included
Refreshments & lunch included
Expert Trainers
$339

Enrol Now

Online Learning - 250 Part Course

Microsoft Excel 2016 Advanced Online Training is a convenient, effective and affordable way to learn. You can complete the Microsoft Excel Intermediate Online Training at your own pace and your own leisure. Whether you’re a beginner, an experienced user, or somewhere in between, you’ll find valuable knowledge that can be accessed again and again at a time and pace that suits you.

Microsoft Excel Intermediate Online Training utilises learning materials that are packed full of high quality content, step-by-step instructions with visuals and plenty of practice exercises offering exceptional value to the participant.

Learning Outcomes Chapters 1 to 9 - 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

Learning Outcomes Chapters 10 to 18 - Click to View

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

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

Classroom Course

1 Day Hands On Classroom Course

The Excel Intermediate training course is designed for users familiar with Excel basics and want to really improve their productivity and get more out of their spreadsheets.

Use detailed functions and complex formulas to enhance your spreadsheet. Use simple tools like Goal Seek to quickly adjust your results in excel.

Duration – 1 day | version: 2010, 2013 & 2016

Classroom Training

Hands on classroom training in our modern facilities in Perth CBD

Learning Outcomes - Click to view

At the completion of this Microsoft Excel Advanced training course in Perth, 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

The Microsoft Excel Advanced training course in Perth utilises learning materials that are packed full of high quality content, step-by-step instructions with visuals and plenty of practice exercises offering exceptional value to the participant.

Course Contents - Click to view

Setting Excel Options

Logical Functions

Lookup Functions

Grouping and Outlining

Summarising and Subtotalling

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

PivotTables

PivotTable Features

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

Validating Data

Protecting Data

Importing and Exporting

Sharing Workbooks

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

PDF Course Outline

Training Manual & Comprehensive Resources Included for MS Excel Course

Each class attendee to our Microsoft Excel Course is given the following;

Microsoft Excel Advanced 2016 Manual (online electronic version)
Handy Tips guide
Handy Tips for Excel 2016 Intermediate (Summarising key tips and trick for using Microsoft Excel 2016)
“Quick Reference” Guide for Excel 2016
Excel 2016 Sample Chapter Files from the course
Excel 2016 Exercise Files
Excel 2016 Exercise File Solutions
Multiple Choice Test for each chapter

Customised Group Training

Customised Group Training for Microsoft Excel 2010, 2013 or 2016

Group Training - Click to view

Pre Course Questionnaire (Suitable for group training sessions if required)
Training Needs Analysis for Microsoft Excel Course
Tests available for download (Suitable for group training sessions if required)
Onsite with up to 12 laptops and projector
Use our computer training rooms venue in Perth CBD at no additional cost
Class sizes 2 to 24 attendees
Includes all resources electronic manual for each attendee or hard copy additional

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

Classroom Dates & Schedule - Perth

Course Duration

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

Duration: 9am-4pm

Hands On Classroom Workshops - Modern Computers Supplied, pens, mints, refreshments & lunch

View Dates & Enrol

Read real reviews from our happy students on google

Download A Course Guide

Enrol Now

Microsoft Excel Advanced Course – Classroom or Online Learning

Harness the Power of Excel

One day hands on classroom courseOnline Excel Advanced Course with 250 tutorials

Study Options

Online Excel Advanced Course

$79

Classroom Excel Advanced Course

$339

Online Learning - 250 Part Course

Classroom Course

1 Day Hands On Classroom Course

Classroom Training

Hands on classroom training in our modern facilities in Perth CBD

Training Manual & Comprehensive Resources Included for MS Excel Course

Customised Group Training

Classroom Dates & Schedule - Perth

Course Duration

Read real reviews from our happy students on google

One day hands on classroom course
Online Excel Advanced Course with 250 tutorials