This error is caused when you improperly implement the concept of spilling an array in your Excel formula. This post explores this spilling concept and discusses the fixes for errors associated with spilling formulas.

What Does Spill Mean in Excel?

Excel was introduced to spilling arrays with the release of Excel 365 in January 2020. The basics of spilling are that you enter a single formula in a cell and the results are returned in multiple cells! You are effectively telling your formula result to spill over to the adjoining cells. You can take advantage of this behavior by including a range in your formula like the above example. Such a formula is called a spilled array formula. It multiplies cell A1 by 10 and displays the result in the formula cell. It also multiplies cell A2 by 10 and displays that result in the adjoining cell beneath the formula cell!

What Causes the Spill Error in Excel?

You’ll receive a #SPLILL! error when Excel can’t use enough cells to show all of your formula results. This is usually because the spill is being blocked by other sheet content. It’s also possible that your formula breaches the system limits of Excel or the feature limits of spilling.

How to Fix the Spill Error in Excel? [Causes and Solutions]

How you fix a spill error depends on the root cause of the error. You can get more information about the root cause by selecting the error icon on your #SPILL! error cell. You’ll explore these possible causes of a spill error:

Spill Range isn’t BlankSpill Inside TablesSpill Range Contains Merged CellsSpill Range is Unknown

Spill Range isn’t Blank

The spill range is the range of cells that will show your formula result. If you already have content in any of those cells, your formula cannot overwrite that content and you’ll receive the Spill range isn’t blank error. You can fix this by moving your spill formula further away from your existing content so that there are enough empty cells to finish spilling. You could also free up the spill range by moving out your content or deleting it if it’s irrelevant.

Spill Inside Tables

You’ll always get a spill error if you attempt to spill within a table. Excel tables do not support spill ranges since tables have their own unique syntax. The best workaround is to convert your table to a range or move your formula out of the table altogether. Select the table and go to the Table Design tab and click on the Convert to Range button to remove the table from Excel without removing the data.

Spill Range Contains Merged Cells

A merged cell is another feature of Excel that prevents a formula from spilling. The way to fix this is to either unmerge the obstructive merged cell or move the formula so that its spill range excludes the merged cell.

Spill Range is Unknown

There are some formulas that cause a spill error because Excel cannot determine how big or small the spill range will be. The above formula attempts to generate a sequenced column of varying row size. The SEQUENCE function is called a dynamic array formula function because it always returns a spill range. The problem is that its spilling gets interrupted by the constant recalculation of the volatile RANDBETWEEN function. An error occurs because the spill range size of SEQUENCE cannot be determined. The only solution is to avoid using volatile functions in a way that affects the spill range size. In this example, you might want to calculate RANDBETWEEN one time in another cell (B2) and then paste that value into the SEQUENCE function.

Spill Range is Too Big

Excel can handle up to 1,048,576 rows and up to 16,384 columns per sheet. That means any spill range exceeding that will cause a Spill range is too big error. A common example is when you attempt to create a spilled array formula in row 2 (or higher) and have it reference an entire column like the above. The A:A column consists of a maximum of 1,048,576 rows. That means the spill range is also 1,048,576 rows. Because your formula is in row 2, the spill range exceeds the Excel row limit by 1 and the error occurs. You can prevent the error by moving the formula to row 1. A more performant fix would be to change the formula to return a single value like the above example and then copy down the formula in the traditional way.

Find All Spill Errors in a Workbook

There are several ways to find spill errors in your workbook. You can use general Excel features like the Find menu or the Go To menu for a basic search. There is also an Error Checking menu built specifically for error auditing. You can even use VBA code or Office Scripts to do your search.

Find Menu

Finding a spill error with the Find menu is much like searching for any other value. Select any row in the results to go directly to that cell!

Go To Menu

The Go To menu has been part of Excel for quite some time. You can leverage a special type of Go To to find only errors like spill errors. You now see all your errors selected on your sheet!

Error Checking Menu

Excel has a dedicated menu for error checking.

VBA

VBA is the programming language for the Desktop versions of Excel. VBA code includes syntax that can search and detect errors specific to spilling: The code loops through every non-empty cell in every sheet. IsError determines if the cell contains an error and those errors are checked against the #SPILL error CVErr(xlErrSpill). The spill error address is output to the Immediate Window using Debug.Print. Look in the Immediate Window for the addresses of your spill errors!

Office Scripts

Office Scripts offers you automation capability in Excel for the web if you have a Microsoft 365 Business Plan. Searching for spill errors in your workbook using Office Scripts is possible: The script loops through every cell on the active sheet. If it finds the #SPILL! error value, it uses console.log to output the cell address to the Output window. Notice how the Output window at the bottom of the Code Editor shows your spill error locations!

How do I turn off spill in Excel?

The spilling behavior is a good feature, but you may sometimes want to revert to the traditional way of calculating formulas. You can turn off spilling in a formula by including the implicit intersection operator (@) like the above example. Including the @ operator tells Excel to calculate the formula in the traditional way without spilling. There is a function equivalent called SINGLE. But this function is automatically converted to the @ operator in your formulas.

Conclusions

You’ve seen how spilling can help you create a single formula to have a widespread impact on your sheet. This power comes with the drawbacks of all the spill errors you might encounter. The most important thing to keep in mind is to keep your spill ranges free of content that may block your spilling. You’ve also seen techniques that allow you to find any spill errors you encounter and have some options to fix them. You can opt out of spilling but it would be a shame to not leverage the power of this new way of formula calculation! Have you come across the #SPILL! error in Excel yet? How did you handle the error? Let me know in the comments!