Fix Excel #SPILL! Errors in Tables and Extend Data Validation Lists
Question details
The user needs to fix lookup formulas that return #SPILL! after data is converted into an Excel table and create data-validation lists that expand with new table rows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Lookup results work in regular worksheet ranges but must work correctly in tables, while validation drop-down lists need to remain dynamic as data is added.
- Observed behavior
- A lookup formula returns #SPILL! inside an Excel table instead of returning one result per row.
Confirm which column contains the lookup value and which table contains the return data. In an Excel table, formulas must normally return one value for each row rather than spill an array across multiple cells.
Use structured references for a single-row lookup in the Excel table
Use this method when a lookup formula is entered in a table column and should calculate one result for each table row.
Excel tables do not allow a dynamic-array formula to spill into multiple table cells. Replace range-based references with a current-row structured reference so each row returns a single value.
Click the first data cell in the Excel table column where you want the lookup result to appear.
Type a formula that uses the current-row operator, such as =VLOOKUP([@ColumnName],Table2,2,FALSE), replacing ColumnName with the lookup column and Table2 with the lookup table or range.
Press Enter. Excel applies the calculated-column formula to the table rows and returns one lookup result per row.
If #SPILL! still appears outside a table, select the error cell and remove or move any values, merged cells, or table content blocking the indicated spill range.
Apply data validation to a table column with a dynamic source list
Use this method when new rows need the same validation rule and the drop-down source list should grow automatically.
Create Table Lookups and Expanding Drop-Down Lists with WPS Spreadsheets
WPS Spreadsheets lets you build row-by-row lookup formulas and data-validation drop-down lists for organized worksheet data. Use table-style ranges, single-cell lookup formulas, and named source ranges to keep calculations and validation choices easy to maintain as your data grows.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the lookup table and validation list.
- 2. Enter a row-level lookup formula: Click the first result cell and enter a lookup formula that references the current row's lookup value, then press Enter to calculate the result.
- 3. Define the validation-list source: Select the allowed values and use Formulas > Define Name to create a named range for the list source.
- 4. Apply a list validation rule: Select the destination data cells, choose Data > Data Validation, select List, and enter the named range in the source field.
Frequently Asked Questions
Why does an Excel formula return #SPILL! after I convert a range to a table?
Excel tables do not support spilled array results within their cells. Change the formula so it returns one value per row, such as a lookup using a current-row reference like [@ColumnName].
What does the @ symbol mean in an Excel table formula?
The @ operator means the current row. For example, [@Product] refers to the Product value in the same table row as the formula.
How do I find what is blocking an Excel spill range?
Select the cell showing #SPILL! and review Excel's error indicator for the blocked range. Clear cells containing values, unmerge merged cells, or move other content out of the required output area.
How can new table rows inherit a data-validation drop-down list?
Apply Data > Data Validation to the existing table column rather than only to individual cells. When you add a new table row, Excel extends the column rule to that row.
Can a data-validation list expand when I add new allowed values?
Yes. Store the allowed values in a source table column and use a defined name that refers to that column as the Data Validation list source. New source-table entries are then included automatically.
