Advanced Modern Excel 365

Advanced Modern Excel 365 is a practical one-day course for experienced Excel users who want to use the newer capabilities available in Microsoft 365 Excel more effectively.

Microsoft Excel has changed considerably. Dynamic Arrays and newer Microsoft 365 functions can simplify formulas, create automatically updating results and reduce some of the repetitive spreadsheet work associated with older Excel techniques.

But having newer functions available does not automatically mean employees know when to use them, how to combine them or when another approach such as Power Query or automation would be more appropriate.

This course focuses on practical workplace application of Dynamic Arrays, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, LET and advanced XLOOKUP applications, together with short introductions to Power Query, automation and AI-assisted Excel.

The objective is not to use modern functions simply because they are new. It is to help experienced Excel users choose simpler and more effective ways of solving spreadsheet problems.

Download the Advanced Modern Excel 365 Course Outline

Often, yes.

Experienced Excel users may have developed strong spreadsheet skills over many years but still rely on techniques that were necessary in older versions of Excel.

Typical examples include:

• copying formulas down large ranges;
• manually filtering information to create separate reports;
• rebuilding lists whenever source data changes;
• using unnecessarily long formulas;
• maintaining multiple copies of similar calculations;
• manually extracting unique values;
• repeatedly sorting and restructuring data;
• using formulas for data-preparation tasks that may be better suited to Power Query; and
• repeating spreadsheet processes that could potentially be automated.

Advanced Modern Excel 365 introduces experienced users to newer ways of approaching these problems.

What Makes Modern Excel 365 Different?

One of the biggest changes in Microsoft 365 Excel is that a formula no longer necessarily has to return only one result into one cell.

Dynamic Array formulas can return multiple results and allow those results to spill automatically into neighbouring cells.

This creates new ways to filter, sort, extract and manipulate business information.

Instead of asking:

“How do I reproduce the old spreadsheet process?”

the better question may be:

“Is there now a simpler way to produce the result?”

That shift in thinking is central to this course.

How Can Dynamic Arrays Reduce Repetitive Excel Work?

Dynamic Arrays allow formulas to return changing sets of results automatically.

Delegates learn how spill behaviour works and how Dynamic Arrays can be applied to practical business data.

The course includes:

• understanding Dynamic Arrays;
• understanding spill ranges;
• using the spill reference operator #;
• recognising the #SPILL! error;
• identifying blocked spill ranges; and
• correcting common spill problems.

Business outcome: employees can create more flexible worksheets that respond automatically as the underlying information changes.

How Can FILTER Create Automatically Updating Business Reports?

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

For example, an employee may need:

• all transactions for a particular customer;
• all overdue accounts;
• all employees in a particular department;
• all sales from a particular region;
• all records above or below a defined value; or
• a changing list based on a business condition.

Instead of repeatedly filtering the original dataset and copying the results elsewhere, FILTER can create a separate result that updates dynamically.

Business outcome: employees can reduce repetitive manual extraction and create more responsive reporting worksheets.

How Can SORT, SORTBY and UNIQUE Improve Business Data Analysis?

Modern Excel can dynamically organise and extract information without repeatedly rearranging the original dataset.

SORT and SORTBY allow results to be returned in a required order, while UNIQUE extracts distinct values automatically.

These functions can be useful when working with:

• customer lists;
• product lists;
• departments;
• regions;
• employees;
• transaction categories; and
• other repeating business information.

SEQUENCE is also introduced as a method of generating dynamic sequential values that can be combined with other modern functions.

Business outcome: employees can create automatically updating lists and reports without repeatedly rebuilding the source information.

How Can Modern Excel Functions Make Formulas Easier to Maintain?

A complicated formula is not automatically a good formula.

Long formulas can become difficult to understand, troubleshoot and maintain, particularly when calculations are repeated several times within the same expression.

The course introduces selected modern Microsoft 365 functions that can help simplify this work.

LET

LET allows users to assign names to values or calculations inside a formula.

This can reduce repeated calculations and make longer formulas easier to understand.

IFS and SWITCH

IFS and SWITCH provide alternative approaches to particular types of conditional logic and can reduce the need for unnecessarily complicated nested formulas.

MAXIFS and MINIFS

These functions allow users to identify maximum or minimum values according to specified criteria.

TEXTJOIN and CONCAT

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

Business outcome: formulas can become clearer, more maintainable and easier for other employees to understand.

How Does Advanced XLOOKUP Fit into Modern Excel?

XLOOKUP is introduced earlier in Advanced Excel — Business Modelling and is extended here through more advanced applications.

Delegates investigate how XLOOKUP can work alongside newer Microsoft 365 functionality.

Applications include:

• more flexible lookup requirements;
• combining XLOOKUP with Dynamic Arrays;
• returning related business information dynamically;
• handling missing results; and
• building more maintainable lookup-driven reports.

Business outcome: employees can move beyond simply replacing VLOOKUP and use modern lookup techniques as part of more flexible spreadsheet solutions.

When Is Power Query Better Than Another Excel Formula?

Not every spreadsheet problem should be solved with another formula.

Employees who repeatedly import, clean, restructure or combine information can spend significant time performing the same preparation steps every reporting period.

Power Query provides a different approach.

This course includes an introduction 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 information when the source changes; and
• recognising when Power Query may be more appropriate than another formula.

The purpose is not to turn this one-day programme into a full Power Query course.

It is to help experienced Excel users recognise when repeated manual data preparation should potentially be handled differently.

Delegates requiring deeper capability can continue to College Africa Group’s specialist Power Query — Data Preparation & Reporting Automation training.

Which Excel Tasks Could Be Automated?

Experienced Excel users often become very efficient at repeating the same process.

But becoming faster at a repetitive process does not necessarily mean the process itself is efficient.

Delegates therefore learn to recognise the difference between:

Formula → Power Query → Automation

The course includes:

• identifying repetitive spreadsheet activities;
• understanding the difference between formulas, Power Query and automation;
• recognising tasks that may benefit from macros;
• a brief demonstration of recording and running an Excel macro; and
• understanding when automation may reduce repetitive manual work.

Business outcome: employees become better able to identify spreadsheet processes that should potentially be simplified or automated.

How Can AI and Copilot Assist an Experienced Excel User?

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

AI can assist with:

• formula development;
• explaining unfamiliar formulas;
• investigating structured business data;
• identifying possible trends;
• highlighting variances and exceptions; and
• suggesting alternative spreadsheet approaches.

However, AI-generated Excel output still has to be checked.

Employees must understand the source information, spreadsheet logic and business requirement sufficiently well to determine whether the suggested result is correct.

The principle remains:

Source Data → Process → Required Output → AI → Validate Against the Source

AI can assist the Excel user. It does not remove the need for Excel knowledge or human judgement.

How Do the Modern Excel Techniques Work Together?

The greatest value comes when employees stop treating each function as an isolated command.

A modern Excel solution may involve:

Source Data → FILTER → SORT → UNIQUE → XLOOKUP → LET → Report

Another requirement may be better approached through:

Source Data → Power Query → Clean & Transform → Refresh → Analyse

And a repetitive process may ultimately require automation rather than more formulas.

Delegates therefore work through an integrated Modern Excel exercise in which they must consider which approach is most appropriate for the business problem.

What Practical Business Problems Can Advanced Modern Excel 365 Help Solve?

Modern Excel techniques can be applied across finance, reporting, HR, sales, administration, operations, projects and business analysis.

Typical applications include:

• automatically updating reporting lists;
• extracting records that meet changing criteria;
• creating dynamic customer, product or employee lists;
• sorting report outputs automatically;
• reducing unnecessarily complicated formulas;
• building more flexible lookup-driven reports;
• preparing imported information for analysis;
• reducing repetitive data-cleaning work;
• identifying processes suitable for automation; and
• using AI to assist with Excel while validating the resulting output.

What Will Delegates Learn?

By the end of Advanced Modern Excel 365, delegates will be able to:

• understand Dynamic Arrays and spill behaviour;
• use FILTER to return matching business records;
• use SORT and SORTBY to dynamically organise results;
• use UNIQUE to extract distinct values;
• use SEQUENCE to generate dynamic series;
• use LET to simplify repeated calculations;
• apply selected modern Microsoft 365 formula functions;
• extend XLOOKUP into more advanced applications;
• combine XLOOKUP with Dynamic Arrays;
• recognise when Power Query may be more appropriate than another formula;
• identify spreadsheet activities that may benefit from automation;
• use AI/Copilot as an assistant while retaining human verification; and
• select an appropriate modern Excel approach for the business requirement.

Download the Advanced Modern Excel 365 Course Outline

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 larger datasets.

It is particularly relevant to employees working in:

• finance and accounting;
• management reporting;
• business analysis;
• sales and marketing;
• human resources;
• operations;
• administration;
• project environments; and
• management and executive support.

Employees do not have to complete every earlier College Africa Group Excel programme before attending.

However, delegates should already have the practical Excel foundation required to understand formulas, business data and spreadsheet analysis.

Not Sure Whether an Employee Is Ready for Advanced Modern Excel 365?

Employees can use Excel regularly without necessarily having the underlying skills required for a modern Advanced Excel programme.

College Africa Group’s practical Excel Skills Assessment can help indicate whether an employee has the foundation required for more advanced Excel training.

The assessment is intended as a practical screening tool rather than a formal Beginner, Intermediate or Advanced classification.

View the Excel Skills Assessment

Where Does Advanced Modern Excel 365 Fit in the Advanced Excel Pathway?

College Africa Group’s Advanced Excel pathway separates different types of advanced Excel work so organisations can select training according to the work employees actually perform.

The programmes are not a compulsory four-level sequence. Experienced employees can enter at the programme appropriate to their existing capability and workplace requirements.

Advanced Excel — Formulas, Data & Reporting

Build stronger formulas, work reliably with business data, use VLOOKUP and conditional calculations, and turn larger datasets into useful reports using PivotTables and PivotCharts.

Explore Advanced Excel — Formulas, Data & Reporting

Advanced Excel — Business Modelling

Test assumptions, compare scenarios and explore possible business outcomes using XLOOKUP, Goal Seek, Scenario Manager, Data Tables and practical modelling techniques.

Explore Advanced Excel — Business Modelling

Advanced Modern Excel 365

This course. Use Dynamic Arrays, modern Microsoft 365 functions, advanced XLOOKUP applications and recognise when Power Query, automation or AI may provide a more efficient approach.

Advanced Excel for Analysts

For experienced Excel users whose roles involve substantial data preparation and analysis, including consolidating, cleaning, reshaping, summarising and calculating business information.

For an overview of the complete pathway, visit Advanced Excel Training.

How Long Is Advanced Modern Excel 365?

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

The programme combines instructor demonstration, explanation and practical Excel exercises.

Microsoft 365 Excel is required for the modern functions covered in the programme.

Training can be delivered onsite at the client’s premises or through live virtual instructor-led training using Microsoft Teams.

Advanced Modern Excel 365 Training Across South Africa

College Africa Group provides instructor-led Advanced Excel training across South Africa and Southern Africa for corporate teams.

Onsite training can be delivered at the client’s premises, while live virtual instructor-led training is available for organisations with employees working across different branches, provinces or countries.

We support organisations requiring Excel training in major business centres including Johannesburg, Pretoria, Cape Town, Durban and KwaZulu-Natal, as well as other locations across South Africa and Southern Africa.

Training locations:

South Africa & Southern Africa
|
Johannesburg & Gauteng
|
Cape Town & Western Cape
|
Durban & KwaZulu-Natal

For organisations developing employees across different Excel skill areas, see the broader Microsoft Excel Training pathway.

Why Choose College Africa Group for Advanced Modern Excel 365?

College Africa Group provides practical corporate Excel training focused on helping employees apply spreadsheet skills to realistic workplace requirements.

Modern Excel training should not simply become a demonstration of every new Microsoft 365 function.

For Advanced Modern Excel 365, the emphasis is on helping delegates understand:

What is the business problem? → Is a modern formula appropriate? → Is there a simpler solution? → Should Power Query or automation be considered? → Does the result make sense?

This helps experienced Excel users modernise the way they work without adding unnecessary complexity.

College Africa Group was established in 2003 and provides corporate training for organisations across South Africa and Southern Africa.

Frequently Asked Questions About Advanced Modern Excel 365

What is Advanced Modern Excel 365?

Advanced Modern Excel 365 is a one-day course for experienced Excel users covering Dynamic Arrays, spill formulas, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, selected modern Microsoft 365 functions, advanced XLOOKUP applications and introductions to Power Query, automation and AI-assisted Excel.

Who should attend Advanced Modern Excel 365?

The course is intended for experienced Excel users who already work confidently with formulas, business spreadsheets and data analysis and want to use newer Microsoft 365 Excel capabilities more effectively.

Does the course cover Dynamic Arrays?

Yes. Delegates learn how Dynamic Arrays and spill ranges work and apply functions including FILTER, SORT, SORTBY, UNIQUE and SEQUENCE to practical business information.

Does the course cover XLOOKUP?

Yes. XLOOKUP is extended through more advanced applications and combined with Dynamic Array functions and modern reporting techniques. XLOOKUP is introduced earlier in Advanced Excel — Business Modelling.

Does the course include Power Query?

Yes. The programme includes an introduction to importing, cleaning, transforming and refreshing data with Power Query. It is not intended to replace 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 recording and running a macro. 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, explanations and analysis while retaining human judgement and verification.

Do employees have to complete Parts 1 and 2 first?

Not necessarily. Experienced Excel users with the required foundation skills can attend Advanced Modern Excel 365 directly. Employees who need to strengthen their formulas, reporting or business-modelling capability may benefit from an earlier programme in the Advanced Excel pathway.

Is Advanced Modern Excel 365 available virtually?

Yes. The course can be delivered through live instructor-led Microsoft Teams training as well as onsite corporate training.

Where is Advanced Modern Excel 365 training available?

College Africa Group provides corporate Excel training across South Africa, including Johannesburg, Pretoria, Cape Town, Durban and KwaZulu-Natal, with onsite and virtual training also available for organisations across Southern Africa.

Advanced Modern Excel 365 Enquiries

If your employees already use Excel confidently but need to work more effectively with newer Microsoft 365 functions and modern spreadsheet techniques, talk to College Africa Group about Advanced Modern Excel 365.

We can discuss their existing Excel capability, the work they perform and whether Advanced Modern Excel 365 or another programme in the Advanced Excel pathway is the appropriate fit.

Better Processes. Stronger Skills. Smarter AI.

Send an Enquiry
|
Call 083 778 4903
|
WhatsApp College Africa Group