Microsoft Excel Course: Level 3
Microsoft Excel. Unlock insights and tell the story in your data.
COVID-19 update: arrangement of our courses
We are now delivering courses online and in-person. Please check the delivery format of each class before enrolling.
Please note that course materials (excluding prescribed texts) are shared electronically within 48 hours of course commencement. Printing is not available.
This short course focuses on more advanced features beyond the Microsoft Excel: Level 2 course. Advanced analysis tools including data linking, data consolidation and outlining and summarising are covered, as are PivotTables, Lookup Functions and some key automation features such as macros. Learn how to import and export data, create Data Tables and Scenarios for What If analysis, techniques to validate data, and create form controls such as List and Combo Boxes to make data entry easier.
Aims
This course aims to provide experienced Excel users with proficient skills in developing more complex formulas, list analysis using a variety of tools and creating simple macros as well as features and tips to assist efficiency.
Outcomes
By the end of this course, you should be able to:
- modify Excel options
- import data into Excel and export data from Excel
- 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 a range of lookup and reference functions
- 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
- construct and operate PivotTables using some of the more advanced techniques
- create and edit a PivotChart
- use a variety of data validation techniques
- create and use a range of controls in a worksheet
- create recorded macros in Excel.
Content
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
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
Data Linking
- Understanding Data Linking
- Linking Between Worksheets
- Linking Between Workbooks
- Updating Links Between Workbooks
Grouping and Outlining
- Understanding Grouping and Outlining
- Creating an Automatic Outline
- 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
Lookup Functions
- Understanding Data Lookup Functions
- Using INDEX
- Using Match
- Using INDIRECT
- Using XLookup
Data Consolidation
- Understanding Data Consolidation
- Consolidating With Identical layouts
- Creating a Linked Consolidation
- Consolidating From Different Layouts
- Consolidating Data Using the SUM Function
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
Scenarios
- Understanding Scenarios
- Creating a Default Scenario
- Creating Scenarios
- Using Names in Scenarios
- Displaying Scenarios
- Creating a Scenario Summary Report
- Merging Scenarios
PivotTable Features
- Understanding Slicers
- Creating Slicers
- Inserting a Timeline Filter
- 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
PivotCharts
- Inserting a PivotChart
- Defining the PivotChart Structure
- Changing the PivotChart Type
- Using the PivotChart Filter Field Buttons
- Moving PivotCharts to Chart Sheets
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
Controls
- 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 Selection
- Adding a List Box Control
- Adding a Scroll Bar Control
- Adding a Spin Button Control
- Adding Option Button Controls
- Adding a Group Box Control
- Adding a Check Box Control
- Protecting a Worksheet With Controls
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
Intended audience
Assumes an intermediate level understanding of the software and experience in the construction and modification of workbooks. It is designed for those who are ready to explore more of the advanced analysis and automation tools.
Delivery style
- Presenter-taught training in a computer lab
- Online training via the platform Zoom
Materials
You will be provided with a link to access and download your own personalised eBook prior to class.
Teaching instructions and materials are specifically tailored to Windows PC users.
Additional information
Face-to-face classes
These classes run in a computer lab and you do not need to bring your own device.
Please bring a USB flash drive to class if you would like to make a copy of your work or any relevant class materials. Alternatively, you can save these to a cloud storage space or email them to your personal email address.
Online classes
If you are attending an online class, you will need your own device.
Recommended operating system: Windows or Mac
Recommended software for PC: MS Excel 2013, 2016, 2019, 365
Recommended software for Mac OS: MS Excel 2016, 2019, 365
Recommended browser: Chrome
Features
- $50 repeat class - Conditions apply
- Expert trainer
- Small class size
- CCE Statement of Completion
What others say.
Extremely knowledgeable tutor who created an excellent learning environment, clearly explained what we covered and assisted a few students in the break time too. Excellent take home resources. Thank you!
Really enjoyed the course, content fabulous, plenty of opportunities to practice and understand the procedures involved.
The pace was fast and the tutor was good at making sure everyone was on the same page before moving on. The lesson was punctuated by good humour and the tutor reiterated summaries that helped drill in key components. Fantastic class, highly recommended!
The course was designed perfectly to allow participants to engage fully with the course content and instructions provided by the tutor. The tutor was very helpful in explaining all concepts and directions so all could follow along and maximise the value of the course. I would highly recommend this course for those wishing to advance their Excel knowledge to a proficient and professional standing.
Hugely experienced tutor. Complex concepts explained in an easy to assimilate manner with very good practical exercises. Thank you.
I found the course moved at a great pace for me, especially since I had been to Level 2 Excel the week prior. The sessions are great for areas of focus and the course materials are excellent for personal review.
I really enjoyed the course. It was well structured and the tutor was very knowledgeable with a clear way of explaining complex concepts.
I wish I took this course before I started my job in investment banking! The course gave a great overview on the Excel advanced features which are applicable to the working environment. The workbook is detailed and Excel files are provided to do exercises within our own time. The class is small and the tutor spent time to answer questions with each one of us throughout the day.
Exceeded my expectations! The tutor was an exceptional trainer. I would 100% recommend it. Everything ran smoothly and I learnt so much.
The teacher was very knowledgeable and patient with the class. It was set at a great pace – not too slow where boredom sets in and not too quick that we couldn’t keep up. The classroom and computers were great. Every topic that was taught was new to me and I walked away with an understanding of not just what to do but why it’s done, which is a valuable way to ensure it’s remembered easier. I have highly recommended this course to my peers.
The course was intensive but well planned. As a result you never felt rushed in the learning process. The lecturer was engaging and an excellent teacher. I would recommend the course to anyone with a strong requirement for Excel in their day to day life.
Excellent course – felt I learned so much, and entered at the right level too (attended the Intermediate course too – the two courses go nicely together). Satisfied with the content, structure of the course, and the pedagogic approach. The tutor was outstanding – very helpful, gave tons of examples which we were able to relate to and plenty of repetitions. Have already recommended this to a lot of my colleagues!
The course was perfectly presented with a very competent presenter — and just as important, the course was incredible value for money. I have recommended it to my employer to consider this well presented and targeted training.
I really liked the format of the notes and exercises,very nice for self-study and suited to work through in small chunks.
Very enjoyable course and loved the training materials and the chance to re-do the exercises in my own time to increase understanding.
Well, I’m new to Excel, all I know I have pick up from work colleagues and I thoroughly enjoyed the course. Once I get into it I will benefit greatly with my work load. Pivot Table and Macro, loved learning about them. I’m only sorry it was for 1 day – there is so much more to learn...
I found the tutor’s knowledge and approach to teaching to be outstanding. The class strengths and weaknesses were taken into account as the tutor tailored the day to suit our needs.
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Thu 14 Jan 2021 | 9:30am - 4:30pm (UTC+11:00) | Room 301 - Face-to-face (CCE Building, Newtown) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Fri 05 Feb 2021 | 9:30am - 4:30pm (UTC+11:00) | Room 301 - Face-to-face (CCE Building, Newtown) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Mon 22 Feb 2021 | 9:30am - 4:30pm (UTC+11:00) | Room 301 - Face-to-face (CCE Building, Newtown) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Wed 03 Mar 2021 | 9:30am - 4:30pm (UTC+11:00) | Online via Zoom - Online via Zoom |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Mon 15 Mar 2021 | 9:30am - 4:30pm (UTC+11:00) | Room 301 - Face-to-face (CCE Building, Newtown) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Tue 23 Mar 2021 | 9:30am - 4:30pm (UTC+11:00) | Online via Zoom - Online via Zoom |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Thu 01 Apr 2021 | 9:30am - 4:30pm (UTC+11:00) | Face-to-face (Venue TBA) - Face-to-face (Venue TBA) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Fri 16 Apr 2021 | 9:30am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Thu 13 May 2021 | 9:30am - 4:30pm (UTC+10:00) | Face-to-face (Venue TBA) - Face-to-face (Venue TBA) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Sat 15 May 2021 | 9:30am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Tue 25 May 2021 | 9:30am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Wed 02 Jun 2021 | 9:30am - 4:30pm (UTC+10:00) | Face-to-face (Venue TBA) - Face-to-face (Venue TBA) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Tue 15 Jun 2021 | 9:30am - 4:30pm (UTC+10:00) | Face-to-face (Venue TBA) - Face-to-face (Venue TBA) |
<p>{block name:“Course Tagline - Microsoft Excel”}</p><p>{block name:“Course Message - Microsoft and Adobe”}</p><p>{block name:“Block - COVID 19 updates”}</p><p>This short course focuses on more
...When | Time | Where | Session Notes |
---|---|---|---|
Tue 29 Jun 2021 | 9:30am - 4:30pm (UTC+10:00) | Online via Zoom - Online via Zoom |
If there isn't a class to suit you, please join the waiting list.
Related content. Further your learning.
- Related Courses
- Financial Decision-Making Course for Managers
- Financial Modelling Best Practice Course
- Financial Modelling Course Online: The Complete Guide
- Financial Modelling Course: Integrated Financial Statements
- Microsoft Access Course: Level 2
- Microsoft Excel Course: Level 1
- Microsoft Excel Course: Level 2
- Microsoft Excel VBA Course: A Masterclass
- Microsoft Excel VBA Course: An Introduction
- Microsoft PowerPoint Course: Level 2
- Microsoft Word Course: Level 3
- Power BI Course: Advanced
- Power BI Course: Intermediate
- Power BI Course: Introduction
What others say.
- I really liked the format of the notes and exercises,very nice for self-study and suited to work through in small chunks.
- Absolutely loved it! The tutor was incredibly knowledgeable and kept me engaged the whole time.
- I found the tutor's knowledge and approach to teaching to be outstanding. The class strengths and weaknesses were taken into account as the tutor tailored the day to suit our needs.
Subjects
- Arts and humanities
- Business and management
-
English
-
Horticulture and gardening
- HSC preparation (years 10-12)
-
Human resources
- Information technology
- Language and culture
- Marketing
- Music and film
- Project management