How to fix the #SPILL! error in Excel formulas

The first time I encountered the #SPILL! error in Excel, it appeared almost as if the program was reacting dramatically. In reality, it was simply indicating that my formula’s output could not fit into the intended range of cells. After several attempts, some troubleshooting, and research, I learned the key methods to resolve it. In this guide, I will outline the steps I follow whenever Excel displays the #SPILL! error.

Key Takeaways:

• The #SPILL! error occurs when a formula’s output cannot fit into its intended cell range.
• Common causes include non-blank cells, merged cells, insufficient space, or formulas inside tables.
• Clearing the spill range often resolves the issue immediately.
• Excel’s yellow warning icon can help identify the exact cause and obstructing cells.
• Converting tables to ranges allows dynamic array formulas to function properly.

 

Follow this detailed tutorial on #SPILL Excel and download this Excel workbook to practice along and understand better:

 

What does SPILL mean?

Dynamic arrays come with a package full of benefits, but it also includes the user to a new error – SPILL! These formulas produce multiple results and display them to the neighboring cells. SPILL error is caused when a formula with multiple results cannot display its output array, as those cells already contain some data.

A simple solution to this problem is to clear the contents of the cells in the spill range.

 

How to fix #SPILL Excel?

The different scenarios when you can encounter this error are:

• Spill range isn’t blank
• Spill range contains merged cell
• Spill range is too big
• Spill range in a table

Let’s look into more detail and understand the causes and solutions to the #SPILL Excel!

Example 1: Spill range isn’t blank

The spill range where the result will be displayed is non-empty.

In the example, we have used the UNIQUE function to extract a unique customer list in column B. Instead of displaying the result, Excel returns #SPILL! error.

To understand the reason behind this error, click on the small yellow! warning sign next to the error. Upon clicking this sign, Excel tells you the reason – Spill range isn’t blank.

Here, B12 already contains text!

Solution:

Simply, clear any contents from the output range and you are good to go!

There can be times when you get the SPILL error but you are unable to spot the cell that contains unwanted data.

Click on Select Obstructing Cells and it will take you to the cell that is creating the problem.

This is useful when you have hidden spaces and you are unable to find them.

Example 2: Spill range contains merged cells

SPILL error can also occur when the result range contains the merged cells.

In this example, you can see the spill range i.e. B7:B15 is empty but still, Excel returns #SPILL error.

To be sure of the reason, let’s click on the yellow warning sign and see what it says – Spill range has merged cell.

Now, click on Select Obstructing Cells and it will take you there.

Go to Home > Merge & Center to unmerge the cells.

The result is now visible and the error disappears!

Example 3: Spill range is too big

This error occurs when the output range is too big and the result goes beyond Excel’s boundary. There isn’t enough space to display the result.

In this example, we are trying to calculate the discount given to the customers based on the sales amount. Column C is used to get 5% of the sales amount.

As you can see, Excel is again and producing a SPILL error and this time it is because the range is too big.

Here, Excel is trying to multiply each cell in Column B by 5% and produce a million results. It will spill the result in Column C starting from cell C7 but it will reach the end of Excel.

Hence, the error!

To solve this, simply replace the column with the relevant range and copy the formula to the output range.

Example 4: Spill range in a table

Excel tables do not support spilled array formulas. If you try to insert an array formula in an Excel table, it will return a #SPILL error.

In this example, we are trying to sort the amount mentioned in Column A in ascending order using the SORT function. It will return a dynamic array as a result in column B.

Unfortunately, we are getting a #SPILL Excel error.  This is because range A7:B15 is in fact saved as a table in Excel and dynamic functions do not work within Excel tables.

We can easily convert it into a range for our SORT function to work properly.

To do so: Right Click on any cell in the Table range > Select Table > Select Convert to Range.

This will convert the table to range and now all array functions will work perfectly as shown below!

 

FAQs

1. What does the #SPILL! error in Excel mean?

The #SPILL! error appears when a dynamic array formula tries to output multiple results, but the required space is blocked or unavailable. This happens because Excel cannot “spill” the results into the adjacent cells. The blockage could be due to existing data, merged cells, table formatting, or sheet boundaries. Identifying and clearing the obstruction typically resolves the problem.

2. How do I fix a spill range that is not blank?

If Excel indicates that the spill range is not blank, it means at least one cell in the intended output area contains data, spaces, or formatting. The simplest fix is to clear the contents of those cells. You can use the Select Obstructing Cells option from the warning icon to quickly locate hidden spaces or characters. Once cleared, the formula should spill correctly.

3. Why do merged cells cause a #SPILL! error?

Merged cells disrupt Excel’s ability to place multiple results in consecutive cells, as the merged area doesn’t match the required spill structure. When a merged cell is detected in the output range, Excel halts the formula output and shows the error. Unmerging the cells in the spill range will allow the formula to work properly. Always check for merged cells when troubleshooting this error.

4. What happens if the spill range is too large for the sheet?

If the output range extends beyond Excel’s row or column limits, the #SPILL! error will occur because the program cannot display the results. This often happens when entire columns or excessively large ranges are referenced. Reducing the range size or limiting the formula to a specific dataset will resolve the problem. Strategic placement of the formula can also help avoid reaching sheet boundaries.

5. Why can’t spilled formulas work inside Excel tables?

Dynamic array formulas are not supported inside structured Excel tables, as tables enforce a one-formula-per-row structure. When such a formula is entered in a table, Excel produces a #SPILL! error. To use the formula, convert the table to a normal range using Right-click → Table → Convert to Range. This enables full functionality for spilled formulas like SORT, FILTER, and UNIQUE.