Advanced Learning Singapore

Download Year 2024 Training Calendar

2-Day Excel 365 Dynamic Arrays Series 1 - Power Functions Course (Exclusive Course)

Course Overview

This course is created, owned and trained by Mr. Alaster Leong. Learn from the original creator of this course.

Important Note: 

Please check the following before registering:

  • The guide to check Excel 365  here.

The addition of Excel Dynamic Array functions is a long-awaited and greatly appreciated change. Despite many changes to Microsoft Excel over the years, one thing has remained the same – one formula, one cell to get the result.

With dynamic array formulas, multiple results will be returned and automatically spill to a range of cells, whereas in old array formulas, multiple results must be copied to many cells. This makes the new Excel Dynamic Arrays extremely powerful and versatile, with numerous potential applications. With this ability, existing functions that return only one single value can now return multiple values and perform magic!

Plus, the output is always dynamic; whenever the source data changes, the results update automatically. Delete and insert rows in old array formulas range is not possible without first deleting all existing formulas; with dynamic arrays, you can insert and delete rows without any problems! They are unquestionably valuable additions to the Excel formulas toolbox!

This Excel 365 Series 1 – Dynamic Arrays and Power Functions course will also take you through the basic of Excel LAMBDA, which enables you to create your own Microsoft Excel custom function without writing any code, and how you can use it within the workbook, as well as a recursive LAMBDA that loops through cells to replace multiple values. In addition, it will also cover some of the recent Excel 365 functions.

Dynamic arrays are the most significant change to Excel formulas in years, changing the way we use functions and formulas. A variety of tasks that were traditionally difficult are now easily accomplished with the new dynamic arrays.

In Excel 365, array formulas became much more straightforward and understandable for all users. The old array formulas are kept for compatibility, but it is now recommended to use the new ones.

Excel dynamic array formulas simplify many complex tasks, and we believe that many Excel users will love them as much as we do! You’ll never want to go back once you’ve seen how the new dynamic arrays work!

Course Information


SGD 910.00 (Nett)

Absentees will be charged the full course fees


9:00am to 5:00pm (14 hours)


Classroom @ Hotel

(Venue may be subject to changes)


11-12 Jul 2024
12-13 Sep 2024
18-19 Nov 2024

Why Learn With Us?

Upon completion of the course, learners will be able to:

  • Maximize the potential of the NEW DYNAMIC ARRAY Formulas in Excel
  • Get a better understanding of the new Excel spill behavior and characteristics
  • Understand how the new Excel has changed the way you use the legacy Excel and how it will impact existing formulas in the future
  • Use LAMBDA functions to iterate your process which is not possible with the old Excel functions
  • Have an understating of how to solve complex problems such as splitting text from alphanumeric values and more…

Perfect for Excel users at work, this course improves task efficiency and overall productivity.

This is a hands-on practical course. Trainer will walk through the topic step-by-step. Learners will be provided with exercise files on every topic to effectively apply what have been taught. A short Q & A session will be available after each topic.

*Note: This course is not intended for basic Excel users.

To undergo our Excel 365 Dynamic Arrays Series 1 – Power Functions training course, you will need to be:

  • A Windows operating system laptop with Office 365 installed (personal laptop is preferred as some company laptops may have security restrictions).
  • Laptop power adaptor and a USB mouse

Course Outline

  • What is Formulas Spill?
  • Spill and Dynamic Array Characteristics
  • SORT
  • When to use SORT and SORTBY functions
  • Boolean Logic
  • Operators used in Boolean Logic
  • IFS
  • LET
  • BYCOL (LAMBDA helper function)
  • BYROW (LAMBDA helper function)
  • MAP (LAMBDA helper function)
  • REDUCE (LAMBDA helper function)
  • SCAN (LAMBDA helper function)
  • MAKEARRAY (LAMBDA helper function)
  • Bonus Topic – Recursive LAMBDA

This course is created, owned and trained by Mr. Alaster Leong. Learn from the original creator of this course.

Alaster has been training adult learners in IT in both the private and public sectors since 1986. Alaster’s career before training centred around IT for over 12 years, as he held various roles within software development. He has earned himself a Microsoft Office Specialist (MOS) certification and he is an existing Trainer approved by People’s Association (PA). He was also a former Associate Trainer with the Civil Service College.

He specialises on training Microsoft Applications and Adobe Photoshop. Based on his vast experience and keeping abreast with the emerging trends, tools and technologies with the IT industry, especially software, he has extensively involved in developing several course outlines and course materials and conducting training to equip learners with skills in-demand or likely to be most important in the jobs of the future work.

Alaster constantly explore innovative and effective ways in imparting knowledge and engaging his learners at the same time. He also focuses his training on providing practical solutions to real-life problems, especially Excel. He is more than willing to help overcome various problems faced by his learners that arise in their workplace in applying their newly-acquired knowledge and skills any time after the course. He is well-known for his dedication, commitment and passion to training that makes him well-liked and appreciated by his learners.

Share This Course

Scroll to Top