This course is conducted in PC/Windows operating system. There will be no laptop provided. Learners are required to bring along their own laptops.

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

Power functions in Excel 365 Dynamic Arrays simplify many complex tasks, enabling you to get your results faster, shorter, and much easier

All our courses are not subsidised and non-claimable using SkillsFuture credits

Excel 365 Series 1 - Dynamic Arrays And Power Functions Course Overview

Learn from the original creator of this course – Mr. Alaster Leong.

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, which were released in August 2022.

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!

The guide to check Excel 365  here.

At the end 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…

This course will benefit people who:

  • Already have advanced Excel knowledge
  • Are advanced Excel users who require to use a lot of formulas and functions to solve their work problems
  • Are advanced or expert Excel users who want to learn higher-level functions in Excel 365 Dynamic Arrays

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

This is a hands-on practical course. Our trainer will walk you through each topic step-by-step. Those undergoing our Power Query training will also be provided with exercise files to practice the application methods at every stage. A short Q & A  session will be available after each topic.

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

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

Below are the list of things to prepare for the Excel 365 Series 1 – Dynamic Arrays and Power Functions course:

  • PC/Windows operating system laptop with Office 365 Microsoft Excel installed
  • Laptop power adaptor
  • USB mouse (optional)

Course Outline

  • What is Formulas Spill?
  • Spill and Dynamic Array Characteristics
  • FILTER
  • SORT
  • SORTBY
  • When to use SORT and SORTBY functions
  • UNIQUE
  • SEQUENCE
  • RANDARRAY
  • Boolean Logic
  • Operators used in Boolean Logic
  • IFS
  • MAXIFS
  • MINIFS
  • TEXTJOIN
  • AGGREGATE
  • SWITCH
  • XLOOKUP
  • LET
  • LAMBDA
  • BYCOL (LAMBDA helper function)
  • BYROW (LAMBDA helper function)
  • MAP (LAMBDA helper function)
  • REDUCE (LAMBDA helper function)
  • SCAN (LAMBDA helper function)
  • MAKEARRAY (LAMBDA helper function)
  • TOCOL & TOROW
  • TEXTSPLIT
  • TEXTBEFORE &  TEXTAFTER
  • VSTACK & HSTACK
  • CHOOSECOLS & CHOOSEROWS
  • WRAPROWS & WRAPCOLS
  • TAKE & DROP
  • EXPAND
  • Bonus Topic – Recursive LAMBDA

Our Dedicated Trainer

Learn from the original creator of this course.

Having trained adult learners from both the public and private sectors, Alaster possesses a wealth of experience in IT training. In his pre-trainer years, Alaster has held many roles within the realm of software development, with a career that spans more than 12 years in the IT industry. Prior to being approved by People’s Association (PA), he was also an Associate Trainer within the Civil Service College. To date, he holds a Microsoft Office Specialist (MOS) certification and specialises in training Microsoft Office Applications and Adobe Photoshop.

With his vast knowledge and commitment to staying up to date with emerging trends, tools, and technologies within the IT and software industry, Alaster is extensively involved in developing course outlines and materials that bring value to learners, and equip them with up-and-coming skills which are in demand in the workforce.

Through implementation of innovative and effective methods of imparting knowledge, Alaster is able to engage his learners in courses he conducts. A large part of his training regime focuses on providing practical solutions based on real-life problems, especially in the Excel courses he conducts – such as the Excel Dynamic Arrays training course. As an affable and engaging trainer, Alaster is always willing to aid his learners in navigating problematic scenarios using the skills acquired, even after course completion. Well-known and liked for his dedication, commitment and passion for training, Alaster is very much appreciated by his learners.

This Excel 365 Series 1 – Dynamic Arrays and Power Functions course is developed and conducted by Mr Alaster Leong himself. Those seeking to explore using Dynamic Arrays in Excel 365 can sign up for this training session.

Course Registration

All Advanced Learning Singapore courses are not subsidised and non-claimable using SkillsFuture credits.

For organisations applying with more than 2 learners, please download and complete the registration form.

All registration forms should be submitted at least 2 weeks prior to course commencement. Many courses have pre-requisites, which are intended to foster a learner’s success in the course. Be sure to check out the necessary requirements before registering. Contact us for more information.

This course is not subsidised and non claimable using SkillsFuture credits.

Course Information

Course Code:

ALSIT-EDA1

No laptop will be provided. Bring Your Own Laptop Course.

Course Fee:

S$910.00 (Nett)

Inclusive of coffee breaks with refreshments and bento / buffet lunch.

Call us for a group rebate when you register in a group of 6 or more.

Lower course fee is also available for closed class at your premises. Request a quote.

Absentees will be charged the full course fee.

Course Dates:

2 days, 9:00am – 5:00pm
(14 hrs, exclude lunch break)

If you have a group of 6 or more preparing for the same course, you can opt for your own closed class at a date of your choice, subject to trainer availability. 

Please note that closed class is not customised class.

Training Venue:

Details of the hotel will be confirmed one (1) week prior to the course start date.

Registration:

For Company-Sponsored learners: Click here to download registration form.

For Self-Sponsored learners: Click here to download registration form.

You can also register online here.

Others:

Complimentary car park coupon upon request (subject to availability and confirmed on a first come, first served basis).

Note:

ALS reserves the right to reschedule, postpone or cancel any course, change the trainer and venue at short notice and at its absolute discretion, due to insufficient enrolment, class size, availability and circumstances outside our control.

More Original Courses Developed & Trained By Alaster