What are Dynamic Array formulas?

Dynamic Array formulas 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 basic example.

Here we have the shell of a multiplication table.

What formula can we use to fill in the data?

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.

This works fine, but it requires several careful steps.

Another option is a traditional array formula.

In this case, we first choose 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 using control + shift + enter.

you’re able to see Excel adds curly braces when we enter an array formula this way.

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.

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

Also, notice that all cells display 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 modify the first formula, and press Enter.

And all formulas are automatically updated.