Spill-Error-In-Excel-Someka-Blog-Featured-Image

How to fix SPILL Error in Excel? Easy and Clear Steps

Excel Tips
Updated on May 16, 2024

Do you keep getting #SPILL! errors in your worksheets? Here’s a step-by-step guide on SPILL Error in Excel? What are the potential reasons behind this error and how to fix it?

Table Of Content

1. Why do you get #SPILL! Errors?
2. How to fix Spill Error in Excel?
3. What kinds of formulas can lead to SPILL errors?
4. Conclusion

Over the years, Excel has changed a lot, adding powerful new features that make users more productive. One of these features is dynamic arrays, which let formulas return more than one result by spilling them into cells next to each other. So, it’s helpful to have this feature. However, it can also cause the #SPILL! error.

Thus, this guide will help you figure out what spill errors are, how to correct them, and which formulas are most likely to make them happen.

1. Why do you get SPILL Error in Excel?

When Excel cannot execute an array formula, it will give you a SPILL Warning. But why cannot Excel execute this array formulas.

These are some common reasons why spills happen:

Existing Data: If any cell in the intended spill range already contains data, Excel will return a #SPILL! error.
Merged Cells: Merged cells can obstruct the spill range, causing the error.
Non-Blank Cells: Even invisible characters or spaces in the cells within the spill range can trigger a spill error.
Table Overlap: Spilling into a structured table is not allowed, which leads to this error.
Formula Overlap: If another array formula or a spilled array formula already occupies the cells in the spill range, you’ll see a #SPILL! error.

When an Excel formula tries to return multiple values (spill) into neighboring cells but one or more of those cells already have data in them, are part of another array, or are blocked in some other way, it gives the #SPILL! error. The error message points out a blocked spill range, which means that Excel can’t do what it’s supposed to do because of these blocks.

Let’s see some examples for the above reasons.

Suppose we have a list of employees with departments. And we want to create a dynamic filter according to the selected department. So, we can easily create this list with FILTER() function.

Existing Data Issue

But, in the below image, we are getting an error because we have an existing data in the spill range.

Merged Cell Issue

Below is another error caused by a merged cell. So, merged cells can prevent many functionalities in excel, including but not limited to dynamic array formulas.

You cannot use dynamic array formulas on the merged cells.

TIP: We do not recommend merged cells as long as possible in Excel. Check out our Merged Cell Guide to learn why you should avoid them and what we can use instead.

Invisible Text Issue

Also, be careful about the invisible characters or spaces in the cells. So, a cell might seem empty, but i can have some spaces or the color of the background and the font might be the same.

Table Designs

We cannot also use array formulas in the Table structures in Excel.

For instance, there’s a employee list below in Table format, and we want to add random numbers for our 50 employees from 100 to 1000. We can use RANDARRAY () function for this. But, as our list is in Table format we cannot execute this formula.

Overlapping Spills

Another reason might be the overlapping spills. So, the below is a very simple example of this case.

If you’re working with big data sets, sometimes it can be hard to oversee any possible overlapping formulas.

*PRO TIP: Another two reasons behind the SPILL errors might be too extensive or unknown spill ranges. In those case, check our your formulas and try to narrow or define the spill range.

In the below image, the C:C is a too big range that Excel can execute your formula. Therefore, you can narrow your range with row numbers like C4:C11, then this will make your formula correct.

So, these are the main reasons behind #SPILL errors on your spreadsheet. Now let’s see how to fix them.

2. How to fix Spill Error in Excel?

Once you understand the main reasons behind the #SPILL! errors, you can easily troubleshoot and correct them.

Step 1: Identify the Obstruction

Firstly, you should find out the reason behind your error. If you’re not sure about the reason, go to Formulas > Error Check from the ribbon. And, this will open the error checking window, where you can easily see your error’s reason.

As you see, the above error is because of the range including an existing data.

Step 2: Clear Obstructions behind the Spill Error in Excel

Once you know the reason, you can easily solve it.

Existing Data: Clean all the range from data
Merged Cells: Unmerge any cells in the spill range that have been merged.
Non-Blank Cells: Get rid of any characters or spaces that can’t be seen.

TIP: You can make sure that all of the cells are blank by clicking on Clear All in the Editing group on the Home tab.

Table Overlap: Either move your array formula to a different table or change the table type to a range.

TIP: Pick out a cell in the table, click on the Table Design tab, and then click Convert to Range. This will turn the table into a range.

Formula Overlap: Look for other array formulas that are in the same range and move them around to fit.
Too extensive Spill range: Narrow your spill range by limiting the row and column numbers on your formula references.

Another important issue is to understand the range of the result of your array formula. Here Excel is also helping you.

The blue dots will show you the potential result area of your formula. So, you can check this particular range to look for the reasons.

Step 3: Type in the Formula again

Lastly, after clearing the obstructions, re-enter the dynamic array formula. So, Excel should now spill the results into the adjacent cells without issues.

3. What kinds of formulas can lead to SPILL errors?

A number of formulas are made to return more than one value, and if the target range is blocked, they can lead to spill errors.

We’ll list some of the most common formulas that can cause SPILL errors.

XLOOKUP
SEQUENCE
UNIQUE
SORT
FILTER
RANDARRAY
TEXTJOIN
XMATCH
TRANSPOSE
And more…

As a final note, Excel 2016 and 2019 do not support dynamic array formulas. But for the newer versions Excel has introduced these dynamic array formulas, which also bring the risk of SPILL Error.

4. Conclusion

Finally, the introduction of powerful dynamic arrays of Excel has brought many useful functions to our lives, such as XLOOKUP, TRANSPOSE, FILTER, SORT, etc. But also the #SPILL! errors have just jumped into our worksheets.

While it may seem daunting at first, understanding the root causes and implementing the steps to resolve these errors can significantly enhance your efficiency. We have listed all possible and common reasons behind this error. So now you can identify your error reason, get rid of that obstacle and re-enter your formula. That’s all about the SPILL Error in Excel.

Hope you enjoy our article!

If you want to practice your Excel formulas, we have a very unique tool, which is also in Excel!