Supplementary Guidance Dynamic Array Formulas

The New Best Practice Approach

Most Excel users remain unaware that there is a new best practice way to go about writing formulas in Excel.

For decades, the ‘correct’ way to go about populating cells has involved the same routine:

This approach—which is still valid and undoubtedly still features in the majority of effective Excel use around the globe—usually results in having almost (but not quite) the same formula in every affected cell.

The new approach (known as a “dynamic array formula”) results in exactly the same formula in every affected cell.

Dynamic array formulas can be written in the Microsoft 365 version of Excel, and also Excel 2021 (but not Excel 2019). Let’s have a look at how to write them and how they differ to the traditional method...

How to Write a Dynamic Array Formula

The best way to learn how to write a dynamic array formula is to convert a traditionally-written formula. Let’s work with a simple LEFT formula (covered in MyExcelSkills Module 3 Level 1):

Traditional Formula

Where † is the single cell containing the text, and ¶ is the number of characters to return.

You’d normally write this formula in the top cell of the range you want to populate it with, then copy/drag/double-click it down. Or you might write it in the leftmost such cell and copy/drag it to the right.

Assuming no need to lock cell references (and that we want to extract 10 characters from each of the values in column A), you end up with something like:

Note how the cell reference † automatically increases in one-row increments (A3, A4, A5 etc.)

If we copied the formula down such that it covered 100 rows, the final cell would contain =LEFT(A101, 10).

Dynamic Array Formula Equivalent

There is just one difference here. Instead of referencing a single cell like the traditional formula, the dynamic array formula references a range of cells covering all the cells we want to run the formula against.

To continue the example above, we would enter =LEFT(A2:A101, 10) once only in cell B2. Excel will then immediately and automatically flow the same formula down to cover all 100 cells (with the 99 ‘non-entered’ formulas being grayed out in the formula bar to indicate their subservience to cell B2):

This process of ‘immediately and automatically flowing the same formula’ to match the shape of † is called spilling.

If when you enter your dynamic array formula you are met with a #SPILL! error, that is simply Excel’s way of telling you “Something is in the way!”, i.e. one or more of the cells it wants to flow to are already populated. Simply remove the offending ‘blockers’ (i.e. delete or move the existing cell contents) and Excel will again immediately and automatically perform the flowing operation (AKA the ‘spilling’).

Advantages of Dynamic Array Formulas

There are three main reasons why we’d encourage you to make the move to dynamic array formulas over the traditional method:

Less need to consider $ signs

With the traditional method, you end up tapping F4 (or ⌘ + T on Mac) a lot to cycle between relative (e.g. A2), absolute ($A$2) and mixed (A$2 and $A2) references, or manually typing the $ symbol(s) in the right places.

With dynamic array formulas, you don’t have to worry about this. Because you only enter the formula once—and in only one cell—you don’t need to worry about how it will translate out to other cells.

Less scope for errors and inconsistencies

It is quite frequent for a traditional copied-down formula to end up inadvertently changed in a further-down row. A common cause of this is amendments being made and copied down whilst a filter is in operation, with the change (unknowingly to the user) only making it through to visible cells, leaving filtered-out rows unaffected.

Dynamic array formulas are only entered in one cell, so there is only one cell to make a change to. This eliminates the inconsistency issue. If you try to change a further-down row, you will cause an explicit #SPILL! error in the original dynamic array formula cell, which is much easier to detect and do something about.

Efficiencies through new dynamic array formula functions

Useful new formulas that only work as dynamic array formulas have been added to Excel. Examples include UNIQUE, FILTER and SORT.

These enable you to achieve things that typically previously involved PivotTables. For example, let’s compare methods for getting to the unique values in a range of cells (let’s say G2:G1000):

(Inefficient) ‘Quick and Dirty’ Traditional Method

There are certainly ‘non-best practice’ steps here, but it’s a common day-to-day route for getting to a list of unique values:

(Efficient) Dynamic Array Formula Method

1. Enter formula =UNIQUE(G2:G1000)
2. (That’s it. Excel immediately and automatically populates the rows beneath with the unique values.)