Excel 2024: Dynamic Arrays Can Spill

July 29, 2024 - by Bill Jelen

Introduced in 2019, the new Dynamic Arrays represent a major change to the Excel calculation engine. While the old Ctrl+Shift+Enter array formulas could return several results into a pre-selected range, these new formulas do not require Ctrl+Shift+Enter and you don't have to pre-select the range.

For example, let's say you type =B2:B8*C1 into cell C2. In the past, this formula would have required dollar signs around $C$1. You would copy the formula to all 7 cells and something called implicit intersection would make sure the numbers were correct.

Dollar amounts in B2:B8. A 10% factor in C1. In the screen shot, something completely new: a single formula of =B2:B8*C1 being entered in one cell, C2.

But now, with Dynamic Arrays, one formula in cell C2 will spill over and return results into many cells.

Press Enter and the one formula returns all 7 answers. No one had to remember Ctrl+Shift+Enter.

When you select any cell from C2:C8, a blue outline appears around the cells to let you know that the values are the result of a single formula. That single formula only exists in C2. If you select any other cell in the range, the formula appears in the formula bar, but it is greyed out.

If the range B2:B8 grows (by someone inserting rows in the middle), the spilled results will grow as well. However, simply typing new values in A9:B9 will not cause the formula to extend, unless you format the whole range with Ctrl+T before adding values.

Although the results are showing in C2:C8, the formula only lives in C2. When you select C3, the formula in the formula bar is greyed out.

What if a formula can not spill? What if there are non-empty cells in the way? Rather than return partial results, the formula will return the new #SPILL! error.

If you try to enter that formula in C2 and there is already stuff in the way in C4, you wll get a #SPILL! error in C2.

Open the yellow dropdown to the left of the error and you can select the obstructing cells.

Open the drop-down to the left of the #SPILL! error and the message says Spill Range Isn't Blank. One option in the menu is Select Obstructing Cells.

Once you clear the obstructing cells, the answers will appear again.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by Pierre Bamin on Unsplash