Excel 表格内出现 #SPILL!：原因与 3 种解决方法

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• Excel for Microsoft 365 (Windows, Mac, web), Excel 2021 and Excel 2024: these versions have dynamic arrays, so they can show “Spill range in table”.
• Excel 2019 and earlier: formulas never spill. A formula that returns several values silently shows just one (implicit intersection), so you won’t see this error. FILTER, UNIQUE, SORT and TEXTSPLIT don’t exist there and return #NAME?.
• Google Sheets: Sheets tables behave differently and have their own overwrite error. See Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets.

First, confirm it’s the table causing it

#SPILL! has several causes. Click the error cell, then click the yellow warning icon that appears next to it. The first line tells you which cause you have:

Example data

Two tables. Create each with Insert > Table, then name them on the Table Design tab.

tblOrders

tblCustomers

The goal is to fill the empty columns of tblCustomers. The obvious formula for Orders is =FILTER(tblOrders[OrderID], tblOrders[Customer]=[@Customer]). Lena has two orders, so the result would need two cells, and you get #SPILL!.

Cause 1 (most common): a missing @ in a structured reference

Inside a table, [@Amount] means “Amount on this row”, a single value. [Amount] means “the whole Amount column”, an array. Microsoft’s implicit intersection article explains that the @ is what reduces a range to “the value from the cell on the same row”.

So these formulas spill inside tblOrders, even though you only wanted one value per row (tblRegions is a small lookup table that maps each customer to a region):

=[Amount]*1.2

=XLOOKUP([Customer], tblRegions[Customer], tblRegions[Region])

Add @ to the reference that should be “this row”:

=[@Amount]*1.2

=XLOOKUP([@Customer], tblRegions[Customer], tblRegions[Region])

How to detect it: click into the formula bar and look at each structured reference. If you see a column name in brackets without @ where you meant “this row”, that’s the problem. Selecting a cell in the same row while writing the formula makes Excel insert the @ version for you. Typing the reference by hand is how @ gets left out.

The same applies to normal cell references. A formula like =A:A*2 inside a table spills. =@A:A*2 or =A2*2 doesn’t.

Cause 2: a function that really returns several values

FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, TEXTSPLIT, VSTACK/HSTACK, and an XLOOKUP that returns several columns all produce arrays. Inside a table they can’t spill, no matter how you write the references. Pick one of the three workarounds below.

Workaround 1: Return one value per row (keeps the table)

Ask yourself what single value each row really needs, then wrap the array function in something that returns one result.

All of a customer’s orders in one cell (Orders column, gives A-101, A-103 for Lena and an empty cell for Zoe):

=TEXTJOIN(", ", TRUE, FILTER(tblOrders[OrderID], tblOrders[Customer]=[@Customer], ""))

FILTER still builds an array, but TEXTJOIN turns it into a single text value before Excel has to put anything in the grid. The third FILTER argument ("") matters for Zoe’s row. Microsoft’s FILTER documentation says that without if_empty, an empty result gives #CALC!.

How many orders (Order count column: Lena 2, Omar 1, Rui 1, Zoe 0). You don’t need an array at all:

=COUNTIFS(tblOrders[Customer], [@Customer])

The largest order (Largest order column: 62, 15, 8, 0). Take the first item of a sorted array with INDEX:

=IFERROR(INDEX(SORT(FILTER(tblOrders[Amount], tblOrders[Customer]=[@Customer]), , -1), 1), 0)

For a plain maximum, =MAXIFS(tblOrders[Amount], tblOrders[Customer], [@Customer]) is shorter. The INDEX pattern is useful when you want the 2nd, 3rd or nth item, or a value from another column of the sorted result.

The first item of a list in one cell (from tblOrders[Items], gives Pen, Ink, Pad, Pen). Use TEXTBEFORE instead of TEXTSPLIT:

=TEXTBEFORE([@Items]&",", ",")

Adding "," to the end makes TEXTBEFORE work for single items like Ink. To count items without splitting at all (2, 1, 3, 1), count the commas:

=LEN([@Items])-LEN(SUBSTITUTE([@Items], ",", ""))+1

One column from a multi-column XLOOKUP: use one XLOOKUP per table column, each returning a single column, instead of one XLOOKUP returning tblX[[Col1]:[Col3]].

Workaround 2: Move the spilling formula outside the table

If you need a real list, for example every order for a customer chosen in a cell, put the formula outside the table. It can still reference the table. Microsoft’s FILTER documentation notes that when the source is a table referenced with structured references, the result resizes automatically as you add or remove data:

=FILTER(tblOrders, tblOrders[Customer]=H1, "No orders")

Things to watch for:

• Leave at least one empty column between the table and the formula. If you type into the column right next to a table, Excel may expand the table to include that column, and the formula is back inside the table.
• Keep the spill area empty. Anything typed below the formula gives the “Spill range isn’t blank” version of the error.
• The output isn’t part of the table, so table banding, filters and slicers don’t apply to it. Format it separately, or reference it elsewhere with the spill operator, for example =J2#.

Workaround 3: Convert the table to a normal range

Microsoft’s own suggestion is: select a cell in the table, then Table Design > Tools > Convert to Range. The formulas can spill after that.

You lose quite a lot: automatic expansion when you add rows, structured references (Excel rewrites them as normal cell references), calculated columns that fill themselves, and the table name other formulas and PivotTables may depend on. Only do this when the table features don’t matter. A raw export you’re analyzing once is a good example.

Which workaround to use

Common mistakes

1. Deleting the @ that Excel added to an old formula. Microsoft notes that removing an @ from a formula that returns a range “causes it to spill”. Inside a table that means #SPILL!.
2. Using a whole-column reference as a lookup value. =XLOOKUP(A:A, …) asks for over a million results. Use [@Column] or a single cell.
3. Clearing cells to fix it. “Select Obstructing Cells” doesn’t help here. Nothing is in the way, tables just don’t allow spills.
4. Forgetting FILTER’s if_empty argument. Without it, rows with no matches show #CALC! inside TEXTJOIN.
5. Sharing the workbook with Excel 2019 users. FILTER, SORT and TEXTBEFORE show #NAME? there. Use COUNTIFS, MAXIFS or helper columns if older versions need to open the file.

FAQ

Why can’t dynamic arrays spill inside an Excel table?

Microsoft’s #SPILL! documentation says spilled array formulas aren’t supported in Excel tables. Each table cell holds one value per row, so a formula there has to return a single result.

How do I fix #SPILL! in an Excel table without converting it to a range?

Change whole-column references like [Customer] to this-row references like [@Customer]. If the formula really returns a list, wrap it in a function that returns one value, such as TEXTJOIN, INDEX or COUNTIFS.

What does the @ mean in [@Column]?

It’s the implicit intersection operator. In a table, [@Column] returns the value from the same row as the formula, while [Column] returns the entire column.

Can I use FILTER in an Excel table?

Only if the final result is a single value, for example TEXTJOIN(”, ”, TRUE, FILTER(…)) or INDEX(FILTER(…), 1). A FILTER that returns several rows has to go outside the table.

Does a spilled formula outside a table update when the table grows?

Yes, if it references the table with structured references such as tblOrders[Amount]. Those references grow automatically as you add rows to the table.

Related tutorials

• FILTER vs QUERY in Google Sheets: Which One Should You Use? (cluster hub)
• Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• SUMIFS with Multiple Criteria in the Same Column (OR Logic) in Excel & Google Sheets
• ARRAYFORMULA vs MAP/LAMBDA vs Fill Down in Google Sheets

Live-test findings

A real Excel Table reproduced #SPILL! for a dynamic array inside the table. Outside it, FILTER returned A-101 and A-103. Lena’s summary returned A-101, A-103, order count 2, and largest order 62. These checks used Excel for Mac, not LibreOffice structured-reference emulation.

Sources

• Microsoft Support: How to correct a #SPILL! error (Table formula: “Spilled array formulas aren’t supported in Excel tables”, Convert to range, other #SPILL! causes): https://support.microsoft.com/en-us/excel/how-to-correct-a-spill-error
• Microsoft Support: Implicit intersection operator: @ (same-row value, removing @ causes spilling): https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34
• Microsoft Support: FILTER function (if_empty and #CALC!, results resize with structured references): https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Ablebits: #SPILL! error in Excel (the warning menu shows “Spill range in table”): https://www.ablebits.com/office-addins-blog/spill-error-excel-fix/
• Microsoft Q&A: SORTBY gives a different result if data is in a table (INDEX around a dynamic array inside a table): https://learn.microsoft.com/en-us/answers/questions/5079940/(resolved)-excel-365-sortby-different-result-if-da