Game Changer: Excel Dynamic Arrays
Join me with Abbott Katz for a special 2-day Excel Training on Dynamic Arrays for only $99.00. This is a special one-time offering at a bargain price. Dynamic Arrays will change the way you work in Excel and save you hours. Learn more about the course by downloading the course brochure here
Buy the course for only $99.00
If you listen to many of the Excel MVP’s you can see how excited they are for dynamic arrays one article said: “Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever.” - Dynamic array formulas in Excel | Exceljet
One of the things I really enjoy at work is introducing dynamic arrays to people and watching them get excited about the possibilities. Dynamic Arrays were first talked about by Microsoft in September 2018 and have been available in general release since January 2020. The release of Dynamic Arrays required a complete overhaul of the Excel Calculation engine and they are only available in Office 365.
Prior to the new dynamic arrays the way people primarily used arrays in Excel was by using the command Control Shift Enter to put {} brackets around code. If you forgot the brackets or in some cases did not highlight the right number of Excels you received an error. For me this was one of the more difficult things to learn in Excel and eliminating the need to put {} around the code was a huge step forward for making array formulas more main stream.
With the release of Dynamic Arrays Microsoft also released the following unique functions in Excel
Filter - Filter a range or array
Randarray - Returns an array of random numbers
Sequence - Returns a sequence of numbers
Sort - Sort a range or array
Sortby - Sorts a range or array based on the values in a corresponding range or array
Unique - Returns the unique value from a range or array, eliminates need for complex formulas to create unique lists.
Xlookup - Searches a range or an array for a match and returns the corresponding item from a second range or array. By default an exact match is used. - No more vlookup :)
Xmatch - Returns the relative position of an item in an array. By default, an exact match is required
For the rest of the article I would like to focus on some great resources for learning more about Dynamic Arrays and how they can change the way one works in Excel and builds financial models.
DYNAMIC ARRAY ARTICLES
Well lets get started talking about some of the best blogs for becoming familiar with Dynamic Arrays. Given how big the change was when Dynamic Arrays were released it was hard to share only a few articles on this subject a quick google search will return ~ 3 million results.
The first reference is an article from Microsoft support that explains the behavior of array and formulas and how spilling works.
Dynamic array formulas and spilled array behavior - Office Support (microsoft.com)
The next reference is from one of my favorite people in Excel Chandoo. The first Microsoft Excel course I ever paid for was from Chandoo and his website Chandoo.org has a ton of great information. In this article he explains dynamic arrays the new function operators, goes into how each new function works, provides examples of how to use dynamic arrays with existing excel functions. The article provides workbook examples, a video on the subject, and the opportunity to sign up for his Dynamic Array course.
The next article gives a high level overview of Dynamic Arrays and then talks about how to use each function. The article is from the Simon Sez IT website.
Dynamic Arrays in Excel: Eight, Must-Know Formulas - Simon Sez IT
Another really good article on Dynamic Arrays is from The Excel Club the article talks about how Dynamic Arrays will change the way you work in Excel and will give some great examples of how these new functions works in Excel.
Excel Dynamic Arrays – A new way to model your Excel Spreadsheets - (theexcelclub.com)
Below are a few other resources and articles I recommend on Dynamic Arrays
Excel Dynamic Arrays - Excel will never be the same - Xelplus - Leila Gharani
Dynamic Arrays 1 - Excel University (excel-university.com)
Excel Dynamic Arrays • My Online Training Hub
DYNAMIC ARAY EXCEL VIDEOS
The first video on my list is Leila Gharani you will find if she has a video on the subject odds are it will show up on my list. She has an outstanding teaching style and in my opinion is a most follow when it comes to learning excel. In addition to the below video she has an entire playlist dedicated to Excel for Office 365 and Dynamic Arrays and it includes 23 videos teaching you all about the subject
Next on the list is a video titled Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas. The video is nearly 40 minutes long but includes 21 “Amazing Examples” of how you can use the Modern Dynamic Arrays. In addition to the below video he has a playlist with 90 videos explaining Dynamic Arrays. If you want to master Dynamic Arryas this is a great place to start.
Excel Dynamic Arrays - The Power of Array Formulas Playlist of Videos - YouTube
The next video is a live recording of a class taught by Chandoo on Dynamic Arrays. This video is nearly two hours long so make sure you have plenty of time if you are wanting to watch this video but plan to get a very good understanding of Dynamic Arrays when done.
CONCLUSION
Taking the time to learn and master Dynamic Arrays will change the way you work in Excel. Many Excel MVP’s have comments that Dynamic Arrays will change how you work in Excel forever and is one of the best things Excel has released in years. Leave your thoughts in the comments.