Excel shows #SPILL! when a dynamic-array formula tries to return results inside an Excel Table, because spilled arrays cannot expand within table cells. Move the formula to an ordinary worksheet cell outside the table, or use a row-by-row table formula; creating a table does not usually cause the error unless the table blocks a formula’s output area.
Why a table and a spill formula conflict
Dynamic-array formulas can return several results from one formula cell. Excel places the formula in an anchor cell and fills neighboring cells with the returned values. Functions such as FILTER, SORT, UNIQUE and SEQUENCE can work this way in Microsoft 365. The output cells are called the spill range.
An Excel Table has its own structure: each column is a field, and formulas entered in a calculated column are generally copied down the table’s rows. Its cells are managed as part of that table, rather than as an open area for a formula to spill into. As a result, a dynamic-array formula entered in a table cell cannot expand into adjacent table cells and may show #SPILL!.
This is different from using a table as the formula’s source. A table can be an excellent source for a dynamic-array formula; the key is to put the formula and its results outside the table. For example, a FILTER formula can read rows from a table and return matching records in a clear worksheet area.
Check whether creating the table blocked the output
If the error appeared after you selected a range and chose Insert > Table, inspect where the formula is located and where its results need to go. The table may now occupy cells that the formula previously used as a spill range. A spill can also be blocked by values or formulas left in the output cells, even if those cells look blank at a glance.
Select the cell showing #SPILL! and read Excel’s error message or warning icon. Depending on the cause, Excel may identify an obstruction or explain that the spill range is in a table. To check the output area, clear contents from the cells where results should appear; do not delete the formula anchor unless you intend to remove the formula. Also check for merged cells, which can prevent a spill.
Keep in mind that a table’s visual formatting is not itself the issue. Applying a table style to the source data is usually fine. The conflict arises when the formula’s spill output would have to occupy table cells, or when an existing table or other content blocks the output range.
Move a dynamic formula outside the table
- Leave the source records in the table. If needed, click a source cell and use Insert > Table to create or confirm the table; check that My table has headers is selected when appropriate.
- Choose an empty worksheet area outside the table, with enough open cells below and to the right for the likely results.
- Enter the dynamic-array formula in that open area. Use the table name and column names in the formula so it can refer to the source data as the table grows.
- Make sure the intended spill range is clear. Remove blocking values or formulas, and unmerge any cells in the output area if necessary.
For example, assume a table named Sales has the columns Region, Item and Amount. Its rows include East / Widget / 120, East / Cable / 85 and West / Adapter / 60. Cell I2 contains East, and J2 is outside the table with clear cells to its right and below. Enter this formula in J2:
=FILTER(Sales,Sales[Region]=I2,"No matches")
The formula returns the matching table rows as a three-column result: East / Widget / 120 and East / Cable / 85. If no rows match, it displays No matches. The result is a spilled worksheet range, not a new Excel Table; it can change size when the source data or the selection in I2 changes.
Use a calculated column for one result per row
If each source record needs one calculated value, a normal table formula is often the better fit. Enter a row-level formula in the relevant table column and Excel can fill it down as a calculated column. This keeps each result attached to its record and avoids needing a variable-size spill range.
For example, assume a table named Orders has Quantity and Price columns. In its Amount column, a row where Quantity is 3 and Price is 12.5 can use:
=[@Quantity]*[@Price]
For that row, the result is 37.5. The [@ColumnName] references mean “the value in this row” for the named table column. This is not a dynamic-array formula: it returns one value for each row, which suits the table’s calculated-column behavior. If Excel does not fill the formula down automatically, check that the entry is inside the table column and review Excel’s calculated-column behavior rather than trying to make the formula spill.
Fix other common causes of #SPILL!
A table conflict is one possible cause, but the error can also occur when an otherwise valid formula has nowhere to put its results. Check the formula’s expected output range, not just the cell containing the formula. Excel’s message for the selected error cell can help distinguish these cases.
- Cells contain something: A space, an apostrophe, a formula returning an empty string, or another value can still occupy a would-be output cell. Clear the contents if they are not needed.
- Cells are merged: Unmerge cells that overlap the intended spill range, or move the formula to an unmerged area.
- The formula sits inside a table: Move the formula outside the table, or replace it with a suitable single-result formula for each row.
- The output area is too close to other data: Move the formula to a larger empty area, particularly if the number of returned rows can grow over time.
- The formula’s result changes size: Leave room for the largest reasonable result, or use a different design if a changing spill range is not practical on that sheet.
Do not assume that hiding rows or columns makes occupied cells available. Hidden cells may still be part of the worksheet range, and the formula needs a valid destination for its results. Likewise, formatting alone is different from cell contents: clear contents to test for obstructions, rather than removing useful formatting without checking.
When to turn the result into a table
A spill range is dynamic, but it is not the same object as an Excel Table. If you need table features such as filter buttons, a named table for use in structured references, or automatic calculated-column behavior, you may want to turn a current result into a table. Excel cannot make the live spill output itself a table while preserving the spill formula in that output area.
One option is to copy the spilled results and paste values into a separate range, then select that range and choose Insert > Table. This makes a snapshot: those pasted values do not automatically update when the source table or filter condition changes. Refresh or repeat the process when you need new results. If the formula’s output changes size, check the copied range carefully so you do not omit records or include unwanted blank cells.
Another option is to keep the formula output as a normal range and use its results for analysis without converting it. If the task is a repeatable import or transformation, Power Query may be a better workflow: it can load transformed data as a table, while the dynamic-array formula remains separate. Choose based on whether you need a live formula result, a structured table, or a refreshable transformation; those are related but distinct needs.
Preserve the source data and choose a layout
Before changing the table or clearing cells, identify which range is the original data and which is the formula output. A useful layout is to keep source records in one table, selection criteria in separate cells, and formula results in a clearly marked area outside the table. This makes the boundary visible and reduces the chance that a later table resize will cover the spill range.
If the spill formula is already inside a table and the table itself must remain, copy the formula, place it in an empty outside cell, and confirm that its references still point to the intended source. Then decide what to do with the original table cell: remove the incompatible formula or replace it with a single-value calculation. Avoid converting the source table to a range just to suppress the error unless you are willing to give up table-specific features.
For a one-time report, pasted values may be sufficient. For a live report, keep the spill formula outside the table. For per-record calculations, use a calculated column. For a refreshable data-cleaning process, consider a query workflow. Picking the layout first is usually safer than repeatedly moving ranges after the sheet has grown.
Frequently Asked Questions
Can an Excel Table contain a FILTER formula?
It can be used as the source for FILTER, but a dynamic result cannot spill within the table. Put the FILTER formula in a worksheet cell outside the table and leave room for its results.
Will converting a spill range to a table keep it updating?
No. A table cannot be the live spill destination. Copying the results and pasting values before making a table creates a static snapshot, so it will not recalculate from the original spill formula.
Why does #SPILL! appear even though the cells look empty?
A cell may contain an invisible-looking space, an apostrophe, or a formula that returns an empty string. Select or clear the suspected output cells, and check whether merged cells or a table overlap the range.
Does formatting my data as a table always cause #SPILL!?
No. The table can be the source data for a spill formula. The error occurs when the formula is placed in the table or its output would need to occupy blocked table cells.
Does the fix differ in Excel for Mac or the web?
The basic rule is the same: put a spilling formula outside the table. Ribbon wording and layout can vary by platform, but Excel for Mac and Excel for the web also support tables and dynamic-array behavior in current Microsoft 365 versions.
Related
- Excel Ribbon Keyboard Shortcuts: A Practical Guide
- Excel Reverse Undo Shortcut: What Works and What Doesn’t
- Excel Reverse Worksheet Order: How to Do It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Table Filter Not Working? Fixes to TryIf an Excel table filter is not working, click its drop-down arrow and choose Clear Filter From [column],…
- Excel Spill Error: Why It Appears and How to Fix ItExcel’s #SPILL! error means a formula is trying to return results into multiple cells, but Excel cannot place…
- Excel Error: Why You Can’t Change Part of an ArrayThe Excel message “You can’t change part of an array” means the cell you are trying to edit…
- Excel Conditional Formatting: Apply Rules and Fix IssuesTo apply conditional formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home >…
- Excel Merged Cells Must Be the Same Size? Fix the ErrorYes—when Excel says “To do this, all the merged cells need to be the same size,” it usually…
- Excel Custom Number Format Not Working? FixesIf an Excel custom number format is not working, first confirm the selected cells contain numbers or dates…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.