Dynamic Arrays: one formula instead of a hundred

blank

We’re all used to writing a formula, pressing enter, then copying it down rows or across columns, over and over. In modern versions of Excel (Microsoft 365), there’s a genuinely better way to manage this: Dynamic Arrays. Write one single formula in one cell, press Enter, and Excel instantly spills the results into all the neighbouring cells that are needed, no dragging, no copying, and it automatically expands or shrinks as your data changes. This is what I’d call ACE.

The functions worth knowing first

UNIQUE() returns every distinct value from a list instantly, brilliant for pulling a clean list of customers, suppliers or codes with none of the manual duplicate removal that used to be needed.

Dynamic Arrays: The functions worth knowing first (step 1)

Dynamic Arrays: The functions worth knowing first (step 2)

SORT() arranges a list into order dynamically, and wrapping it around UNIQUE like =SORT(UNIQUE(A2:A10)) gives you a clean, alphabetically sorted list of unique values in one single formula. Add PROPER() around the outside too and you can tidy up inconsistent capitalisation at the same time.

Dynamic Arrays: The functions worth knowing first (step 3)

Dynamic Arrays: The functions worth knowing first (step 4)

Dynamic Arrays: The functions worth knowing first (step 5)

FILTER() returns only the rows that meet a condition you set, for example =FILTER(A1:E16,C1:C16=”Marketing”) pulls every row relating to Marketing with no manual filtering or copying required, and your original data stays completely untouched.

Dynamic Arrays: The functions worth knowing first (step 6)

Dynamic Arrays: The functions worth knowing first (step 7)

You can wrap SORT around a FILTER too, to sort the results by whichever column you choose.

Dynamic Arrays: The functions worth knowing first (step 8)

Dynamic Arrays: The functions worth knowing first (step 9)

SEQUENCE() generates a list of numbers or dates in one go, useful for building reporting periods or invoice numbers without dragging a fill handle down a column.

Dynamic Arrays: The functions worth knowing first (step 10)

Combining them for something genuinely powerful

The real magic happens when you stack these together, for example:

=SORT(UNIQUE(FILTER(A2:A16,B2:B16=F1)))

filters your data down to a specific selection, removes duplicates, sorts the result, and updates automatically the moment anything changes. One formula replacing what used to be several manual steps, or a Pivot Table, depending on what you needed.

Dynamic Arrays: Combining them for something genuinely powerful (step 11)

Dynamic Arrays: Combining them for something genuinely powerful (step 12)

A couple of practical notes

Dynamic Arrays work in Microsoft 365 and Excel Online (I also wrote about them for Executive Support Magazine) (not in older perpetual versions), and they play especially nicely with Excel Tables, for example =UNIQUE(Table1[Customer]) instead of a fixed cell range.

If a formula tries to spill into cells that already contain data, you’ll see a #SPILL! error, Excel simply doesn’t have anywhere to put the results. Clear whatever’s in the way and the formula will spill exactly as expected.

Start simple

Try =UNIQUE(A:A) on your own data today, and build up to combining functions as your confidence grows. Once you see how much manual tidying up one Dynamic Array formula can replace, it’s genuinely hard to go back to copying formulas down a column the old way.

Share This Article

More To Explore

Join the Excel Ace community!
Get FREE Excel tips, guides, and industry insights delivered straight to your inbox. Sign up for our newsletter today!
Name