How to Fix an IF Formula Returning #SPILL! Error in WPS Office Spreadsheets
Question details
The user needs to resolve a #SPILL! error produced by an IF formula that uses FIND and LOWER to search a table column for the word consulting.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with an Excel table column and wants to check whether a product name contains the word consulting, but the formula returns an array result instead of a single-cell result.
- Observed behavior
- The formula returns a #SPILL! error because a structured reference to an entire table column generates multiple TRUE/FALSE values that cannot fit in a single output cell.
Open the workbook in WPS Office Spreadsheets and identify whether the formula should return one result for one row or multiple results. Also check that adjacent cells are empty before testing a spill formula.
Use @ to Force a Single Value from a Structured Reference
Use this when you only need the IF formula to check one row at a time and the #SPILL! error is caused by an entire-column reference.
In Microsoft 365, Excel 2021, and WPS Office Spreadsheets, a structured reference such as Table1[Product Name] can return an array. The @ operator forces implicit intersection, so the formula evaluates the current row instead of the full column.
Click the cell showing the #SPILL! error so the formula appears in the formula bar.
In the formula bar, type @ immediately before the structured reference, for example =IF(ISNUMBER(FIND("consulting",LOWER([@Product Name]))),"Yes","No").
Press Enter and confirm that the #SPILL! error disappears and the formula returns one Yes or No value.
Use a Single-Cell Reference Instead of a Table Column
Use this when you are checking one specific product name rather than an entire table column.
Use a Helper Column When You Need to Search Every Row
Use this if you want to see a Yes/No result for each row in the table instead of forcing a single value.
Fix #SPILL! IF Formula Errors with WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-style functions including IF, FIND, and LOWER, and lets you correct structured references or implicit intersection so the #SPILL! error is resolved without changing your workbook logic.
- 1. Open the workbook: Download and install WPS Office, then open the Excel workbook that contains the #SPILL! formula.
- 2. Inspect the formula: Click the error cell and review the structured reference in the formula bar.
- 3. Apply the fix: Add @ before the column reference or change the reference to a single cell, then press Enter.
- 4. Check the result: Confirm that the formula now returns the intended single result instead of #SPILL!.
Frequently Asked Questions
Why does an IF formula with FIND and LOWER return #SPILL! in WPS Spreadsheets?
It usually happens because the formula references a whole table column, which returns an array of TRUE/FALSE values. If the formula sits in a single cell, WPS Spreadsheets cannot place the entire array in that cell and displays #SPILL!.
How do I stop a formula from spilling when using a structured table reference?
Click the formula cell, go to the formula bar, and put @ immediately before the structured reference, such as [@Product Name]. This forces the formula to use the current row value only.
Does WPS Office support Excel dynamic array formulas?
Yes. WPS Office Spreadsheets supports Excel-style formulas and workbook compatibility, including common dynamic array behavior and structured references in .xlsx files.
Can I use a helper column to avoid #SPILL! errors?
Yes. Add a new column inside the table and enter a row-based formula such as =IF(ISNUMBER(FIND("consulting",LOWER([@Product Name]))),"Yes","No"). The table can fill the formula for each row so results stay in separate cells.
How do I check a structured reference in WPS Spreadsheets?
Select the formula cell and look at the reference in the formula bar. Make sure table and column names match exactly and that apostrophes, if present, surround only the correct identifier parts. You can also click the table to see its actual name under Table Tools.
