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 Lumify Group's 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.

Nexacu powered by Lumify - master logo

Request Course Information


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.


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.


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

Personalise your schedule with Lumify USchedule

Interested in a course that we have not yet scheduled? Get in touch, and ask for your preferred date and time. We can work together to make it happen.



Offers

Continue your learning experience online with Lumify Plus
Lumify Plus (formerly DDLS Plus) is your online learning pathway to extend knowledge beyond courses. Get resources to help you practice what you learned and prepare for future courses, exams and certifications.