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? 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. 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 it 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 using control + shift + enter. You can see Excel adds curly braces when we enter an array formula this way. To modify the formula, we need to select all formulas, make the change, and then enter the formula 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. 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
