Exciting Dynamic Array functions in #Excel 365

Have you heard of the new Dynamic Array functions recently available in Excel 365 (currently for Insider Fast users only)?

If you are an advanced Excel user, you will be impressed by these new functions.  They are simply awesome, super powerful and have lots of potential to solve “used-to-be” complicated problems in Excel with ease.

Leila Gharani has great example to show you how the Dynamic Array functions saves 30+ minutes of time in solving a complex problem, comparing to using traditional array formulas.   Check this out from her YouTube video: 

Isn’t it amazing?

I want to highlight one point: The time saved is much much more than 30 minutes.  We have to take into consideration of the complexity of array-formula. It is something we cannot master in just 3 days (or weeks).  However from the demonstration of Leila, you will see that the new functions are so simple, and the logic flows so smoothly.  Everything is so simple.  As Leila said, it will change EVERYTHING.

I cannot agree more….. IF the corporate world is adapting to Office 365 quick enough, at least not too late… 😦

Why I am saying this?

In recent in-house Excel training sessions, I asked participants (more than 50 ppl) if they know about Excel Table, a feature that has been introduced since Excel 2007.  Yes, it was 11 years ago.

(My company was using Excel 2010 when I delivered the training. We just updated to Excel 2016 this week.  You know what, many users “complain” about the update because they are not familiar with the “new” interface and they have no idea what is new in Excel 2016 vs. 2010).

To my surprise, indeed not to my surprise, none (ZERO) of them knows about Excel Table.  Text-to-Columns is another hidden gem that resides in Excel for decade.  Try to do little research and see how many of your co-workers know about it.

Because of this, well… Iknow it is a non-scientific research but I have high level of confidence :), I guess most of the Excel users will have never used (or even heard about) Dynamic Arrays in Excel 2028.

An unreachable tool could be a source of frustration

With no doubt, I am super excited about the new Dynamic Arrays and looking forward to it.  However I am also foreseeing frustration at the same time.  Why?

Think about this, you know very well that you can use a can-opener to open a can in 10 second; however you have no can-opener in your workplace, in where your job is to open can.  Your employer has no idea what a can-opener is and still think that you can open can with these – the tools available to you:

Can opener

Is it not frustrating?

While the product team of MS is working hard and doing great to provide all the good stuff, it is not changing the world if the corporate world is not using it.

Well, I know I talk too much non-excel… 😛

Want to learn more about Dynamic Arrays, here’s some nice videos for your reference:

From ExcelIsFun



Also check out the YouTube channel of MrExcel.com, where you can learn the new dynamic array functions in multiple bite-size videos.



About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in General and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.