#SPILL! is a new Excel error that appears when a dynamic array formula doesn’t have enough space to display all results. Let's see when this error occurs.

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.

But, if the function "detects" that there isn't enough place to return the result, the function will return the #SPILL! error.

• In this case, the UNIQUE function requires seven empty cells to display its result.
• But there is a formula in Cell
• The expected range is outlined with a blue dotted rectangle.
• Because there is a formula in
• If any of these cells aren't empty, Excel returns a #SPILL! error.

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 error with some Excel functions

However, functions like SUMIF and SUMIFS can also trigger the #SPILL! error under certain conditions. This error often appears if the arguments aren't structured correctly. For example, mixing up SUMIF and SUMIFS arguments is a common mistake that can lead to a #SPILL! error.

• In SUMIF, the correct syntax is: SUMIF(search_range, criterion, sum_range)
• For SUMIFS, it’s: SUMIFS(sum_range, search_range1, criterion1, search_range2, …)

If SUMIF is used with SUMIFS-style arguments (or vice versa), Excel may misinterpret the function, resulting in #SPILL! due to incorrect argument arrangement. For instance, in this situation,

1. The first argument of the SUMIF function is the sum_range. But SUMIF expects the search_range
2. But the real problem is the second argument. In this situation, the second argument is a range and not a single value like expected.
3. For the first formula, there is no error

But when you copy the formula for the other name of column E, you have SPILL error for all the previous cells. It's because, the first formula doesn't have enough cells to return the result.