Exceljet

Quick, clean, and to the point

What are Dynamic Array formulas?

In this video, I'll explain the basic idea of dynamic array formulas.

Dynamic Arrays are the biggest change to the Excel formula engine in years. Maybe the biggest change ever. This is because Dynamic Arrays make it easy to work with many values at the same time.

For many users, this will be the first time they understand and use array formulas.

Let's look at a simple example.

Here we have the shell of a multiplication table.

What formula can we use to fill in the data?

Well, before dynamic arrays, we'd have two choices: a formula with mixed references, or a multi-cell array formula. Both of these options are tricky in their own way.

With mixed references, we start by multiplying B5 times C4.  Then we carefully the lock column in B5, and carefully lock the row in C4.

Then we can copy the formula down, and across.

=$B5*C$4 // mixed reference

This works fine, but it requires several careful steps. And, if we want to modify the formula, say add 1 to results, we need to edit the first formula, then copy again throughout the table.

Another option is a traditional array formula. In this case we first select the entire table. Then we multiply the values in column B by the values in row 4 directly.

Then we need to enter the formula with control + shift + enter. You can see Excel adds the curly braces when we enter an array formula this way.

={B5:B14*C4:L4} // array formula

To modify, we need to select all formulas, make the change, and then enter again with control + shift + enter.

Again, this works, but there are several careful steps.

Now, with dynamic arrays, we have a much more elegant solution.

In the first cell, I can provide a formula that multiplies the two ranges together.

When I enter the formula, it automatically fills the grid.

=B5:B14*C4:L4 // dynamic array

This formula contains no fancy mixed references and no curly brackets.

Also notice that all cells show the same formula. In other words, there is just one formula that returns many results.

Making changes to a dynamic array formula is also straightforward.

All I need to do is change the first formula, and press enter.

And all formulas are automatically updated.

Dynamic Array Formulas are available in Excel 365 only.

Related shortcuts

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.