SPILL error is a new Excel error that only occurs when a dynamic array formula hasn't had enough place to display the result.

Why #SPILL! error appears in an Excel cell?

With Excel Online or Microsoft 365, you can create Dynamic Array (DA) functions. These functions return the result in as many cells as necessary 😮.

For instance here, you can see that the UNIQUE function returns the result in 6 cells.

So, as you can see, DA functions need empty cells to return the result 😮 So, if the function "detects" that there isn't enough place to return the result, the function will return the #SPILL! error. For instance, in this situation, the function UNIQUE needs 6 cells to return the result but not all cells are empty. So #SPILL!

SPILL with previous array functions

But also now, SPILL can appear with the previous array functions of Excel like TRANSPOSE or FREQUENCY. For instance, we have seen in this article how to unstack your data. In this situation, for the first 3 results, there isn't enough room to display the result. This is why Excel returns the SPILL error one more time.

SPILL with other functions

But in some situations, functions like SUMIF can also return the SPILL error 🤔 The SPILL error can occur with the SUMIF function if you don't write the argument properly. This problem is a common mistake between SUMIF and SUMIFS.

• SUMIF (search range;criterion;range to add)
• SUMIFS (range to add;search range1;criterion1;search range2; ...)

But if you miswrite SUMIF with the arguments of SUMIFS, you make a mistake in the writing of the function. Excel interprets the function as an array formula because the second argument of SUMIF is an array. You can see the problem in the following screenshot.

When you select the first formula, you can see that Excel tries to return the result over many cells. This is because the second argument, in this writing, is a range of cells and not the criterion as expected.