Advanced Modern Excel

Advanced Modern Excel is a practical one-day course for experienced Excel users who want to use the latest Excel 365 functions and features more effectively.

The course focuses on Dynamic Arrays, Spill formulas, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, LET and advanced XLOOKUP applications, together with an introduction to Power Query, automation and AI-assisted Excel.

Delegates learn how modern Excel functionality can simplify formulas, reduce repetitive work, improve analysis and create more flexible spreadsheets that update automatically as information changes.

This course can be attended as part of College Africa Group’s progressive Advanced Excel pathway or as a standalone course by experienced Excel users who already have the required foundation skills.

Download the course contents: Advanced Modern Excel

Advanced Modern Excel 365 Training South Africa

Microsoft Excel has changed significantly with the introduction of Microsoft 365. Newer functions and Dynamic Arrays allow users to build formulas that are more flexible, easier to maintain and capable of returning multiple results automatically.

Advanced Modern Excel 365 & Dynamic Formulas helps experienced Excel users understand and apply these newer features in practical workplace situations.

The emphasis is not simply on learning a list of functions. Delegates learn how to choose the appropriate Excel technique, combine functions where useful and recognise when Power Query, automation or AI may provide a more efficient approach.

Not Sure Which Excel level your staff should start with?

Use College Africa Group’s Excel Skills Assessment to help determine whether an employee has the Excel foundations required for more advanced training.

View the Excel Skills Assessment

Dynamic Arrays & Spill Formulas

Dynamic Arrays are one of the most important developments in modern Excel.

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

Delegates learn how Dynamic Arrays work and how they can be used to create more flexible worksheets that update automatically as 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 blocked Spill ranges
Correcting common Spill problems

FILTER

FILTER allows Excel users to return only the records that meet defined criteria.

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

SORT and SORTBY

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

UNIQUE

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

SEQUENCE

SEQUENCE allows users to generate sequential values automatically and can be combined with other Dynamic Array functions.

Combining Dynamic Array Functions

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

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

Advanced Modern Excel Formula Functions

The course also introduces selected modern Microsoft 365 functions that can make formulas clearer, shorter and easier to maintain.

LET

LET allows users to assign names to values or calculations inside a formula. This can make longer formulas easier to understand and reduce repeated calculations.

IFS

IFS provides a cleaner method 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 outcomes 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.

Advanced XLOOKUP Applications

XLOOKUP is introduced earlier in the Advanced Excel pathway and is extended here through more advanced applications.

Delegates learn how XLOOKUP can be combined with modern Excel functions to create more flexible lookup-driven spreadsheets and reporting solutions.

More flexible XLOOKUP applications
Combining XLOOKUP with Dynamic Arrays
Returning related business information dynamically
Handling missing results
Building more maintainable lookup-driven reports

Enquire About Advanced Modern Excel 365

Introduction to Power Query

Not every spreadsheet problem should be solved with another formula.

Power Query provides a different approach to importing, cleaning and transforming data. This course includes a practical introduction so delegates can recognise situations where Power Query may be more efficient than repeated manual data preparation.

Delegates are introduced to:

Importing data into Power Query
Simple data cleaning and transformation
Removing unnecessary information
Restructuring data for analysis
Loading transformed information back into Excel
Refreshing data when the source changes
Recognising when Power Query is more appropriate than another formula

Delegates who regularly import, clean or combine data can continue to College Africa Group’s specialist Power Query – Data Preparation & Reporting Automation training.

Recognising Opportunities for Excel Automation

Experienced Excel users often spend significant time repeating the same spreadsheet processes.

This section helps delegates recognise where formulas, Power Query or automation may provide a better solution.

Identifying repetitive spreadsheet activities
Understanding the difference between formulas, Power Query and automation
Recognising tasks that may benefit from macros
Brief demonstration of recording and running an Excel macro
Understanding when automation may reduce repetitive manual work

AI & Copilot in Modern Excel

Modern Excel is increasingly being used alongside AI tools such as Microsoft Copilot and ChatGPT.

AI can assist with formulas, explanations, analysis and identifying possible trends or exceptions. However, Excel users must still understand the underlying spreadsheet logic and verify whether AI-generated outputs are accurate and appropriate.

The course provides a short practical introduction to:

Using AI to assist with formula development
Asking AI to explain Excel formulas
Using AI to investigate structured business data
Identifying trends, variances and exceptions
Checking and verifying AI-generated outputs
Maintaining human judgement and responsibility

Practical Advanced Modern Excel Applications

The purpose of Advanced Modern Excel 365 & Dynamic Formulas 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
Reduce complicated older formulas
Combine XLOOKUP with Dynamic Arrays
Create more flexible reporting worksheets
Reduce repetitive spreadsheet work
Recognise when Power Query may be the better solution
Identify opportunities for automation
Use AI as an assistant while validating the output

Who Should Attend Advanced Modern Excel 365?

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

It 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 strengthen their Excel foundations should first consider Advanced Excel – Formulas, Data & Reporting.

Progress Through the Advanced Excel Programme

College Africa Group’s Advanced Excel programme provides three progressive courses. Organisations can select an individual course or develop employees through the complete pathway.

Advanced Excel – Formulas, Data & Reporting

Build reliable formulas, improve spreadsheet data quality, work with VLOOKUP and conditional calculations, prepare business data and analyse larger datasets using PivotTables and PivotCharts.

View Advanced Excel – Formulas, Data & Reporting

Advanced Excel – What-If Analysis, XLOOKUP & Business Modelling

Use Goal Seek, Scenario Manager, Data Tables, XLOOKUP, multi-criteria calculations and business modelling techniques to test assumptions and support better decisions.

View Advanced Excel – What-If Analysis, XLOOKUP & Business Modelling

AdvancedModern Excel 365 & Dynamic Formulas

Use Dynamic Arrays, modern Excel functions, advanced XLOOKUP applications and recognise where Power Query, automation and AI may provide a more efficient solution.

Download the course contents

Modern Excel Skills and AI

AI is changing how employees interact with Microsoft Excel. Tools such as Microsoft Copilot and ChatGPT can help users generate formulas, explain spreadsheet logic and explore business information.

However, AI produces suggestions rather than business judgement.

Experienced Excel users still need to understand how formulas, data and spreadsheet models work so they can identify incorrect assumptions, recognise errors and validate the final result.

Modern Excel skills therefore remain important even as organisations introduce AI into finance, reporting, analysis and operational workflows.

Advanced Modern Excel Course Duration & Delivery

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

Advanced Modern Excel can be delivered onsite at the client’s premises or virtually using Microsoft Teams.

For organisations developing employees across different Excel skill levels, Advanced Modern Excel forms part of College Africa Group’s broader Excel Training South Africa offering, including Basic, Intermediate and Advanced Excel programmes.

Download the Advanced Modern Excel Course Contents

The complete course outline covers the Dynamic Array functions, modern formulas, advanced XLOOKUP applications, Power Query introduction, automation and AI topics included in the one-day course.

Download Advanced Excel – Modern Excel 365 & Dynamic Formulas

Advanced Modern Excel focuses on practical workplace application. By completing Advanced Modern Excel, experienced users can work more effectively with newer Excel functions, dynamic formulas and modern data tools while understanding when automation or AI may provide additional value.

Microsoft Excel Resources

Delegates can also use Microsoft’s official Excel resources for further information about modern Excel functionality:

Why Choose College Africa Group for Modern Advanced Excel Training?

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

Modern Excel 365 & Dynamic Formulas combines instructor demonstrations with practical exercises so delegates can understand and apply modern advanced 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 Certificate of Attendance on completion.

Frequently Asked Questions – Advanced Modern Excel 365

What is Advanced Excel – Modern Excel 365 & Dynamic Formulas?

It is a one-day advanced Excel course covering Dynamic Arrays, Spill formulas, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, modern formula functions, advanced XLOOKUP applications and introductions to Power Query, automation and AI-assisted Excel.

Does the course cover XLOOKUP?

Yes. XLOOKUP is extended through more advanced applications and is combined with Dynamic Array functions and modern reporting techniques.

What are Spill formulas in Excel?

Spill formulas are modern Excel formulas that can return multiple results into neighbouring cells. The course 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 the course include Power Query?

Yes. The course includes an introduction to Power Query, including importing, cleaning, transforming and refreshing data. Delegates requiring deeper Power Query capability can continue to College Africa Group’s specialist Power Query training.

Does the course include Excel automation?

The course includes an introduction to recognising automation opportunities and a brief demonstration of Excel macros. It is not a full VBA programming course.

Does the course include AI or Microsoft Copilot?

Yes. Delegates receive a short introduction to using AI and Microsoft Copilot alongside Excel for formula assistance, analysis and identifying trends or exceptions, while retaining human judgement and verification.

Do I have to complete the other Advanced Excel courses first?

Not necessarily. Experienced Excel users with the required foundation skills can attend this course directly. Delegates who still need to strengthen their formulas, data analysis or business modelling skills should consider the earlier Advanced Excel courses first.

Is Advanced Excel Modern Excel 365 available online?

Yes. Instructor-led virtual training is available using Microsoft Teams. Corporate onsite training is also available.

How long is the course?

The course is a one-day instructor-led programme, normally running from 09h00 to approximately 15h30.

Advanced Excel Modern Excel 365 Enquiries

Advanced Modern Excel 365 & Dynamic Formulas 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