MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Dynamic Arrays Can Spill

September 05, 2019 - by Bill Jelen

Dynamic Arrays Can Spill. Photo Credit: Elyse Turton at

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.

Title Photo: Elyse Turton at

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.