Advanced Excel Part 3 – Modern Excel & Microsoft 365

Advanced Excel Part 3 is a practical one-day course for experienced Excel users who want to take advantage of the powerful modern features available in Microsoft Excel and Microsoft 365.

The course focuses on newer Excel functionality including XLOOKUP, XMATCH, Dynamic Arrays, Spill formulas, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and LET, together with useful Microsoft 365 features such as linked Stocks and Geography data types.

Delegates learn how these newer Excel tools can simplify formulas, automate analysis and make spreadsheets more flexible and efficient.

Advanced Excel Part 3 can be attended after Advanced Excel Part 1 and Advanced Excel Part 2 or as a standalone course by experienced Excel users who already have the required advanced Excel skills.

View Advanced Excel Part 1 – Core Advanced Excel

Advanced Excel Part 3 Training South Africa

Microsoft Excel has changed significantly with the introduction of Microsoft 365. Functions such as XLOOKUP and Dynamic Arrays can replace older, more complicated approaches and allow spreadsheets to update automatically as information changes.

Advanced Excel Part 3 is designed to help experienced Excel users understand and apply these modern features in practical business situations.

The emphasis is not simply on learning new functions. Delegates learn how to combine modern Excel functionality to solve workplace problems, analyse information and build more efficient spreadsheets.

Download the course contents Advanced Excel Part 3 – Modern Excel & Microsoft 365

Modern Lookup Functions in Advanced Excel Part 3

XLOOKUP

XLOOKUP is one of the most useful modern Excel functions and provides a more flexible alternative to traditional VLOOKUP formulas.

Delegates learn how to:

• Create XLOOKUP formulas
• Understand XLOOKUP versus VLOOKUP
• Perform exact and approximate matches
• Look up information to the left or right
• Handle values that cannot be found
• Apply XLOOKUP to practical business spreadsheets

XMATCH

Delegates are introduced to XMATCH and how it can be used to identify the relative position of information within an Excel range or array.

The course includes practical exercises so delegates can understand when modern lookup functions provide a simpler solution than older Excel techniques.

Dynamic Arrays & Spill Formulas

Dynamic Arrays are one of the most important changes introduced into modern Excel.

Traditional formulas generally return a single result into a single cell. A Dynamic Array formula can return multiple results and automatically spill those results into neighbouring cells.

In Advanced Excel Part 3, delegates learn how Spill formulas work and how they can be used to create worksheets that update automatically when the underlying information changes.

Understanding Spill

• Understanding Dynamic Arrays
• Understanding Spill formulas
• Understanding Spill ranges
• Using the Spill reference operator #
• Understanding the #SPILL! error
• Identifying common causes of #SPILL! errors
• Correcting blocked Spill ranges

FILTER

FILTER allows users to return only the records that meet specified conditions.

Unlike manually filtering a worksheet, the resulting list can update dynamically when the source information changes.

SORT and SORTBY

Delegates learn how SORT and SORTBY can dynamically organise information without manually rearranging the original dataset.

UNIQUE

UNIQUE can automatically extract a list of distinct values from a dataset. This is useful for creating dynamic lists of customers, departments, products, regions and other business information.

SEQUENCE

SEQUENCE allows users to generate sequential numbers automatically and can be combined with other modern Excel functions.

Combining Dynamic Array Functions

The real power of Dynamic Arrays becomes apparent when functions are combined.

Delegates work through practical examples using functions such as FILTER, SORT, UNIQUE and XLOOKUP together to create flexible, automatically updating results.

Modern Excel Formula Functions

Advanced Excel Part 3 also introduces useful modern functions that can simplify formulas and make complex spreadsheets easier to understand and maintain.

LET

LET allows users to assign names to calculations or values within a formula. This can make longer formulas easier to read and can avoid repeating the same calculation several times.

IFS

IFS provides a cleaner way of evaluating multiple conditions and can reduce the need for long nested IF formulas.

SWITCH

SWITCH allows a value to be compared against several possible results and can provide a simpler alternative to certain nested formulas.

MAXIFS and MINIFS

MAXIFS and MINIFS allow users to identify maximum and minimum values based on specified criteria.

TEXTJOIN and CONCAT

Delegates learn modern methods of combining text and information from different cells and ranges.

Stocks & Linked Data Types

Not every feature in Advanced Excel Part 3 involves complicated formulas. Microsoft 365 also includes linked data types that make it easy to bring structured information into Excel.

Excel Stocks Data Type

The Stocks Data Type allows recognised company names and ticker symbols to be converted into linked data within Excel.

Depending on the information available through Excel, delegates can retrieve fields such as:

• Stock price
• Company information
• Exchange
• Market capitalisation
• Other available linked fields

Delegates build a simple share information worksheet and learn how linked Stocks data can be referenced from Excel formulas.

Geography & Linked Data

The Geography Data Type applies the same linked-data concept to countries, cities and other recognised geographic locations.

Delegates learn how to convert geographic information into linked Excel data and extract available fields directly into a worksheet.

This provides a simple practical demonstration of how Microsoft 365 can turn ordinary worksheet entries into structured linked information.

Useful Microsoft 365 Excel Features

Advanced Excel Part 3 includes additional modern Excel features that can help users work more efficiently.

Insert Data from Picture

Where supported, Excel can convert information from an image into worksheet data, reducing the need to retype information manually.

Analyse Data

Analyse Data can help users investigate information and identify possible patterns, summaries and questions relating to a dataset.

Recommended Charts

Delegates review how Excel can recommend appropriate chart types based on the information selected.

These easier Microsoft 365 features provide useful productivity gains alongside the more advanced formula and Dynamic Array sections of the course.

Practical Modern Excel Applications

The purpose of Advanced Excel Part 3 is to apply modern Excel functionality to practical workplace problems.

Delegates learn techniques that can help them:

• Create automatically updating lists
• Extract information dynamically
• Remove duplicates dynamically
• Sort results automatically
• Filter results automatically
• Replace complicated older formulas with modern functions
• Combine XLOOKUP with Dynamic Arrays
• Create more flexible reporting worksheets
• Reduce repetitive spreadsheet work
• Build spreadsheets that respond automatically when source data changes
• Download the course contents Advanced Excel Part 3 – Modern Excel & Microsoft 365

Who Should Attend Advanced Excel Part 3?

Advanced Excel Part 3 is designed for experienced Excel users who are already comfortable working with formulas, business spreadsheets and data analysis.

The course is particularly suitable for employees working in:

• Finance and accounting
• Management reporting
• Business analysis
• Sales and marketing
• Human resources
• Operations
• Administration
• Project environments
• Management and executive support

Delegates who still need to develop their core Advanced Excel skills should first consider Advanced Excel Part 1.

Progress Through the Advanced Excel Programme

College Africa Group’s Advanced Excel programme provides three progressive levels of training:

• Advanced Excel Part 1 – Core Advanced Excel: advanced formulas, referencing, data validation, VLOOKUP, PivotTables and PivotCharts.
• Advanced Excel Part 2 – Advanced Analysis & Productivity: Advanced Filters, Goal Seek, Scenario Manager, macros, conditional formatting, 3-D formulas and Sparklines.
• Advanced Excel Part 3 – Modern Excel & Microsoft 365: XLOOKUP, Dynamic Arrays, Spill formulas, modern functions and linked data types.

This structure allows organisations to select an individual course or progressively develop employees through the different Advanced Excel levels.

Advanced Excel Part 3 and AI

Modern Excel skills are becoming increasingly important as organisations introduce AI tools such as Microsoft Copilot and ChatGPT.

AI can help generate formulas and suggest approaches to spreadsheet problems, but users still need to understand the underlying Excel functionality to determine whether the results are accurate and appropriate.

Advanced Excel Part 3 gives experienced users a stronger understanding of the modern Excel functionality that they may increasingly use alongside AI.

Advanced Excel Part 3 Course Duration

Advanced Excel Part 3 is a one-day instructor-led course, normally running from 09h00 to approximately 15h30.

Corporate training can be delivered onsite at the client’s premises or virtually using Microsoft Teams.

For organisations requiring training across different Excel skill levels, College Africa Group also provides Excel Training South Africa, including Basic, Intermediate and Advanced Excel programmes.

Advanced Excel Part 3 Table of Contents

The complete Advanced Excel Part 3 – Modern Excel & Microsoft 365 Table of Contents covers the modern Excel functions, Dynamic Arrays, linked data types and practical exercises included in the one-day course.

Download the Advanced Excel Part 3 Table of Contents.

Advanced Excel Part 3 – Modern Excel & Microsoft 365

Microsoft Excel Resources

For additional information about modern Excel functionality, delegates can also use Microsoft’s official Excel resources:

• Microsoft Excel Help & Learning
• Microsoft – XLOOKUP Function
• Microsoft – FILTER Function
• Microsoft – UNIQUE Function

Why Choose College Africa Group for Advanced Excel Part 3?

College Africa Group provides practical corporate Excel training focused on the skills employees need in the workplace.

Advanced Excel Part 3 combines instructor demonstrations with practical exercises so delegates can understand and apply modern Excel functionality rather than simply seeing a list of new features.

Training is available for corporate groups onsite and virtually. Delegates receive course material and an electronic attendance certificate on completion.

Frequently Asked Questions – Advanced Excel Part 3

What is Advanced Excel Part 3?

Advanced Excel Part 3 is a one-day modern Excel course covering XLOOKUP, Dynamic Arrays, Spill formulas, FILTER, SORT, UNIQUE, modern formula functions and Microsoft 365 linked data types.

Does Advanced Excel Part 3 cover XLOOKUP?

Yes. XLOOKUP is one of the major sections of the course. Delegates learn how it works, how it differs from VLOOKUP and how it can be applied to practical business spreadsheets.

What are Spill formulas in Excel?

Spill formulas are modern Excel formulas that can return multiple results into neighbouring cells. Part 3 covers Dynamic Arrays, Spill ranges, the Spill reference operator and common #SPILL! errors.

Does the course include FILTER and UNIQUE?

Yes. FILTER, SORT, SORTBY, UNIQUE and SEQUENCE are included as part of the Dynamic Arrays section.

Does Advanced Excel Part 3 include Excel Stocks?

Yes. Delegates are introduced to the Excel Stocks Data Type and learn how available linked company and market information can be brought into an Excel worksheet.

Do I have to complete Parts 1 and 2 first?

Not necessarily. Experienced Excel users with the required skills can attend Part 3 directly. Delegates who still need to develop their advanced formula, data analysis or PivotTable skills should consider Parts 1 and 2 first.

Is Advanced Excel Part 3 available online?

Yes. Instructor-led virtual Advanced Excel Part 3 training is available using Microsoft Teams. Corporate onsite training is also available.

How long is Advanced Excel Part 3?

Advanced Excel Part 3 is a one-day instructor-led course, normally running from 09h00 to approximately 15h30.

Advanced Excel Part 3 Enquiries

Advanced Excel Part 3 – Modern Excel & Microsoft 365 is available for corporate groups across South Africa through onsite and instructor-led virtual training.

Contact College Africa Group for course dates, group pricing and training options.

Telephone: 083 778 4903
Email: sales@collegeafricagroup.com

Enquire About Advanced Excel Part 3

“`html

“`