To fix a #SPILL! error in an Excel table, move the dynamic-array formula into a regular worksheet cell outside the table, where the results can fill empty neighboring cells. Excel tables do not support spilled array results inside their table cells; if a formula needs to stay in a table, make it return one value per row instead.
The quickest fix for a spill error in a table
First check whether the formula is located in a table column. Click the cell showing #SPILL! and read the error message or warning icon. If it says the spill range is in a table, cut or copy the formula to a blank cell outside the table. Leave enough empty cells below or beside that cell for every result the formula may return.
For example, suppose a table named Sales has columns Item and Units, with Pencil (4 units), Folder (0 units), and Pen (3 units). Enter the following in H2, outside the table, and make sure the cells below H2 are available:
With a table named Sales containing Item and Units columns, and the three example rows above, enter this formula in H2 outside the table:
=FILTER(Sales[Item],Sales[Units]>0)
The result is Pencil and Pen in two cells. The formula belongs only in H2; Excel fills the matching results into the cells below it. Do not type the formula into a row of the Sales table if you expect it to return multiple items.
If the formula is already outside a table, the table may not be the problem. A cell in the intended output area might contain a value, a formula, or a space, or the spill area may include a merged cell. Use the steps below to identify the specific cause before clearing anything.
Why Excel tables and spill formulas conflict
Dynamic-array functions such as FILTER, SORT, UNIQUE, and SEQUENCE can return several results from one formula. Excel places the formula in a single anchor cell and displays the rest of the results in a spill range. These functions are available in Microsoft 365 and Excel 2021 or later, although availability can depend on the particular function and version.
An Excel table has different rules. Table columns are designed to hold a value or calculation for each table row. When you enter a formula in a calculated column, Excel can copy that row-level formula through the column and adjust its structured references. A multi-cell spill would conflict with the table’s row-and-column structure, so a formula that tries to spill from within a table cannot do so.
This does not mean tables cannot be used with dynamic-array formulas. A formula outside the table can refer to table columns, as in the FILTER example, and its results can spill into an ordinary worksheet range. The key distinction is where the formula is entered: the formula can read table data, but its spill output must be outside the table.
Also, not every #SPILL! message indicates a table conflict. Excel uses the same general error when it cannot place an array result for another reason. The error detail is a useful clue: identify the cause first rather than changing the formula at random.
How to identify what is blocking the spill
Select the cell with #SPILL! and inspect the outlined spill area, if Excel shows one. In Excel for Microsoft 365 on Windows, open the error menu beside the cell and choose Select Obstructing Cells when that option is available. Excel will select cells preventing the result from appearing. The wording and available commands can vary slightly by Excel version.
- Spill range in table: The formula’s output would occupy part of an Excel table. Move the formula outside the table.
- Spill range isn’t blank: One or more cells in the expected output area contain something. Check the entire outlined range, not just the first cell.
- Merged cells: A merged cell lies in the area where Excel needs to place results. Unmerge the relevant cells or move the formula.
- Spill range is too big: The requested result would extend beyond the worksheet’s available rows or columns. Reduce the returned range or place the formula where there is room.
A cell can look empty and still block a spill. It may contain a formula returning an empty string, a space, or another invisible character. Formatting alone, such as a fill color or border, does not make a cell nonblank; the cell’s contents are what matter. If a spill range has a blue outline, inspect every cell inside it, including cells outside the visible portion of the current window.
Clear blocked cells without losing data
When Excel identifies an obstruction, check what is in the selected cell before deleting it. If it contains useful information, move that information elsewhere or choose a different location for the spill formula. Clearing a blocker removes its contents, so do not use Delete or Clear Contents on a large range until you know what it contains.
If a cell appears blank but is blocking the result, select it and look in the formula bar. A visible formula, a space, or an apostrophe can explain why Excel does not treat it as empty. Remove the content only if it is safe to do so. If the blocker is part of a merged area, select the relevant cells and use Home > Merge & Center > Unmerge Cells on Windows, then check that the resulting cells are suitable for the spill.
Some obstructing cells may be far below the formula, especially when a formula returns a long list. Use the error menu’s selection command when available, or inspect the outlined range and clear only confirmed obstructions. If the output may change size over time, keep the whole area below and beside the anchor cell available. New source records can make a formerly short spill longer.
If the obstruction is another table, move the formula to an area with enough clear space or place it on a separate worksheet. Do not insert the spill formula into a nearby table column simply because the first few results appear to fit; a later refresh or data change could require more cells and create another error.
Keep row-by-row calculations inside the table
If the desired calculation produces one answer for each table row, a normal table formula is usually the right choice. For example, a Sales table might have Units and Price columns and a Total column. Put this formula in the first data row of Total:
With Units and Price columns in a Sales table, enter this in a row of the Total column:
=[@Units]*[@Price]
For a row with 4 units at a price of 1.5, the result is 6. Excel typically fills the calculated-column formula through the table, and new table rows can inherit it. This is a single value per row, not a spill formula, so it fits the table’s design.
If you need one summary value from a table, use a formula that returns a single result in a cell outside the table. For instance, with the Sales table containing Units values of 4, 0, and 3, this formula returns 2:
With the Sales table and the Units values 4, 0, and 3, enter this formula in a regular worksheet cell:
=COUNTIF(Sales[Units],">0")
That count is not a spill. It can sit beside the table without needing an output range. Choose a scalar calculation like this when the question is a total, count, average, or other single summary rather than a list of matching rows.
Choose the right place for a list of results
For a list produced by FILTER or another dynamic-array function, choose a normal worksheet range with enough open cells. Leave a buffer if the number of results can grow, and avoid placing unrelated notes or formulas directly in the likely spill area. You can put the results on another worksheet if the table occupies most of the current sheet.
If the goal is simply to show selected records from a table, Excel’s table filters may be more convenient than a separate formula-generated list. Use the filter arrows in the table headers to display rows matching a condition. Filtering changes which table rows are shown; it does not create a new spilled copy of those rows elsewhere.
Converting a table to a range is another possible workaround, but it changes the object rather than fixing the formula placement. In Windows Excel, select a table cell and use Table Design > Tools > Convert to Range. The data remains on the sheet, but it no longer has table features such as structured table references, automatic table expansion, and table-style filtering. Consider moving the formula outside the table first. On Mac and Excel for the web, tab names or menu placement can differ, but the same principle applies: keep dynamic-array output outside a table.
Prevent the error when formulas or data change
Dynamic results can change length when source data changes. A FILTER formula might return two items today and ten after new records are added. Plan for the possible output, not just the current output, and avoid placing permanent content in the area where the list could expand.
- Keep spill formulas in ordinary worksheet cells, not in table data rows or table headers.
- Separate source data, table calculations, summaries, and spilled lists so their ranges do not collide.
- Use table structured references to read changing source data while placing the result formula outside the table.
- When moving a table or adding columns, recheck nearby spill formulas for overlaps.
- Do not copy a dynamic-array formula into every cell of its intended output. Keep one formula in the anchor cell and let Excel populate the spill range.
In Excel for the web, dynamic-array results also need room outside a table, and an obstruction still prevents the output from appearing. The ribbon may look different from Windows desktop Excel, but moving the formula to an empty range and checking the blocking cells are still the core fixes. On Mac, the exact ribbon labels can vary by release; the table-versus-spill limitation remains the important point.
Frequently Asked Questions
Can a dynamic-array formula spill inside an Excel table?
No. A table cell cannot host a multi-cell spill range. Put the formula in a regular worksheet cell outside the table, or change the calculation so it returns one value per table row.
Why does Excel show #SPILL! when the cells look empty?
A cell in the output range may contain a formula returning an empty string, a space, or another hidden character. Select the obstructing cell and inspect the formula bar. Formatting by itself does not block a spill.
Will converting the table to a range fix the error?
It may remove the table-specific restriction, but the output cells must still be clear and unmerged. Converting also removes table features, so moving the formula outside the table is usually the less disruptive solution.
Can I use FILTER with data stored in a table?
Yes. Reference the table’s columns in the FILTER formula, but enter that formula in a regular worksheet cell outside the table. Leave enough clear cells for all matching rows.
Does hiding rows or columns fix a spill error?
Hiding rows or columns is not a dependable fix for a spill conflict. Excel still needs a valid output area. Resolve the stated cause, such as a table, a nonblank cell, a merged cell, or insufficient worksheet space.
Should I delete the table to fix #SPILL!?
Usually not. Tables provide useful structured references and expand as data is added. Keep the table and put the spill formula elsewhere unless you have a separate reason to give up those table features.
Related
- Excel Vendor List Template: Build and Manage Your Records
- Excel Data Validation from a List: Setup and Troubleshooting
- Excel Named Ranges: How to Create and Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Reading Dates as Dates? Fix the Cell ValuesIf Excel is not reading a date as a date, the cell usually contains text that only looks…
- Excel Named Range Error? Causes and FixesAn Excel named range error usually means the name is invalid, unavailable in the current worksheet or workbook,…
- COUNTA Not Working in Excel? Fix Common Counting ErrorsIf COUNTA is not giving the result you expect in Excel, first check what is actually in the…
- Excel FILTER Function in a Table: Fixing #SPILL!If Excel’s FILTER function returns #SPILL! because its formula is inside a table, move the formula to a…
- Excel Circular Reference Popup: How to Find and Fix ItThe Excel circular reference popup means a formula, directly or through other cells, depends on its own result.…
- Excel Goal Seek Not Working? Fix These Common ProblemsIf Excel Goal Seek is not working, first confirm that the Set cell contains a formula linked to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.