master new excel functions
in office 365

Unlock The Power Of The New Excel Functions

The way we use Excel has changed.

We now have many new Excel Functions like FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE & More!

This change impacts everyone who uses Microsoft 365, Office 2021, or Excel for the web – Beginner to Advanced Excel users.

With these new functions you get to dramatically simplify the work you do in Excel.

By learning to maximize their potential, you can significantly reduce spreadsheet development time, potentially by up to 90%!

Moreover, these skills allow you to create dynamic reports so you don’t need to refresh your results.

There are multiple ways you can apply the new Excel Formulas to your files.

In this Excel 365 Advanced Formulas course I’ll show you how to maximize the NEW Potential in Excel!

When Microsoft first rolled out these functions, they called them “Dynamic Arrays” functions. But term confused many people, because it made it sound like they were reserved for Excel wizards – not for average Excel users.

Since then, Microsoft has toned down the terminology. You might still hear “Dynamic Arrays” used in videos and tutorials, but really these are just some new functions to help you get your work done faster and easier in Excel.

In fact, they are easier to learn than the IF or VLOOKUP functions.

Watch the Video for a Quick Overview of the Course

I highly recommend this well-organized course for anyone whose work/projects involve data analysis. The knowledge I’ve gained on the new dynamic array functions will raise the level of my analysis while making my work easier at the same time. The new functions are well explained with business examples, and the course notes will be a great reference!

Jennifer Farry, AVP of Compensation

Enroll in This Course & You’ll Learn

  • Create Excel Dashboard reports in MINUTES instead of HOURS
  • Use Excel’s new FILTER Function to find multiple match results and solve Complex Lookup problems
  • Create Pivot Table style reports, without using Pivot Tables – no need to press “refresh” this way
  • Quickly create dynamic loan amortization reports in Excel or dynamic calendars
  • Master the new UNIQUE, SORT & SORTBY Excel Functions and combine them with other formulas
  • Use the brand new Excel RANDARRAY Function to randomly sort lists
  • Use the new XLOOKUP function to simplify your lookup tasks (includes 5 practical examples with XLOOKUP)
  • Use VLOOKUP, IF and SUMIFS together with Excel Dynamic Arrays formulas
  • Compatibility & how older files behave in the new Excel and New Excel files in the old Excel
  • Do Excel Dynamic Arrays make our spreadsheets faster or slower?
  • Use dynamic array referencing as chart ranges in Excel
  • Solve difficult Excel problems – such as split text and numbers from alphanumeric values & much more…
  • Most importantly – how to think outside the box to solve any Excel challenges you come across
I give you a big 10 for the explanation of this course, I learned a lot from it.

🆕 Certified Achievement Badge

Earn Your Digital Badge Powered by Credly

After completing the course, you’ll receive a digital badge. It shows potential employers that you have mastered the new Excel Dynamic Array Formulas in Office 365, gaining proficiency in handling large datasets and automating tasks.

This credential showcases you as valuable asset in roles requiring advanced Excel reporting and data analysis capabilities.

Our Digital Badges can easily be shared. You can add them to your LinkedIn profile, include them in your resume, or spread the word across your social networks – all it takes is a simple click.

Stand out to employers by clearly displaying your skills with a digital badge from XelPlus.

🎁 Join Now To Get Your Exclusive Bonus

You’ll find that the course alone is A+

But your experience will be made even better with our comprehensive eBook.

This 240-page eBook goes into detail covering all key concepts you’ll learn in the Excel 365 Advanced Functions course. This approach significantly simplifies the review and retention process of your learning. Consequently, you’ll gain a crystal-clear understanding of how to utilize these functions effectively whenever they’re required, ensuring a seamless application of your new skills. You’ll appreciate the clear, easy-to-follow language and step-by-step explanations that make even the most complex topics easy to understand.

Detailed course manual for Office 365 New Excel Functions Course

You’ll receive your eBook will be available 30 days after signing up to the course.

The way you break down the components of a complex formula, showing various examples of how they work, before building up to the complete solution is a great method of teaching.

Very well organized and implemented course. It was easy to follow along yet challenging enough to learn.

Stephanie Perkins

How is the Course Structured?

The Excel 365 Advanced Functions course is designed to have an even balance between talking head explanations and screen-casts.

Follow along with organized workbooks to practice as you learn.

Example Downloadable File

Along the way I will challenge you to apply what you learnt to solve a practical problem.

Challenge example in Excel 365 Advanced Functions Course
Example Challenge

In this comprehensive course, you’ll receive over 30 pages of detailed handouts. These materials are expertly designed to summarize the most crucial points, ensuring you have quick access to key information at your fingertips.

Course notes for Excel 365 Advanced Formulas Course
Course Notes for Excel 365 Advanced Functions Course

Your Curriculum

You can view the detailed curriculum below. Click on “See a full outline” button below to expand the list of lectures.

See anything that jumps out? Which lesson do you think you’d begin with?

Excel New functions course cover

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365.

You’ll create professional-grade reports in a fraction of the time it used to take you.

This course includes:

Over 7 hours of video content

Downloadable Workbooks and Templates

Course Notes summarizing the most important concepts

Exercises & Quizzes to test your skills

LIFETIME Access so you never feel rushed

Comment sections to ask questions and get answers from our friendly teaching assistants

English Closed Captions

Bonus – exclusive eBook with 200+ pages summarizing key concepts covered in the course

Section 1: Before You Dive In

Course Structure & Outline
Availability of Excel Dynamic Arrays
Summary of Updates to Excel’s Calculation Engine

Section 2: Dynamic Arrays & Spill (hash) Behavior (What you need to know)

Introduction & Glossary of Modern Excel Terms
Formulas Spill & Other Excel Dynamic Array Characteristics
Excel FILTER Function (The New Excel Power Multi-Lookup)
SORT & SORTBY Functions in Excel
UNIQUE Function (Get a Unique List of Values in Excel)
Practical Combination of UNIQUE, SORT & FILTER in Excel
SEQUENCE Function in Excel
RANDARRAY Function in Excel
@ Prefix for Compatibility (in Excel Formulas)
Challenge: Create an Excel Summary Report FAST
Answer: Create an Excel Summary Report FAST
Quiz: Test Your Excel Dynamic Array Knowledge
Conclusion: Dynamic Array Rules Recap

Section 3: How Legacy Formulas are Impacted by Excel’s NEW Calculation Engine

Introduction – DA impact on Excel Formulas
Backward & Forward Compatibility with Excel Dynamic Arrays
Absolute & Mixed Cell Referencing – No Longer Needed in Excel?
SUMIFS & COUNTIFS Improved! (More than one criteria in the same column)
VLOOKUP with Excel Dynamic Arrays
IF Function & Excel Dynamic Arrays
Referencing Spilled Data Ranges in Other Excel Sheets
TRANSPOSE & FREQUENCY Functions Made Easier with Excel DA
Do Excel Dynamic Arrays Make Spreadsheets Slow?
Challenge: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Answer: Data Analysis with Legacy Excel Formulas & Dynamic Arrays
Conclusion: Impact of Dynamic Arrays on Legacy Excel Formulas

Section 4: How Excel Features are Impacted by Dynamic Arrays

Introduction – DA Impact on Excel Features
Data Validation List using Spill Range (and Excel Table)
Excel Drop-down List (Excludes Blank Cells)
Dependent Drop-down Excel Data Validation (Unique & Sorted)
Conditional Formatting & Excel Dynamic Arrays
Excel Charts with Dynamic Arrays
Challenge: More Dynamic Dependent Data Validation Excel Report
Answer: More Dynamic Dependent Data Validation Excel Report
Conclusion: DA Impact on Excel Features

Section 5: Becoming Advanced in Excel Dynamic Arrays

Congratulations! You Completed the Fundamentals!
More Challenges & Practice Activities
Quiz: Challenge Successfully Completed?
Challenge Answers

Section 6: The ONE Theory you NEED to Use Excel FILTER Like a Pro

Why THIS Excel Theory is Important
Boolean Logic (TRUE & FALSE) in Excel Formulas Explained
Excel FILTER to Exclude Blanks, Zero-Value Cells & Text
FILTER Function for Multiple Criteria (AND Logical Test in Excel)
FILTER Formula for Either Value (OR Logical Test in Excel)
Challenge: Dynamic Array Filter Like a Pro
Answer: Dynamic Array Filter Like a Pro
Conclusion: How to Filter Like a Pro in Excel

Section 7: Excel Array Logic & Array Constants in Excel Formulas

Debugging Trick & Excel Array Syntax – Comma versus Semicolon
Named Array Constants for Quick Report Setup in Excel
FILTER Function with Array Constants for IF EMPTY Argument
Top 3 Values with Excel LARGE Function
Custom Dynamic Sort with Multiple Columns in Excel
Quiz: Working Correctly with Array Constants in Excel
Conclusion: Use the Correct Array Syntax in Excel Formulas

Section 8: Interactive Reports Made Easy with Excel Dynamic Arrays

Introduction – Interactive Excel Reports Made Easy
Easy Pivot Table-Style Reports with Dynamic Arrays
Pivot Table-Style Report with Row & Column Selection (INDEX MATCH & Dynamic Arrays)
Interest Paid per Month & Year with Excel SEQUENCE Function
Loan Amortization Table (Interest, Principal) with SEQUENCE in Excel
Dynamic Excel Calendar with SEQUENCE
Shrinking Data Validation – Hide Used Items in Excel
Searchable Data Validation Drop-down List in Excel
Challenge: Interactive KPI Selection Excel Report
Answer: Interactive KPI Selection Excel Report

Section 9: Advanced Data Analysis in Excel

Introduction – Advanced Data Analysis with Excel Dynamic Arrays
Multiple Match Results Across Columns (Excel FILTER Function)
Multiple Match Results in Single Cell (Excel TEXTJOIN)
Lookup Approximate Match with Excel FILTER Function
Find Top n Largest Categories & Values (FILTER) in Excel
Challenge: Top 3 Salaries & Employees by Department
Answer: Top 3 Salaries & Employees by Department

Section 10: Advanced to Expert: How Excel Arrays Really Work

Introduction – How Arrays Work in Excel
Lifting & Pairwise Lifting (Excel Arrays)
Broadcasting Excel Array Behavior
Implicit Intersection – The Main Legacy Excel Problem
Are Excel CSE Formulas No Longer Needed?
Quiz: How Excel Arrays Work Under the Hood

Section 11: More Advanced Excel Dynamic Array Examples

Introduction – More Advanced Excel Dynamic Array Examples
Unique Values From Nonadjacent Columns in Excel
FILTER for BOTH Criteria or EITHER Criteria (AND, OR Together in Excel)
Split Text & Numbers From 1 Cell to 2 Columns in Excel
Challenge: Filter Values From Non-Adjacent Columns in Excel
Answer: Filter Values From Non-Adjacent Columns in Excel

Section 12: Final Words & More Learning

Final Words

I’m Leila.
Nice to meet you.

Each month, I hop on a special video call with the Microsoft team. This is one of the perks of being a Microsoft MVP. They fill us in on all the latest features coming down the pipeline.

I love these calls and leave them as excited as a kid on their birthday.

But I also ask myself this question: “How are everyday people going to learn this?”

For years, I worked as a project manager at a multinational paper & packaging company. Excel was my #1 tool. Without it, I’d be lost. And let me tell you – I sure worked some magic at that company. Some of the Excel automations I had to create… wow, talk about beasts.

But you know the saying: What doesn’t kill you makes you stronger. And safe to say I can now use Excel with the best of them.

Leila Gharani - Instructor for Excel 365 Advanced Formulas Course

This course has enabled me to finish jobs in 5–10% of the time due to my ability to process Excel so far. It has also given me the confidence to better get around Excel.

Mitchell Ryan

Who Is Right For This Course

You’re in the right place if …

  • You HAVE OFFICE 365 or 2021 with the NEW Excel functions
  • Business Professionals who use Excel to analyze data & create reports
  • You currently use Excel and are familiar with SUM, IF & VLOOKUP functions
  • You’d like to learn how to create interactive reports with dynamic array formulas
  • You’d like a complete course to understand ALL there is to Excel’s NEW Calculation engine

Who’s Not Right For This Course

You’re in the wrong place if …

  • You are BRAND NEW to Excel
  • You don’t have Office 365 or Office 2021 with the NEW Excel functions
  • The course is taught for Excel on Windows. If you have a Mac, I wouldn’t be able to help you if you run into problems (I’ve never worked with a Mac).

Frequently Asked Questions

When does the course start and finish?

You’ll take this Excel 365 Advanced Functions course in your own time. It is a completely self-paced online course – so you decide when you start and when you finish.

How long do I have access to the course?

How does lifetime access sound? After enrolling, you have unlimited access to this course for as long as you like – across any and all devices you own.

What if I am unhappy with the course?

We would never want you to be unhappy! If you are unsatisfied with your purchase, contact us at in the first 14 days and we will give you a full refund.

What if I have questions about the lessons? Can I get those answered?

Yes! Most online courses leave you on your own, but not here at XelPlus.

We have a team of helpful teaching assistants (TA’s) ready to answer your questions about each lesson. Below each lesson, you’ll find a comment section where you can ask a question about the topic. Our TA’s will chime in and do their best to help you.

Who can I contact if I have questions not addressed?

You can contact us at

Save with a Bundle!

You’d like to improve in more than one aspect? Pick our Excel Black Belt package.

This course is a part of our Black Belt Package. Learn anytime that suits your schedule.

Excel New functions course cover

Master NEW Excel Functions in Office 365 & Office 2021


Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365.

You’ll create professional-grade reports in a fraction of the time it used to take you.

Excel Black Belt course cover

Black Belt Excel Package

Save $452 with a bundle!

Our Black Belt Package is your gateway to becoming an Excel Grand Master. With this package you get exclusive access to courses not available as stand-alone. Our step-by-step guidance makes learning easy, so you can say goodbye to confusion and frustration and hello to mastery.

Courses included

This means you get

  • Over 100 hours of video content (CPD Credits)
  • Certificate of Completion for individual courses
  • 600+ Workbooks and Templates
  • 200+ Challenges & Quizzes to test your skills
  • LIFETIME Access so you never feel rushed
  • Comment sections to ask questions and get answers from our friendly teaching assistants