Have you ever entered your Excel formula and been met with a #SPILL! error?

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!

= A1:A2 * 10

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 Blank
• Spill Inside Tables
• Spill Range Contains Merged Cells
• Spill 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.

= SEQUENCE( RANDBETWEEN( 1 , 10 ) )

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:A * 10

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.

= A2 * 10

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.

1. Go to the Home tab and drop down Find & Select.

2. Select Find or use the shortcut key Ctrl + F.

3. Type #SPILL! in the Find what field.
4. Select Workbook in the Within dropdown.
5. Select the Values from the Look in dropdown.
6. Click Find All and your results will be listed in the lower portion of the same window.

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.

1. Go to the Home tab and click on the Find & Select dropdown.

2. Select Go To Special option or use the shortcut key sequence Ctrl + G followed by Alt + S to navigate to the Go To Special menu.

3. Select Formulas and uncheck every box except Errors.
4. Click the OK button.

You now see all your errors selected on your sheet!

Error Checking Menu

Excel has a dedicated menu for error checking.

1. Under the Formulas tab select Error Checking.

2. Click the Next or Previous button until you find your spill error!

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:

1. Open the VBA Editor by going to the Developer tab and selecting Visual Basic or simply press Alt + F11. You may first need to enable the Developer tab since it is hidden by default.

2. Go to the Insert menu and select Module.

Sub FindSpillErrors()

Dim ws
Dim cell

For Each ws In ThisWorkbook.Worksheets
    For Each cell In ws.UsedRange
        If IsError(cell.Value) Then
            If cell.Value = CVErr(xlErrSpill) Then
                Debug.Print ws.Name & "!" & cell.Address
            End If
        End If
    Next
Next

End Sub

3. Double-click the new module and paste the above VBA code into the module.

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.

4. Ensure the Immediate Window is visible by going to the View menu and selecting the Immediate Window option.

5. Click anywhere in your code to ensure that it will be the macro that runs.
6. Run your code by clicking Play or pressing F5.

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:

1. Go to your browser and open your workbook in Excel for the web.

2. Under the Automate tab select New Script. Ensure that you have a Microsoft 365 Business Plan if you don’t see the Automate tab.

function main(workbook: ExcelScript.Workbook) {
    let range = workbook.getActiveWorksheet().getUsedRange();
    let rowCount = range.getRowCount();
    let colCount = range.getColumnCount();

    for (let row = 0; row < rowCount; row++) {
        for (let col = 0; col < colCount; col++) {
            let cell = range.getCell(row, col);
            let value = cell.getValue();
            if (value=="#SPILL!"){
                let address = cell.getAddress();
                console.log(address);
            }
        };
    };
}

3. Paste the above script into the Code Editor pane.

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.

4. Run your script by clicking the Run button.

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.

=@ A:A * 10

You can turn off spilling in a formula by including the implicit intersection operator (@) like the above example.

=SINGLE(A:A) * 10

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!