End User Applications Category Banner Image

Microsoft Excel - Advanced

Length 1 day
Price NZD 435 exc GST

Course overview

View dates &
book now

Course locations >>

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.

The course covers structured references, focusing on cell-based versus table-based references and table elements. You will learn to create and manage named ranges for more dynamic spreadsheets. The session also includes dynamic array functions like UNIQUE, SORT, and XLOOKUP. PivotTables will be emphasised, with guidance on creating, customising, and extracting insights from data sets. Finally, the training introduces advanced functions, including Date, Information, Math, Statistical, and Text functions, to handle various data analysis tasks proficiently.

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.

This course introduces functions that require an Microsoft Office 365 version of excel. These tools have no equivalent in prior Excel Versions.

Request Course Information

What you’ll learn

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

Learn structured references, including cell/table-based references, table elements, rows, ranges, totals, and the union operator.
Master creating, editing, applying, managing, and using named ranges in formulas.
Understand and apply dynamic array functions such as UNIQUE, SORT, FILTER, CHOOSECOLS, INDEX, MATCH, XMATCH, and XLOOKUP.
Develop skills in creating, customising, filtering, calculating, and visualising data with PivotTables and PivotCharts, including using slicers and the Timeline Filter.
Utilise advanced Excel functions, including Date, Information, Math, Statistical, and Text functions.

Microsoft Apps at Lumify Work

Lumify Work has been delivering effective training across all Microsoft products for over 30 years. We are proud to be both Australia's and New Zealand’s first Microsoft Gold Learning Solutions Partner and the winner of the Microsoft MCT Superstars Award for FY24, which formally recognises us as having the highest quality Microsoft Certified Trainers (MCTs) in ANZ. Join more than 5,000 students who attend our quality Microsoft courses every year.

View all Microsoft Apps courses

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

Structured References

Cell-based vs table-based references
Table elements
Structured reference for a row
Structured reference for a Range
Structured reference for Table Totals
Union operator (,)

Named Ranges

Creating Named ranges
Creating Named ranges using the Name Box
Creating Named ranges using the Create from Selection
Applying names to Tables
Creating Ranges for Data Validation
Editing named ranges – Single Cell
Editing named ranges - Range
Finding where named ranges are used
Deleting named ranges
Using names in a formula
Display a list of range names

Dynamic Array Functions

Function Arguments Dialogue Box
UNIQUE Function
SORT Function
FILTER Function
CHOOSECOLS Function

LOOKUP: INDEX MATCH

INDEX Function
Using XMATCH
SEARCH Mode
MATCH Function [LEGACY FUNCTION]
Using MATCH and INDEX together [LEGACY FUNCTION]
Nesting the MATCH and INDEX Functions

LOOKUP: Choose Switch XLOOKUP

CHOOSE vs SWITCH
XLOOKUP

PivotTables

Creating Pivot Tables
Extracting data from the PivotTable
Filtering the PivotTable
Changing the calculation in the PivotTable
Creating PivotCharts
Adding Slicers
The Timeline Filter
Show Values As
Grouping Data
Report Layout in Pivot Tables
Totals in a Pivot Table
Calculations in a PivotTable
Report Filter Pages
Refresh the PivotTable

Advanced Functions

Date Functions
Information Functions
Maths and Statistical Functions
TEXT Functions (OPTIONAL)

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.