Dynamic Arrays: The new hotness in Microsoft Excel
Phillip Wong
Financial planning, tax, accounting, automation tech, and business education.
Very soon Microsoft will release have a new set of functions known as Dynamic Arrays. These are going to be the most important Excel skill to learn since you figured out a pivot table or =INDEX(MATCH).
Dynamic arrays referenced by a #hash following the top left cell reference, e.g. A1#. The # will automatically resize the array contextually based on next available cell to the left or the right. No longer will we need to reference a static array as A1:D5. Nope, it’s just A1# and off you go.
This is a big deal.
How do I get Dynamic Arrays?
At the moment Dynamic arrays functionality is available in Preview for users of Office 365 Insiders Program from September 2018. When will they roll it out to the rest of the user base? No one knows.
However, perhaps you may wish to give some twitter love to the engineers at Microsoft. <3
Joe McDaid, Program Manager, Excel Development Team at Microsoft (Tweet @jjmcdaid)
Olaf Hubel, Senior Excel Development Team at Microsoft (Tweet @olafhubel)
How do Dynamic Arrays work?
Leila Gharani has a neat video that explains what they are and how they work.
MrExcel has a super clear PDF e-book on how to use these new formulas. Link here: https://www.mrexcel.com/download-center/books/2018/ExcelDynamicArraysStraightToThePoint.pdf
I don't think I have ever been this excited about an Excel capability since I learned how to use =Sum() in primary school.
Happy studies folks!
- Phillip Wong
27 Oct 2018
https://www.youtube.com/watch?v=2USJsIyIzvo