'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel #SPILL! Errors Caused by Merged Cells
Question details
The user needs to fix a #SPILL! error in Excel that occurs when a dynamic array formula interacts with merged cells.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using dynamic array formulas in Microsoft 365 that either reference a merged cell range or attempt to output data into an area containing merged cells.
- Observed behavior
- Excel displays a #SPILL! error because dynamic array formulas treat referenced merged cells as an entire range and cannot spill their output into merged cells.
Click the cell displaying the #SPILL! error to reveal a dashed blue border indicating the intended spill range, so you can easily spot any merged cells blocking the formula.
Unmerge Cells in the Spill Range
Since dynamic arrays cannot spill into merged cells, removing the cell merges in the formula's output area is the most direct way to resolve the error.
A dynamic array formula needs a clear, standard grid of single cells to display its multiple results. If even one merged cell overlaps this required area, Excel blocks the entire calculation to prevent data overwriting and returns the #SPILL! error.
Highlight the merged cells that are located within the formula's intended spill range (indicated by the dashed border).
Navigate to the 'Home' tab on the Excel ribbon, click the drop-down arrow next to 'Merge & Center', and select 'Unmerge Cells'.
Ensure that the newly unmerged cells are empty so the formula has enough blank space to spill its results. The formula will automatically recalculate once the space is clear.
Reference the Top-Left Cell of a Merged Range
If your formula triggers an error because it pulls data from a merged cell rather than outputting to one, you can fix the reference by pointing only to the top-left cell.
Duplicate the Worksheet Layout
If your worksheets have identical structures and removing merges breaks your layout, simply copying the entire worksheet may be more practical than building complex formulas.
Easily Handle Dynamic Arrays and Formula Errors in WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas and provides intuitive tools to help you identify and resolve formula spill conflicts. Easily unmerge cells and calculate complex data in a familiar, user-friendly environment.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the file containing the formula errors.
- 2. Locate the spill error: Select the cell showing the error. WPS Spreadsheet will outline the expected spill range so you can identify obstructions.
- 3. Unmerge blocking cells: Highlight any merged cells in the output area, go to the Home tab, and click 'Merge and Center' to unmerge them.
- 4. Recalculate automatically: Once the merged cells are removed, your dynamic array formula will automatically update and display the correct values.
Frequently Asked Questions
What does a #SPILL! error mean in Excel?
A #SPILL! error occurs when a dynamic array formula attempts to return multiple results, but the required blank output range (spill range) is blocked by existing data, merged cells, or Excel tables.
Can I force a dynamic array formula to spill into merged cells?
No. Dynamic array formulas require a standard grid of individual cells to output multiple values. They cannot spill into any range that contains merged cells. You must unmerge the cells for the formula to function.
How do I quickly find the merged cells causing the #SPILL! error?
Click on the cell displaying the #SPILL! error. A small warning icon will appear next to it. Click the icon and select 'Select Obstructing Cells' from the menu to instantly highlight the merged cells or data blocking the formula.
Why does referencing a merged cell cause a #SPILL! error?
Excel treats a merged cell range (e.g., A1:B1) as an array rather than a single cell. When a dynamic formula references this array, it attempts to process multiple cells instead of one, which can trigger the error. Referencing only the top-left cell of the merged area resolves this.
