Excel UNIQUE, SORT, and FILTER Spill Errors Explained

Formulas & functions · Updated November 20, 2026

If a formula using UNIQUE, SORT, and FILTER returns #SPILL!, Excel cannot place its results in the cells the formula needs—usually because one or more of those cells is occupied or merged. Clear the obstruction, or move the formula to a location with enough empty space; the formula itself may be perfectly valid.

What an Excel spill error means

In Microsoft 365, UNIQUE, SORT, and FILTER are dynamic array functions: one formula can return several results, and Excel fills the neighboring cells with those results automatically. The original formula sits in the top-left cell of the output, called the anchor cell. The cells Excel intends to fill are its spill range.

A #SPILL! error means Excel cannot place the complete result in that range. It is different from a formula returning the wrong values: the calculation may be valid, but its output cannot be displayed where Excel needs it. Click the error cell to read the explanation Excel provides; when available, the warning menu can offer Select Obstructing Cells to help locate a blocker.

The error is not a special failure of the three functions working together. Any formula that returns a dynamic array can encounter the same problem. The key diagnostic question is: what cells would the result occupy, and what is preventing Excel from using them?

How UNIQUE, SORT, and FILTER work together

FILTER selects rows or values that meet a condition. UNIQUE removes repeated values from the resulting array, and SORT orders what remains. In this arrangement, Excel calculates the filtered list, deduplicates it, sorts it, and spills the final list from the formula cell.

With source values in A2:A100, including possible blanks, enter this formula in a clear cell such as D2:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

The FILTER condition excludes empty cells before the values reach UNIQUE and SORT. If A2:A12 contains Pear, Apple, Pear, Banana, a blank, Apple, Grape, Banana, Kiwi, a blank, and Grape, the results are Apple, Banana, Grape, Kiwi, and Pear, spilling downward from D2. The output needs five available cells, D2:D6.

This assumes a current Microsoft 365 version of Excel. UNIQUE, SORT, and FILTER are also available in Excel 2021 and later versions that support these dynamic array functions. In older Excel versions, the formula may not be recognized or may not behave as a spilling formula.

Check the spill range before changing the formula

Start by selecting the cell showing #SPILL!. Excel often outlines the intended output range. Look through that area for a value, a formula, a space, or another item that is not truly empty. A cell containing a formula that displays an empty string can still block a spill. Formatting alone—such as a fill color or border—does not normally occupy a cell and is not the same as cell content.

Try this sequence:

  1. Identify the anchor cell and the outline or range Excel indicates for the result.
  2. Inspect the intended output cells, including cells that look blank. Select a suspected cell and check the formula bar for a value or formula.
  3. Move or remove the blocking content if it is no longer needed. If it must stay, move the array formula to an area with enough clear cells.
  4. Check for merged cells in the output area. Unmerge them, or choose a different location for the formula.
  5. Re-enter or recalculate the formula if needed, then confirm that the entire result appears and is not cut off by another obstruction.

Do not delete data just to make the error disappear. If a blocking cell contains information you need, place the result elsewhere or reorganize the sheet. Clearing only the obstruction is usually safer than rewriting a working formula.

Common blockers: occupied cells, merges, and worksheet edges

The most common cause is a nonempty cell somewhere in the spill range. It might contain a number, text, a formula, or a single space. A formula that returns "" looks blank but still occupies its cell, so Excel cannot overwrite it with a spilled result. If the warning identifies a blocker, inspect its contents before clearing it.

Merged cells also interfere with dynamic arrays because Excel cannot fill the intended rectangular output normally across a merged area. Unmerge the relevant cells, or move the formula to a region without merges. A spill can also fail when its proposed output would extend beyond the worksheet boundary. That is uncommon for a short list, but possible when an array is very large or the formula is placed near the last row or column.

Another common surprise is that the blocking cell is not immediately next to the formula. A result may need to spill many rows or columns, and any occupied cell inside that footprint can stop the whole output. Check the full outlined range rather than only the first few cells.

Excel tables and dynamic array formulas

Excel tables are useful for structured data, filtering, and automatic expansion, but a dynamic array formula cannot spill from inside a table. If you enter the UNIQUE/SORT/FILTER formula in a table column, Excel may show a spill-related error because table columns expect a formula for each row rather than one formula that fills neighboring cells.

Put the formula in a normal worksheet cell outside the table, leaving an empty area for the results. The formula can still refer to the table’s data. For example, if the source table is named Products and its column is named Item, enter the formula outside that table:

=SORT(UNIQUE(FILTER(Products[Item],Products[Item]<>"")))

This assumes the table is named Products and contains a column whose header is Item. Structured references adjust as table rows are added, while the formula’s output still needs a clear spill area outside the table. If you are unsure of the table or column name, check the Table Design tab and the header row.

Distinguish #SPILL! from #CALC! and other errors

A blank result from FILTER can produce #CALC! when no values meet its condition and no fallback value is supplied. That is a different issue from #SPILL!: the first concerns an empty calculation result, while the second means Excel cannot place an array in its intended cells. Fixing a blocked spill range will not make a no-match FILTER return a value.

If a fallback message is useful, include FILTER’s optional third argument. With source data in A2:A100 and the formula entered in D2, this version displays a message when the filter finds no nonblank values:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"","No matches")))

The fallback is a value for the no-match case, not a way to repair a blocked spill range. If you see #NAME?, check whether your Excel version supports the functions and whether their names are spelled correctly. If the formula returns unexpected items, inspect the source data for spaces, inconsistent spelling, or other values that appear blank but are not empty.

Check what UNIQUE means by “unique”

UNIQUE normally returns one copy of each distinct value. For example, three occurrences of Apple become one Apple in the result. A frequent misconception is that UNIQUE always means “show values that occur exactly once.” It does not: that behavior requires the function’s optional exactly-once argument, which changes the result and is not needed for an ordinary deduplicated list.

The order of the functions also affects what you ask Excel to do. FILTER first is useful when you want distinct, sorted values only from rows that meet a condition. SORT then orders the final distinct list. If a value appears several times in qualifying rows, UNIQUE returns it once. If you filter on one column but need to return corresponding rows from several columns, make sure the include condition has the same number of rows as the array being filtered; mismatched array sizes can cause a different formula error.

Choosing a safer formula and location

Use a bounded source range when practical, rather than referencing an entire worksheet column for a calculation that needs only a portion of the data. A range such as A2:A1000 is easier to inspect and avoids asking Excel to process more cells than necessary. If the source grows regularly, an Excel table can keep the source reference expandable, while the formula remains outside the table.

Before placing the formula, decide how many results might be returned and reserve clear cells below or beside it. Do not type labels, notes, or totals inside that reserved output area. If the output should be horizontal instead of vertical, SORT has an optional orientation argument, but changing orientation does not remove the need for a clear spill range in the chosen direction.

In current Microsoft 365 for Windows, Mac, and Excel for the web, the core dynamic-array behavior is similar: a formula returns results into adjacent cells, and an occupied spill range can prevent that. The appearance of error tips and the steps for selecting or clearing cells can vary by interface. On any platform, the reliable fix is to inspect the intended range, preserve needed data, and provide unobstructed space.

Frequently Asked Questions

Why does UNIQUE return #SPILL! when the cells look empty?
One of the cells in the intended output range may contain a space, a formula returning an empty string, or another value that is not visually obvious. Select the cells and inspect the formula bar, or use Excel’s obstruction guidance when it is available.

Can I put a UNIQUE formula inside an Excel table?
A dynamic array formula cannot spill from inside a table. Enter it in a regular worksheet cell outside the table and leave enough empty cells for its results.

Does a #SPILL! error mean my formula is wrong?
Not necessarily. It usually means Excel cannot display the array where the formula is located. Check the spill range first; if the range is clear, read the specific error explanation Excel shows.

How do I exclude blank cells from the results?
Use a FILTER condition that tests the source range for nonblank values, as in the examples above. Cells containing spaces are not empty, so those may need to be cleaned separately.

Why do I get #CALC! instead of #SPILL!?
FILTER can return #CALC! if no rows meet its condition and you did not provide an if-empty value. Add FILTER’s third argument for a message or other fallback; that does not fix a separate spill obstruction.

Will the formula work in older Excel versions?
UNIQUE, SORT, and FILTER require a version with dynamic array support, including Microsoft 365 and Excel 2021 or later. In older versions, use another approach, such as Excel’s built-in Remove Duplicates command, or update to a supported version.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.