Get the latest insights
delivered straight to your inbox
Jan 6, 2023
Philip Wiest, Guest Blogger
If efficiency is intelligent laziness, dynamic arrays and array functions are your new best friends.
In September 2018, Microsoft upgraded the Excel calculation engine to support a new generation of formulas and functions featuring dynamic arrays and array functions.
The magnitude of this change to the new Excel engine cannot be overstated.
In short, you can write one formula and target an array of inputs and it “spills” down to an array of output cells.
Additionally, one formula can contain a function that outputs an array of results — like a multi-cell list.
One formula!
For example, how would you write a formula to add Phase 1 and Phase 2?
That’s a lot of formulas, and a lot of places where things can go wrong!
Or would you write one formula and let it “spill” to other cells?
Managing one formula is simpler than managing five, yes?
When you need to extract a UNIQUE list from a column of cells, and then SORT the output, how many formulas would that take? How many steps would that require?
Using the UNIQUE Function on the Range and the SORT Function on the results is a “game changer.”
In the following example, you’re using one formula to extract a dynamic SORTed, UNIQUE range!
When you need to calculate the SUM for each person in ONE dynamic formula, you are SUMming the Values by looking up the SPILL range of Unique Names (G12#).
Wait, you mean there’s no VLOOKUP?
That’s right!
Use SUMIFS (in other words, if the name matches the new dynamic array, sum the values).
And, when the list of names changes, your UNIQUE list updates “automagically.”
And you didn’t have to re-write your output formulas!
Welcome to the new calculation engine behind Microsoft Excel.
Don’t let the newness of dynamic arrays prevent you from learning to do things more efficiently — embrace the new intelligent laziness.
It’s easier than you think!
Ready to learn more? Check out some of SkillPath’s live virtual training programs, on-demand video training or get it all with our unlimited eLearning platform.
Philip Wiest
Guest Blogger
Philip Wiest is an expert software trainer, computer analyst and database consultant who knows both the intricacies of computer systems and the ways today’s professionals need to use these essential business tools. Using his experience in Microsoft Excel and Microsoft Office, as well as Windows and the Internet, Phil uses a special mix of tactful guidance and distilled observation so his audiences learn and retain the critical keystrokes, application combinations and creative processes that save time and simplify computer use.
Latest Articles
Article Topics