You mostly encounter the #SPILL! error when you use dynamic arrays in MS Excel. When you use a dynamic array in Excel, it senses a pattern and fills the neighboring cells accordingly. Although an amazing feature, it is very easy to get the #SPILL! error when the feature is incorrectly used.
It can take quite a while, especially for new users, to understand the reason behind the #SPILL! error. This article will assist you if you’re in a similar situation. Keep reading to fix the #SPILL! error in Excel so you can avoid it the next time.
How Do You Get the #SPILL! Error?
The #SPILL! error is exclusive to Office 365 version. You get this error when the formula you entered returns multiple values that Excel cannot fit in its grid. Depending on the data you’re working with, there can be many reasons for encountering this issue.
Below, we’ve listed the causes for the #SPILL! error in MS Excel:
- Blockage in Spill Range
- Varying Spill Range
- Value Exceeds Excel Grid
- Array Spilled in Table
- Large Array Size
- Spill Cell Merged
Fix #SPILL! Error in Excel
The solutions to the #SPILL! error depends on the issue you might be facing. After you skim through the list of causes, you can move on to the relevant fixes from the solutions we’ve discussed below.
Remove the Blockage
To spill an array, you need to have certain cells empty. For example, if your array contains five cell items, five cells that fall under the spill range must be empty. If any cell in between contains data, Excel will display the #SPILL! error.
For your value to spill, you must remove the blockage in the range. If you do not see anything in between, your cell must include either space or a hidden character. In that case, you’ll need to use the Sort & Filter tool.
Through this feature, you can inspect the cells under a row and delete them. Here are the steps you can follow to remove the blockage using the Sort & Filter tool:
- On your workbook, locate the Sort & Fill option from the Home ribbon.
- From the dropped-down menu, select Filter.
- An arrow will appear on the top of the column; select it.
- From the window, deselect Select all.
- Check the box next to (Blanks) or what seems to be nothing. Select OK.
- Select the empty row and hit the Delete key on your keyboard.
- Click on the Sort & Fill option again, then select Clear.
Convert Table to Range
Although sorting your data in tables might make it easier to view your data, it does not support the spill feature. If you try spilling your array in Excel tables, you’ll be met with the #SPILL! error.
You can keep the look of a table and still spill your formula. For this, you will have to convert the table to a range. Follow these steps to change your table to a range in Excel:
- Open your workbook.
- Select any cell inside the table.
- A new tab named Table Design will appear on the menu bar; select it.
- In the Tool section, click on the Convert to Range option.
- Validate your action by choosing Yes on the confirmation box.
You will now be able to spill your value into the corresponding cells.
The values won’t spill if a cell in the spill range is merged. The merged cells act as an interference in the range. This then triggers Excel to display the #SPILL! error to notify users that there’s been an error spilling values to the cell.
If you have any cells merged in the spill range, you must split them. Separating cells is just as easy as merging them. Follow these steps to unmerge cells in Excel:
- Launch Excel to open your spreadsheet.
- Locate the merged cell in the sheet and select it.
- From the Home ribbon, click on Merge & Center from the Alignment section.
- Select Unmerge Cells.
NOTE: When you split your merged cells, the value from the cell shifts to the left cell
Avoid Volatile Functions
Excel won’t spill value if it cannot determine the array size. Some array functions such as RAND(), NOW(), and TODAY() are extremely volatile with uncertain spill ranges. Additionally, some arrays return values that exceed the Excel grid. When the range isn’t specified or exceeds the grid, Excel will return the #SPILL! error.
Your only way to solve this issue is to avoid spilling dynamic array. Swap the function with less volatile Excel functions or smaller arrays if you can.
Spill to a Different Cell
You cannot reference an entire column to a cell other than the one cell first in a row. Excel will try to fit every element, including blank cells from the referenced column to the range under the cell with the formula. Excel cannot fit the results in its grid as it does not have enough cells to fit each element.
You can easily solve this issue by changing the cell to enter your formula. If you want to spill value from an entire column, use the first cell in a row to enter your formula. This will allow Excel to fit every element in its grid.