Excel FILTER Function in a Table: Fixing #SPILL!

Errors & fixes · Updated November 19, 2026

If Excel’s FILTER function returns #SPILL! because its formula is inside a table, move the formula to a blank worksheet area outside the table; FILTER can still use the table as its source. Dynamic-array results cannot spill inside an Excel table, and any cells the results need to occupy must also be clear and unmerged.

Why FILTER and Excel tables clash

FILTER is a dynamic-array function: one formula can return several values, which Excel places in neighboring cells as a spill range. An Excel table is a structured data area with its own rules for calculated columns, headers, and expansion. Excel does not allow a dynamic array to spill into the body of a table. A FILTER formula entered in a table cell may therefore show a #SPILL! error, including the explanation that the spill range is in a table.

This is a location limitation, not evidence that FILTER cannot read table data. You can keep records in a table, benefit from structured references, and put the formula in an ordinary worksheet range. The table can continue to grow as you add records, and a formula referring to the table can include the added rows.

FILTER is available in Microsoft 365 and Excel 2021 or later. Older Excel versions that do not support dynamic arrays will not calculate the function as intended; moving the formula outside a table does not add the function to those versions.

How to move the formula and keep the table as a source

Suppose a table named Sales has columns Region, Rep, and Amount. It contains West / Maya / 120, East / Leo / 90, and West / Noor / 150. Put the formula in a blank cell outside the table, such as G2, with enough empty space to its right and below for the results.

With the Sales table containing those three columns and records, enter this in G2 outside the table:

=FILTER(Sales,Sales[Region]="West","No matching rows")

The result is two rows: West / Maya / 120 and West / Noor / 150. The formula returns matching records from the table, but it does not add the table’s header row to the results. The third argument supplies a message if there are no matches.

To return only names, with the same table and records and the formula in an empty cell outside the table, use:

=FILTER(Sales[Rep],Sales[Region]="West","No matching rows")

This returns Maya and Noor in two cells. The include condition, Sales[Region]=”West”, tests each record, while the first argument determines which values FILTER returns. For a row-based filter, the include range must correspond in height to the array being filtered.

  1. Click a blank cell outside the table where the first result should appear.
  2. Enter the formula using the table name and column references, or build the formula with cell references.
  3. Press Enter. Excel should show a spill outline around the output area and fill the matching results.
  4. If Excel still reports #SPILL!, select the error cell and inspect Excel’s explanation, then clear or relocate whatever prevents the results from appearing.

Check that the spill area is really available

Moving a formula out of the table solves only the table-specific blockage. FILTER needs to place every returned value in the cells covered by its spill range. If one of those cells is occupied, Excel cannot display the complete result and reports #SPILL! for an obstruction. A cell can count as occupied even if its formula displays an empty string, so check for formulas as well as visible text, numbers, and spaces.

Look for these common obstructions:

  • Existing values or formulas beneath or beside the formula cell.
  • A merged cell in the area where results need to appear.
  • A spill range that overlaps a table, even if the FILTER formula itself is outside that table.
  • A result that would extend beyond the worksheet’s last row or column.

Select the #SPILL! cell and use the error indicator to see whether Excel identifies a blocked range or another specific cause. Clear only cells you intend to replace: deleting existing content to make room for a spill can remove data or formulas. Alternatively, move the formula to a larger empty area or adjust the output so it returns fewer columns.

Distinguish no matches from a spill error

No matching records and a blocked spill range are different problems. FILTER’s optional third argument lets you specify what to return when the include condition finds no rows. In the examples above, that text is “No matching rows.” Without a suitable if_empty argument, a filter with no results can produce #CALC!, not #SPILL!.

A #SPILL! error means Excel cannot place the returned array in its intended cells. The reason might be the table limitation, an obstruction, or another spill-range constraint. Do not treat the third argument as a general error handler: it addresses the no-match case, not occupied cells or an invalid formula. Likewise, wrapping a formula in a broad error-handling function can hide problems that should be diagnosed directly.

Use structured references without putting the formula in the table

Structured references such as Sales[Region] and Sales[Rep] name table columns rather than fixed worksheet addresses. They are useful in an outside-the-table FILTER formula because the reference follows the table’s data as records are added or removed. Give the table and its columns clear names so the formula remains readable.

For example, if new records are added to Sales and their Region value is West, the formulas above can include those records without manually extending a reference such as A2:C20. If your formula uses a table name or column heading that does not exist, correct the name in the formula or rename the table or heading. Table names and structured-reference syntax must match the workbook.

Keep the output area separate from the input table. Placing results beside a table may be convenient, but leave enough blank rows and columns for the largest likely result. If the table might expand toward the output, move the formula to a separate worksheet or a distant clear area to reduce the chance that new records and spilled results collide.

FILTER results are not the same as filtering table rows

A FILTER formula creates a separate result array; it does not hide nonmatching rows in the original table. If your goal is to inspect or temporarily show only certain records in the existing table, use its filter controls instead. Select a table header’s filter arrow and choose the values or conditions to display. This changes which table rows are visible, rather than producing a second list of results.

Use the table’s built-in filtering when you want to work directly with the original records, keep the table intact, or print a filtered view. Use FILTER when you need a formula-driven output elsewhere—for example, a changing report, a list for another calculation, or a subset displayed on a dashboard. A formula result is linked to its source, but it is not an independently editable copy of those records.

In Microsoft 365 for Windows, table filter arrows are available in the table headers. You can also select a cell in the table and use Data > Filter to toggle filtering controls. Excel for Mac and Excel for the web have comparable table-filter features, though ribbon layout and labels can differ by platform and update. These commands filter the table; they do not make a FILTER spill inside it possible.

When converting the table to a range is an option

If you do not need table features, you can convert the table to a normal cell range and place the dynamic-array formula in an area that is no longer part of a table. In Excel for Windows, select a table cell and use the Table Design tab’s Convert to Range command. Confirm the prompt if Excel asks whether to convert the table.

Conversion removes table behavior such as structured references and automatic table expansion, though existing cell values and formatting remain. It is usually not the first choice when the table is serving as a maintained data source: keeping the table and placing FILTER elsewhere preserves useful organization and references. Even after conversion, the result still needs an unobstructed spill area; a range of cells is not automatically a clear spill destination.

Common formula and layout checks

If moving the formula does not fix the result, review both the formula and the worksheet layout. A condition for filtering rows should test the corresponding records, not a differently sized list. Confirm that the table column names are spelled correctly and that the formula uses the intended column for the condition. If a criterion is typed as text, put it in straight double quotation marks as in the examples.

Check whether the output is wider or taller than expected. FILTER returns the shape of its array argument for the matching records: filtering the entire Sales table returns all of its data columns, while filtering Sales[Rep] returns one column. Choosing a narrower array can reduce the room needed for the result, but it does not change the rule against spilling into a table.

Also consider whether other formulas or workbook changes might take up the output area later. A spill range is managed by Excel from its top-left formula cell; cells within that range are not places to type separate values. If you need a fixed, manually editable snapshot rather than a live result, copy the spilled output and use Paste Special > Values in a different location. That pasted result will no longer update when the source table changes.

Frequently Asked Questions

Can the Excel FILTER function spill inside a table?
No. Dynamic-array results are not supported as spill ranges inside Excel tables. Enter the formula in a normal worksheet range outside the table, while continuing to use the table as the formula’s source if desired.

Why does FILTER show #SPILL! outside my table?
One or more cells the result needs may already contain content, may be merged, or may belong to a table. Select the error cell and inspect Excel’s explanation, then clear or move the obstruction or choose a different output location.

Can FILTER use an Excel table as its source?
Yes. A formula outside the table can use structured references such as Sales[Rep] and Sales[Region]. The limitation is where the array result can spill, not whether the source can be a table.

Why do I get #CALC! instead of #SPILL!?
A filter with no matching rows can return #CALC! if no if_empty result is supplied. Add a third argument such as “No matching rows” to provide a result for that case. That does not resolve a blocked spill range.

Should I use FILTER or the table’s filter arrows?
Use the arrows to hide nonmatching rows in the original table for browsing or analysis. Use FILTER when you need a separate, formula-driven list of matching values elsewhere on the sheet.

Does FILTER work in older Excel versions?
FILTER is available in Microsoft 365 and Excel 2021 or later. If your Excel version does not support dynamic arrays, use the table’s built-in filters or an approach supported by that version; moving the formula outside the table will not add FILTER support.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.