I think about Excel stuff probably over 8 hours a day! And there have actually been two times when I dreamed about an Excel trick I should try out! One of my dreams was in mid-2019, not long after Microsoft announced a new feature – “dynamic arrays”. This is a feature in which you can enter a formula in just one cell and see the results in many cells. Just for a really simple example, entering =SEQUENCE(5) in cell A1 gives this result:
Nothing at all was entered into cells A2 through A5.
Well, in my dream, I envisioned a feature from an old version of Excel – Version 4
(there have been well over 10 versions since then) married to this new dynamic array feature.
In that old language, you could enter =FILES() and you would only see =FILES(), but if you looked at the result, somehow, there’d be the name of a file in that cell, like this, perhaps:
But I also knew that that one cell actually contained LOTS of file names – you just couldn’t display them in one cell! Just so you can get a sense of the enormity of this discovery, here’s the same screenshot but with the results expanded (and this is just an excerpt!):
So, when I woke up, I just couldn’t wait to try this out! I defined a name, FL, to be =FILES() The cell A1 contains =FL. The rest are empty. Look at the result here:
This shows through row 36, but it continued to row 172!!
I was floored! Wow!
I shared this with many of my fellow MVPs. Every one of them gave the same enthusiastic assessment.
Attached is an excerpt from Excel MVP Leila Gharani’s video of this. In the first 3 seconds, she says “This one really blew my mind!” The rest of the video gives more details on how one could use this feature.
And at 1:05, she shares my book, “This isn’t Excel, it’s Magic!”, IIL Publishing:
Bob Umlas
Bob Umlas has been using Excel since version 0.99 (on the Macintosh)! He was a contributing editor to Inside Microsoft Excel for many years. He has had more than 300 articles published on subjects ranging from beginner to advanced macros, and on tips, shortcuts, and general techniques using virtually all aspects of Excel.
He was voted an “MVP” (Most Valuable Professional) by Microsoft each year from 1993-2018 (25 years!) for his contributions to the various online Forums about Excel and is known world-wide for his contributions in Excel. He is the author of “This isn’t Excel, it’s Magic!” which is available from http://www.iil.com/publishing as well as from Amazon.com. He has had more than 300 articles published on subjects ranging from beginner to advanced macros, and on tips, shortcuts, and general techniques using virtually all aspects of Excel.
From 1998 to 2018 Bob worked for a major tax and accounting firm, using Microsoft Excel® 8 hours a day, writing custom applications for staff and clients.
Disclaimer: The ideas, views, and opinions expressed in this article are those of the author and do not necessarily reflect the views of International Institute for Learning or any entities they represent.
