MS Excel — Introducing Dynamic Arrays and Functions
Introduced by Microsoft in September 2018
** UPDATE ** — Dynamic Arrays are fully available to all Microsoft 365 subscribers! This alone makes the Office subscription service a must have service!
Although not available to the general Excel user base yet (and, then, only for Office 365 users), there has been a lot of buzz about the new Dynamic Arrays and Array Functions that are coming to Excel.
Many of my “go to” resources have been talking about it and, clearly, it is a huge deal for Excel users. Dynamic Arrays and Functions introduces a lot of pretty fantastic capabilities (and that will undoubtedly only get better as it begins to get applied in the Excel community).
So, here is a compilation of resources I have found very useful to educate me — so far — on what is coming down the road (early-2019 is the speculation at this point).
There’s a lot here, but get started and you will develop a real sense for the magnitude of change (and opportunity) with the new Excel!
Microsoft Announcement (Ignite Conference)
“With dynamic arrays, we continue to invest in making advanced formulas easier to use. Using dynamic arrays, any formula that returns an array of values will seamlessly “spill” into neighboring unoccupied cells, making it as easy to get an array of values returned as it is to work on a single cell.”
Bill Jelen, MrExcel
“Introduced on September 24 2018, the new SORT, FILTER, and UNIQUE make everything easier in Excel formulas!”
Bill Jelen, MrExcel
Bill’s e-book, “Excel Dynamic Arrays Straight to the Point” is a free download through the end of 2018. Take advantage of this opportunity — it’s a great resource!
The following article from the November 2018 issue of Strategic Finance Magazine is another excellent introduction to Dynamic Arrays by Bill Jelen.
I also found this video by Bill to be insightful — an indication of how our Excel workflow might change as a result of this new functionality.
Excel Replace a Pivot Table with 3 Dynamic Array Formulas
The following two videos show how our workflow processes may change in a couple of instances (and I’m sure there will be many others!)
Excel Dynamic Arrays: Fully Dynamic Cross Tabulated Reports? Unbelievable!
VLOOKUP: Step Aside! ExcelIsFun Video
Oz Du Soleil
Oz’s content is entertaining as much as it is informative — definitely fun to watch and a lot of great insight conveyed!
Excel Dynamic Arrays: SORT, FILTER, and Dynamic VLOOKUP
As you would expect, there are some issues — or considerations — that every Excel user needs to be aware of. Dynamic Arrays are unique and we will not be able to use the results that way we are used to using Excel data.
Here are a few that Oz spotted — and I’m sure many more will come up in real-world application!
5 Dynamic Array Gotchas in Excel
Leila Gharani
“What you’re about to see is gonna change everything.”
Excel Dynamic Arrays (How they will change EVERYTHING!)
I hope you found a few informative nuggets here!
I can’t wait for this capability to land in my Office 365 — how about you?
