Excel #SPILL! Inside a Table: Keep the Table, Move the Formula

A spilled array formula cannot live inside an Excel table. Keep the source records in their table and put FILTER in ordinary worksheet cells outside it. Clearing neighboring table cells does not remove this restriction. Microsoft’s dynamic-array guidance explicitly distinguishes a table used as the source from the location of the spilled output.

This example requires Excel for Microsoft 365, Excel 2024 or Excel 2021 with FILTER. The function is documented for those Windows and Mac editions; the menu steps below use Windows desktop Excel. Excel 2013 does not support this FILTER/spill workflow. For that edition, use a legacy extraction method rather than trying to fix the error by entering the formula with Ctrl+Shift+Enter. Microsoft FILTER reference.

Build a source table that remains useful

In a blank workbook, enter this synthetic input in A1:C7:

Team	Item	Units
North	Pen	12
South	Pad	5
North	Box	7
East	Tape	0
North	Pen	2
South	Folder	3

Select A1:C7 and use Insert > Table. Confirm the range and select My table has headers. With a table cell selected, use Table Design > Table Name to name it Orders. Keep its Total Row off for this exercise. If Orders already exists in the workbook, use a fresh workbook so the written formulas refer to the intended source. Create and format tables; table names and structured references.

The repeated North/Pen records are deliberate: FILTER should retain both. The zero for East/Tape is also intentional. Do not replace it with a blank.

In E1 enter Team to return, and in E2 enter North. Leave F empty as a buffer. Put Item in G1 and Units in H1. Reserve ordinary, unmerged cells G2:H20 for the result. Do not format that output block as another Excel table.

Reproduce and identify the table restriction

On this disposable example only, add a fourth table column D headed Attempt. In its first data cell D2, enter:

=FILTER(Orders[[Item]:[Units]],Orders[Team]=$E$2,"No matches")

This requests multiple rows and columns inside a table column. The expected diagnosis is the table formula cause of #SPILL!, rather than an ordinary piece of text blocking an otherwise valid spill. Excel may copy the formula through the calculated column; that does not make it a supported spill location. Microsoft’s table-formula error explanation.

After inspecting the error, remove only this newly created Attempt table column. Do not delete any real source columns in your own workbook. If working on an existing failure, copy the formula text somewhere safe before changing its location.

Put the same formula in G2

Enter the formula once in G2 and press ordinary Enter:

=FILTER(Orders[[Item]:[Units]],Orders[Team]=$E$2,"No matches")

Expected result in G2:H4:

Item	Units
Pen	12
Box	7
Pen	2

Only G2 owns the formula. Do not fill it down and do not type into the other result cells. To change the formula, edit its top-left cell. The output omits the source headers because these structured references select data columns.

Orders[[Item]:[Units]] returns the adjacent Item and Units columns. Orders[Team]=$E$2 produces one inclusion decision for each source row. The absolute E2 reference is the selector. The table name allows these ranges to adjust when the table grows; converting the source to an ordinary range would remove that benefit. FILTER syntax and table resizing.

The formulas use English names and comma argument separators. Adapt localized names/separators where your installation requires them. See the FILTER guide for general syntax; this tutorial concentrates on the table/output boundary.

Check the result against the source, then change it

Put control formulas in J1:K3, away from the spill:

• J1: Source match count; K1: =COUNTIF(Orders[Team],$E$2)
• J2: Source unit total; K2: =SUMIFS(Orders[Units],Orders[Team],$E$2)

For North, expect 3 records and 21 units. These controls count and total the source; they are not measurements of how many result cells happen to be occupied. Compare them with the three returned records. See COUNTIF and SUMIFS.

Test E2 with these values before moving on:

Selector	Expected output	Source count	Source units
South	Pad / 5; Folder / 3	2	8
East	Tape / 0	1	0
West	One message: No matches	0	0

For West, the third FILTER argument returns a single text result in G2. It is not a one-record match or a two-column placeholder row. Without this argument, a no-match FILTER can return #CALC!. Restore North after these tests.

Now append North, Folder, 4 in the next table row. Confirm that the table actually expanded to include the new record: if it did not, use Table Design > Resize Table and select the full A1:C8 source range. Microsoft documents table resizing. The expected result expands to G2:H5, adding Folder / 4 after the existing three rows. The source controls become 4 and 25. Existing duplicates should still remain separate records. Restore the original input by removing that practice table row when finished.

A second #SPILL! after moving outside the table

Moving G2 outside the table resolves only the table restriction. A different obstruction can still block its output.

To test safely, with the original six records restored, change E2 to West, which collapses the result to G2. Enter Reserved in H4, then change E2 to North. The intended three-by-two output now needs H4, so a blocked-spill error is expected. Select the error cell and use its error alert, including Select Obstructing Cells when offered, to identify the blocker. Move or remove only the disposable Reserved text; the original three rows should return. Microsoft: diagnosing #SPILL!.

For real reports, inspect before deleting:

• A formula returning "", a space or an apostrophe can occupy a cell that looks empty.
• A merged cell in the intended output area prevents spilling. Move the output or carefully unmerge only an expendable layout region.
• A result placed too close to the worksheet edge may have nowhere to expand. Use a bounded source and more room.
• A second table is not a suitable spill destination. Put the result on a dedicated ordinary-cell report sheet if the source is likely to grow into it.

A number format, colored fill or wider column does not turn an occupied output cell into an empty one. Do not delete the source table to clear the result area.

When a table calculated column is the right tool

If you want one value per input record, a scalar formula such as =[@Units]*2 is suitable for a table calculated column. For the original six rows its values are 24, 10, 14, 0, 4, 6. That is a different goal from returning a varying number of matching records.

Do not add @ to a spill formula just to silence an error without checking the meaning: implicit intersection requests a single result rather than the full matching list. Converting a table to a range is another documented option, but it changes the source’s structured-reference behavior. Keeping the table and separating the report is usually the less disruptive repair. Structured-reference behavior.

Verification notes

Not tested in Excel. Microsoft references were checked October 16, 2026. All output rows, counts, totals and the doubled quantities were independently calculated outside Excel. The error and recovery steps are documentation-based expectations, not native execution results or screenshots. No claim is made about an original release date, every Microsoft 365 update channel, or the Mac/web menu layout.