Understanding Array Functions in Microsoft Excel

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.

 

What are Dynamic Arrays and Array Functions?

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?

• Would you write “=C3 + D3” and copy it to four other cells?
• Would you write “=SUM(C3:D3)” and copy it to four other cells?

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?

Boost your digital skills when you register now for: Getting the Most from Microsoft Excel, a live, virtual workshop.

 

What about Array Functions?

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!

But wait, there’s more

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.