Excel 2019: Dynamic Arrays Can Spill

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.

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

---

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.

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.

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

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

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1