How to Fix Excel #SPILL! Errors in WPS Office Spreadsheets
Question details
The user needs to resolve #SPILL! errors in Excel when a dynamic-array formula threshold is changed and the intended spill range is blocked by occupied cells, merged cells, or a table.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user changed the threshold in an array formula and the dynamic-array result cannot spill because the output range is not empty.
- Observed behavior
- The #SPILL! error persists after changing the threshold because cells in the intended spill range remain occupied, merged, or part of a table.
Before you edit the formula, identify the formula cell and the intended spill range. Check whether that spill path contains data, merged cells, or an Excel table, because these are the main blockers for a dynamic-array formula.
Clear the Blocked Spill Range and Remove Merge or Table Blockers
Use this first when #SPILL! appears after changing an array formula threshold, because a non-empty spill range is the most common cause.
The #SPILL! error does not mean the formula is invalid; it means Excel or WPS Office cannot place the resulting array into the worksheet because part of the spill path is not empty. Clearing or unblocking that range lets the formula spill normally.
Click the cell that shows #SPILL!, then click the floating error icon next to it and choose Select Obstructing Cells. WPS Office Spreadsheets highlights the cells that are blocking the spill range.
With the blocking cells selected, press Delete, or right-click and choose Clear Contents. If any highlighted cell is merged, select it and click Home > Merge & Center to unmerge before clearing.
If the highlighted spill path overlaps an Excel table, move the formula to a location outside the table, or click Table Design > Convert to Range to turn the table into a normal range.
Click the formula cell, press F2, and press Enter so WPS Office Spreadsheets recalculates and spills the dynamic-array result into the now-empty range.
Use a Single-Cell Array Formula to Avoid Spilling
Use this when you only need one threshold result, so the formula does not need to spill into a range.
Fix #SPILL! Errors in WPS Office Spreadsheets
Open the affected workbook in WPS Office Spreadsheets to clear the blocked spill range, unmerge cells, remove table obstacles, and re-enter the array formula. WPS Office keeps your .xlsx file compatible with Excel while offering a free and lightweight spreadsheet editing experience.
- 1. Open the Excel workbook in WPS Office Spreadsheets: Launch WPS Office, click Home > Sheets, then open the .xlsx file containing the #SPILL! error.
- 2. Select the formula cell and show blocking cells: Click the cell with #SPILL!, click the error icon, and choose Select Obstructing Cells to highlight the occupied spill path.
- 3. Clear contents and unmerge cells: Press Delete or right-click > Clear Contents, and use Home > Merge & Center to turn off merged cells inside the highlighted range.
- 4. Re-enter the formula: Press F2 on the formula cell and press Enter to recalculate. For older compatibility, use Ctrl+Shift+Enter if your version requires legacy array confirmation.
Frequently Asked Questions
Why does #SPILL! still appear after I change the threshold in my array formula?
Because the dynamic-array formula is still trying to place results into a range that is blocked. Select the formula cell, click the error icon, choose Select Obstructing Cells, and clear the highlighted cells. Also check for merged cells or a table in the spill path.
How do I clear the cells that block an Excel spill range?
Click the #SPILL! cell, use the error icon > Select Obstructing Cells, then press Delete or right-click and choose Clear Contents. If merged cells are present, unmerge them through Home > Merge & Center first.
Can I avoid #SPILL! by using a single-cell array formula?
Yes. For a one-row threshold result, use =IF(MIN(AF6:AQ6)<2.5,0,MIN(IF(AF6:AQ6>=2.5,AF6:AQ6,10^99))) or =MIN(IF(AF6:AQ6>2.5,AF6:AQ6)). In older Excel versions, confirm with Ctrl+Shift+Enter.
Will WPS Office keep my dynamic-array formulas compatible with Excel?
WPS Office Spreadsheets supports Excel .xlsx files and common array formulas. When you save the workbook, you can keep the .xlsx format so it remains compatible with Microsoft Excel.
What should I do if the spill range overlaps an Excel table?
Move the formula outside the table area, or convert the table to a normal range via Table Design > Convert to Range before entering the dynamic-array formula.
