Do you keep experiencing the #SPILL! error in your Excel worksheets on Windows? This error occurs when the formula you have used is unable to populate results into the cells. Now, in this post, we will discuss what exactly this error is, why it is caused, and how you can fix it.
Why does Excel keep saying SPILL?
The #SPILL! error in Microsoft Excel is the error that occurs in worksheets when a formula cannot populate multiple cells with the calculated results. Basically, it arises when a formula is unable to output all the produced results in the sheet. Let us try and understand spilling in Excel in detail.
Spilling is the behavior when Excel formulas produce multiple results that are returned to the neighboring cells. Now, the range of the cells containing these values is called the spill range. Now, if there are some factors that are preventing filling the spill range, you will get a #SPILL! error.
Now, there can be different reasons behind this error. It can be caused when the cells within the spill range contains data or there are merged cells in teh spill range. Besides that, the error could be caused if you have used a table as Excel tables don’t support dynamic array formulas. Other reasons for the #SPILL! error is the fact that the spill range is unrecognizable or is too large.
Based on the scenarios that suits you, follow the below fixes to resolve the error.
How to fix #SPILL! error in Excel?
The #SPILL! error is of different types and is caused in different scenarios. You can apply a suitable fix based on the error message with the #SPILL! you are experiencing. When you click on the Yellow exclamation mark present next to the #SPILL error, you will be able to see the error message and reason on the top. Here are the common #SPILL! error messages you will experience:
- Spill range isn’t blank.
- Spill range has merged cells.
- Spill range in table.
- Spill range is unknown.
- Spill range is too big.
1] Spill range isn’t blank
Spill range isn’t blank is one of the common error messages associated with the #SPILL! error in Excel. This error message is triggered when the range of spill for a spilled array formula is not blank. The fix to this error is to clear any data from the spill range or copy the formula to another column that contains no blockage.
You can simply select the formula cell and you will see the spill range boundaries indicated by a dashed border. The data consists inside the spill range is what causes the error. However, you may see blank cells but they are not. Some invisible characters like a space or an empty string returned by formulas, are causing this error.
To get rid of the #SPILL! error, in this case, you must delete the cells triggering the error. For that, press the warning sign next to the error and click on the Select Obstructing Cells option from the appeared menu options. Excel will now display all the cells causing the obstacle.
Once you know the blocking cells, you can simply clear them up. To do that, go to the Home tab in Excel, and from the Editing group, press the Clear > Clear All option. If you want to move the cele entries to another place, you can use the Cut and Paste method.
If you are getting some other warning message with the #SPILL error, you can move ahead and apply a suitable fix.
Read: Excel can’t insert new cells because it would push non-empty cells.
2] Spill range has merged cells
The next error message is “Spill range has merged cells.” As the message indicates, the cause of #SPILL! error is that there are merged cells within the spill range that don’t work with spilling.
If this scenario is applicable to you, unmerging the cells causing the error is the solution. Here are the steps to do that:
- First, select the warning sign present next to the #SPILL! error.
- Now, from the appeared options, choose the Select Obstructing Cells option.
- You will now be shown the problematic cells that are merged.
- Next, you can unmerge the blocking cells. For that, go to the Home tab and click on the Merge & Center down arrow.
- After that, choose the Unmerge Cells option.
Besides unmerging the problematic cells, you can also move the formula to a column without merged cells.
See: Fix Errors were detected while saving Excel file.
3] Spill range in table
If you get the error message “Spill range in table” with the #SPILL! error, the reason behind it is that the Excel tables do not support dynamic or spilled array formulas. Now, if you want to fix the error in this case, you need to convert the table into a normal range. Or, you can also try placing the formula out of the table to let it spill.
To convert the table to a range, you can press the right-click anywhere within the table. After that, from the appeared context menu, select the Table > Convert to Range option. Or, click inside the table, go to the Table Tools > Design option on the ribbon, and choose the Convert to Range button from the Tools group. This will fix the error for you. Besides that, you can just move the formula out of the table.
Read: Not enough memory to run Microsoft Excel [Fixed].
4] Spill range is unknown
Spill range is unknown is another warning message linked with the #SPILL! error in Microsoft Excel. The primary reason for this error message is that the size of the spill range is unknown by Excel.
Voltauke functions (RAND, TODAY, RANDBETWEEN, etc.) with dynamic functions might throw a #SPILL! error as the “wrapper” function is unable to determine the size and how many values to generate.
So, in that case, the workaround is to change the formula that you are currently using for the task. You can try using a different combination of formulas that will compute the same values without #SPILL! error.
See: Excel cannot open two workbooks with same name at same time.
5] Spill range is too big
If you are getting the Spill range is too big error message, the output exceeds the edges of the worksheet. In that case, you can follow the below approaches to fix the error:
- You can reference only the specific lookup values you need (e.g.,
=VLOOKUP(A2:A7,A:C,2,FALSE)). While this formula type generates a dynamic array, it is incompatible with Excel tables. - You can simply reference the value within the same row and copy the formula downwards. It is a conventional formula style that functions properly in tables. However, it does not generate a dynamic array.
- You can perform the implicit intersection using the “@” operator in your formula and copying it downwards (e.g.,
=VLOOKUP(@A:A,A:C,2,FALSE)). It doesn’t return a dynamic array but works in tables.
Hope these fixes will help you eliminate #SPILL! error from your Excel workbook.
How do I remove duplicates in Excel?
You can delete duplicate entries from an Excel sheet using the dedicated feature provided in Excel. First, open the file from where you want to clear duplicates. Now, click on the Data tab and then press the Remove Duplicates button. A dialog window will open up where you can select a few or all columns from where you can remove all duplicate entries. Finally, you can press the OK button to save changes.
Now read: How to fix the #REF error in Excel?
