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 formula...
In the above example, we have a merged cell for the range B2:C3 and when we try to enter RANDARRAY in cell A1, it returns the spill error in cell A1 as it can’t re-write a result in a merged cell. Selecting Obstructing Cells to Fix the #SPILL! Error When you click on the cel...
Non-empty cells in the spill range:Spill formulas require an empty space to display results. If cells within the spill range are occupied, it can lead to the #SPILL error. Merged cells:Merging cells within the spill range can disrupt the spill formula's functionality, causing unexpected result...
2. Spill Range has a Merged Cells A user may encounter the #spill error in Excel if the Spill Range contains merged cells. Here, unmerging the merged cells in the Spill Range may clear the #spill error. Click on theformula cellto highlight theSpill Range. Now try to findmerged cellsan...
Hello, I am working on a client project and am having trouble getting my formula to work due to a merged cells error. So I unmerged the cells which I don't want to do for my form template and then I got a spillover error which I don't know how to fix. my formula ...
Case 2 – Merged Cells in Spill Range Suppose, we use theUNIQUEfunction in cellF5, to get the unique values from cell rangeD5:D9and get the error because theSpill range has merged cell. We type the formula at the cell. The formula we are using is: ...
What Causes the #SPILL Error? 6 Methods for Fixing the #SPILL Error in Excel 1. When the #SPILL Range Isn’t Blank 2. When the #SPILL Range Is in the Table 3. When the #SPILL Range Is Too Large 4. When the #SPILL Range Has Merged Cells 5. When the #SPILL Range is Unknown Fr...
6. Spill Into Merged Cells Do you have merged cells in your Excel spreadsheet? If yes, spilled array formula cannot spill successfully into merged cells. To solve this spill error in Excel, you need to unmerge the cells. If that is not possible then move the formula to a different cell ...
To clear blocking cells, go to theHometab >Editinggroup, and clickClear>Clear All. Spill range contains merged cells Reason: Spilling does not work with merged cells. Solution: Unmerge cells in the spilled area or move the formula to another location that has no merged cells. ...
Step 7: Format Cells window opens. Go to theNumbertab Step 8: From the left-hand pane, selectCustom Step 9: From the right-hand side pane, change the Type from ; ; ; toGeneral Step 10: Click on theOkbutton Fix to be followed when Spill range has merged cells ...