Fix Excel #SPILL Errors in WPS Spreadsheets When Matching Data Across Sheets
Question details
The user needs to resolve #SPILL! errors that appear when populating temperature columns from a second worksheet by using plot numbers to match data across sheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Matching temperature data between worksheets by plot numbers.
- Observed behavior
- The formula returns a #SPILL! error because the dynamic array result cannot expand into the intended cells or references an oversized range.
Open the workbook in WPS Spreadsheets and select the #SPILL! cell to see the dotted spill outline. Keep the second worksheet visible so you can confirm the lookup column and the actual data range.
Clear the Blocked Spill Range and Limit the Lookup Reference
Use this solution when the dotted spill outline shows blocked cells or when the formula references entire columns and creates an unnecessarily large array.
A #SPILL! error usually means the formula is returning an array that cannot expand because destination cells are not empty or because the reference range is too large for the available grid. WPS Spreadsheets follows the same spill behavior as Excel for dynamic array formulas.
Click the cell that displays #SPILL! in WPS Spreadsheets. A dotted border appears around the intended spill range.
Inspect every cell inside the dotted outline. Press Delete to remove values, or select Home > Merge & Center to unmerge any merged cells that occupy the spill range.
Click the formula bar, replace entire-column references such as Sheet2!A:A with the actual data range, for example Sheet2!$A$2:$A$45001.
Press Enter and verify that the temperature values fill the intended rows without the #SPILL! error.
Rewrite the Formula to Return One Result per Row
Use this solution when you do not need a dynamic array and want one temperature value per plot number row.
Fix #SPILL! Errors in WPS Spreadsheets When Matching Cross-Sheet Data
Open the affected Excel workbook in WPS Spreadsheets, inspect the spill outline, clear blocked cells, and replace entire-column references with the actual data range so temperature values populate correctly.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the affected .xlsx workbook, and go to the worksheet with the #SPILL! error.
- 2. Select the error cell and inspect the spill outline: Click the #SPILL! cell; WPS Spreadsheets shows the spill range boundary so you can see which cells are blocked.
- 3. Clear values, merged cells, or stray data in the spill range: Delete any entries or unmerge cells inside the outlined range so the dynamic array can populate.
- 4. Adjust the formula reference: In the formula bar, replace entire-column references with the actual data range such as $A$2:$A$45001, then press Enter.
- 5. Verify temperature columns fill correctly: Check that the matched temperature values populate across the intended rows without #SPILL!.
Frequently Asked Questions
Why does a #SPILL! error appear when I match data across sheets?
It appears when a dynamic array formula tries to return multiple values but something is already in the spill range, or when an entire-column reference creates too large an array. Select the error cell to see the outlined spill range and clear blocking cells.
How do I check which cells are blocking the #SPILL! range?
Click the #SPILL! cell. A dotted or blue border outlines the intended spill area; any non-empty cell or merged cell inside that border is blocking the result.
Can I stop the formula from spilling and just fill one temperature value per row?
Yes. Replace a dynamic array lookup with a single-row lookup such as INDEX/MATCH or XLOOKUP and copy it down, or structure the formula to return only the value for the current row.
Does WPS Spreadsheets support Excel dynamic array formulas that cause #SPILL!?
WPS Spreadsheets supports common Excel functions and .xlsx files, so you can open the workbook and inspect, edit, or simplify the formula to avoid spill conflicts.
