With the introduction of dynamic arrays comes a new type of error; the spill error.
Other errors, such as #N/A, #NUM! and #REF! have existed in Excel for many years. In many cases, workarounds for these errors have been created in order to mitigate seeing these errors.
The spill error only occurs with specific new formulas – dynamic array formulas.
What are dynamic array formulas?
A dynamic array formula is one where the result of the formula can “spill” into multiple cells.
An example of this is the SEQUENCE() formula, which creates a sequence of numbers in different cells.
In the example below, the formula is entered into cell A2, but the results of the formula appear in cells A3 to A11.
Should you try to refer to the formula in cell A3, you will get an error, as there is no formula in this cell. However; you can use other formulas to refer to the values that are created by the dynamic array formula.
Some other dynamic array formulas include the SORT() and UNIQUE() functions. However; other formulas such as XLOOKUP() can also return a dynamic array.
In the case above, one XLOOKUP formula works properly, but the other has a #SPILL! error.
What causes a spill error?
A spill error occurs when a dynamic array is blocked from being entered into a cell by an existing value or formula.
In the example below, the SEQUENCE() formula has been added in cell A1. This returns the numbers from 1 to 10 in cells A1 through A10.
However; when the word BLOCKED is entered into cell D5, it causes the formula in D1 to return the #SPILL error.
Since the dynamic array formula cannot fill all the results from 1 to 10, it returns the #SPILL error. If the entered text is deleted or moved, the SEQUENCE formula will fill expected.
When importing data, you need to be especially careful. Non-breaking, invisible spaces can take up space in a cell. These cases will still cause a SPILL error to be triggered!
Conclusion
Dynamic arrays are one of the most significant new features to be added to Excel in the last 5 years. This feature eliminates the traditional method of using Control-Shift-Enter to enter array formulas.
As more users start to use these new formulas in their spreadsheets, this new #SPILL error will be seen frequently, but resolved very quickly!