Got a question? Call 1800 853 276   |   
Contact usCheckoutContact us
End User Applications Category Banner Image

Microsoft Excel - Advanced

  • Length 1 day
Course overview
View dates &
book now

Why study this course

This course is intended to help proficient users reach new heights with the advanced features of Excel, Microsoft’s powerful and easy-to-use spreadsheet program.

Learn how to create complex formulas, utilise a wide range of functions, and develop dynamic charts and reports. In addition, you'll learn how to automate repetitive tasks using macros, and gain a practical understanding of data analysis tools such as Solver, data linking, Pivot Charts, and controls.

By the end of the course, you'll have acquired the skills and knowledge necessary to work confidently with Excel at an advanced level.

This course builds on what was learned in Microsoft Excel - Beginner and Intermediate. After completing this course, you may be interested in Excel Expert or one of our Excel Specialist courses.

Note: Excel 2016, Excel 2019, Excel 2021, and Excel 365 are all desktop application versions of Microsoft Excel. Excel 2016, Excel 2019, and Excel 2021 are the perpetual, bought-outright, stand-alone versions of the software; Excel 365 is the subscription-based version. Anyone working with any of these versions will be able to successfully complete this course. Throughout this course you will be using the desktop app; the course does not cover use of the web/online app.

Nexacu Public Schedule

With the recent acquisition of Nexacu, we're pleased to now offer you the largest public schedule of end user applications training in Australia and New Zealand. As we move to consolidate our End User offering with Nexacu, as an interim measure, you can now access the schedule of the most closely aligned courses and book, by clicking on the link below. Pricing of some courses may vary when booking directly through Nexacu.

Logo: Nexacu Powered by Lumify

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

What you’ll learn

At the completion of this course you should be able to:

  • Protect data in worksheets and workbooks

  • Use a range of lookup and reference functions

  • Use the Formula Auditing tools to find and fix formula errors

  • Import data into Excel and export data from Excel

  • Understand and create simple PivotTables

  • Construct and operate PivotTables using some of the more advanced techniques

  • Create and edit a PivotChart

  • Create recorded macros in Excel

Microsoft Partner Cloud Logo

Microsoft Apps at Lumify Work

Lumify Work is your best choice for training and certification in any of Microsoft’s leading technologies and services. We’ve been delivering effective training across all Microsoft products for over 30 years, and are proud to be Australia's and New Zealand’s first and largest Microsoft Gold Learning Solutions Partner. Join more than 5,000 students who attend our quality Microsoft courses every year.

Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Quality Instructors and Content

Expert instructors with real world experience and the latest vendor-approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world's leading vendors as preferred training partner.

Ahead of the Technology Curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

Who is the course for?

This course is designed for existing users of Excel who wish to move to an advanced level, with a focus on increasing automation and employing the more advanced tools available.

We can also deliver and customise this training course for larger groups – saving your organisation time, money and resources. For more information, please contact us on 1800 U LEARN (1800 853 276)

Course subjects

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

Lookup Functions

  • Understanding Data Lookup Functions

  • Using VLOOKUP

  • Using VLOOKUP for Exact Matches

  • Using INDEX

  • Using Match

  • Using XLOOKUP

Formula Auditing

  • Understanding Tracing Precedents

  • Understanding Tracing Dependents

  • Tracing Precedents and Dependents

  • Showing Formulas and Cell Arguments

  • Common Error Messages

  • Understanding Error Checking

  • Checking for Errors

  • Tracing Errors

  • Evaluating Formulas

  • Using the Watch Window

  • Working With Circular References

Importing and Exporting

  • Understanding Data Importing

  • Understanding Text File Formats

  • Importing Tab Delimited Text

PivotTables

  • Understanding Pivot Tables

  • Recommended Pivot Tables

  • 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

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

PivotCharts

  • Inserting a PivotChart

  • Defining the PivotChart Structure

  • Changing the PivotChart Type

  • Using the PivotChart Filter

  • Field Buttons

  • Moving PivotCharts to Chart Sheets

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

  • Deleting a Macro

  • Copying a Macro

  • Assigning a Macro to Toolbar

  • Running a Macro from Toolbar

  • Assigning a Macro to Ribbon

  • Assigning a Shortcut to a Macro

Supplementary Content 

  • Dynamic Array Functions

  • Referencing Tables and Pivot Tables

  • Get & Transform (Power Query)

  • Scenarios

  • Solver

  • Data Tables

  • Lookup Functions

Supplementary Content 

  • Mathematical Functions

  • Importing and Exporting

  • Data Consolidation

  • Summarising and Subtotalling

  • Grouping and Outlining

  • Recorder Workshop Controls

  • Information Functions

Prerequisites

This course assumes the user has completed Microsoft Excel - Beginner and Intermediate, or has the equivalent knowledge and skills.

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.

Select and book a course

Can't find a date you like?

Contact sales

Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Quality Instructors and Content

Expert instructors with real world experience and the latest vendor-approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world's leading vendors as preferred training partner.

Ahead of the Technology Curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

Looking for more course options?