You may encounter the #SPILL! error in Excel when working with spilled array formulas. This error usually occurs when a formula generates multiple results and the Excel application failed to return those results to the grid. It can also occur if you accidentally apply a formula that is meant for a single cell to multiple cells. However, there are various other causes behind this error.
This post will outline the causes behind the #SPILL! error and the methods to fix this error. It also mentioned about Excel file repair tool to fix the error if it occurs due to corruption in the Excel worksheet.
Causes of the Spill Error in Excel
Here are some possible causes of the #SPILL! error in Excel:
- Missing arguments in the formula.
- When you use spilled array formulas in merged cells.
- Excel runs out of memory.
- When you use spilled array formulas in Excel tables.
- When the spill range isn’t empty.
- Incompatible datatype in the formulas.
- Lengthy formula.
- When you select an invalid range in the formula.
- Corruption in Excel file.
Methods to Fix #SPILL! Error in Excel
You can try the below-mentioned methods to resolve the #SPILL! error in Excel.
Method 1: Check and Clear the Values in the Cell
Let’s say, you are using a dynamic function that is supposed to return multiple values in the range of the cells, but that range isn’t blank. The dynamic function can’t overwrite the values. Instead, the application throws the error. To fix this issue, you need to clear the values in the cells.
Method 2: Use Different Functions
Sometimes, the #SPILL! error can occur if there is an issue with the size of the spilled array. This issue mainly occurs when you use the functions, such as RAND, RANDARRAY, RANDBETWEEN, etc. To fix this, you can use other volatile functions, such as TODAY(),=INDIRECT(A4), etc. These functions change each time Excel performs calculations.
Method 3: Convert the Excel Table
You may get the #SPILL! error if you are applying dynamic array function on Excel tables. The tables in Excel are not compatible with dynamic arrays. In such a case, you can change the table to a normal range. Follow the below steps to convert an Excel table to range:
- Open the Excel file in which you are getting the error.
- Go to the table and click anywhere in the table.
- Next, go to Tools on the Ribbon section and click Design.
- Right-click on the table, click Table, and then click Convert to Range.
Method 4: Break the Formula or Decrease Data Size
Sometimes, the #SPILL! error can occur when the Excel application runs out of memory. It usually happens when you use spilled array formula for large data set. The spilled array formula refers multiple cells, which results in the need for more memory. To fix this issue, you can break the formula into smaller parts to simplify the calculations or decrease the size of data range of the cell that the formula is referring.
Method 5: Unmerge the Cells in the Sheet
The #SPILL! error can occur if you try to apply spilled array formula to a spilled area having merged cells. To fix this, you can unmerge the cells or change the formula to another range that does not have any merged cells.
Method 6: Repair Your Excel Sheet
The #SPILL! error in Excel can also occur if the formula or other data gets corrupted. In such a case, you can use the “Open and Repair” tool – an inbuilt tool in Excel provided by Microsoft to fix corruption issues in Excel file. To repair the Excel file using Open and Repair, follow these steps:
- Open your Excel.
- Go to File and then click Open.
- Go to the location of the corrupted Excel sheet and select it.
- Next, click on the arrow that appears next to the Open
- Click the Open and Repair
- Click Repair.
Sometimes, the Open and Repair tool fails to repair the Excel file if it is severely corrupted. In such a case, you can use an Excel recovery software, such as Stellar Repair for Excel. It is a specialized tool to repair severely corrupted Excel files. It can recover all the data from the file without changing the original formatting. The software supports files created in Excel 2019, 2016, and earlier versions.
Closure
The #SPILL! error can appear if the range of cells associated with the formula or function in Excel is not empty. There are many other reasons associated with the error. Try the methods discussed above to fix the #SPILL! error in Excel.
If it occurs due to corruption in Excel file, then you can use the Open and Repair tool or a reliable Excel recovery software, such as Stellar Repair for Excel. The software can easily repair severely corrupt Excel files (.xls, .xlsx) and restore all the data. It can help you fix all types of error that occurs due to corruption in Excel file. It is compatible with Windows 11, 10, 8.1, 8, and 7.