2-Day Excel 365 Series 1 - Dynamic Arrays
And Power Functions Course
In Singapore (New & 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.
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, 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!
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…
Who Should Attend?
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:
- An intermediate Excel user who already have Excel formulas and functions working knowledge
- Proficient in advanced Excel formulas and functions skills or an advanced or expert Excel user.
*Note: This course is not intended for basic Excel users.
Things To Bring
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)
1. Dynamic Arrays & Spill Behavior
- What is Formulas Spill?
- Spill and Dynamic Array Characteristics
2. Dynamic Array Functions
- When to use SORT and SORTBY functions
- Boolean Logic
- Operators used in Boolean Logic
3. Power Functions
- 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
- TEXTBEFORE & TEXTAFTER
- VSTACK & HSTACK
- CHOOSECOLS & CHOOSEROWS
- WRAPROWS & WRAPCOLS
- TAKE & DROP
- Bonus Topic – Recursive LAMBDA
Our Dedicated Trainer
Mr Alaster Leong
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.
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.
No laptop will be provided. Bring Your Own Laptop Course.
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.
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.
Details of the hotel will be confirmed one (1) week prior to the course start date.
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.
Complimentary car park coupon upon request (subject to availability and confirmed on a first come, first served basis).
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
Create Digital Fill-In Form In Word And Auto Import Form Data From Excel Course
Data Analysis Using Dynamic And Interactive Excel Dashboard Course
Data Analysis With Excel Power Query, Data Model And Power Pivot Course
Excel 100 Tips And Techniques Course (Exclusive)
Excel Advanced Formulas And Functions Course
Excel Automation With VBA Series 1 – Fundamentals Course
Excel Data Cleansing And Building Techniques Course
Excel Pivot Table In-Depth Course
Excel Power Query – Automating Data Preparation (Exclusive)
Power BI Desktop Series 1 – Data Model, DAX And Dashboard Visualization Course (Exclusive)
Power BI Desktop Series 2 – Advanced DAX Formula (New & Exclusive)
PowerPoint Tips And Techniques Course