Excel UNIQUE Spill Error: Causes, Fixes, and Checks

An Excel UNIQUE spill error usually means the results cannot fit in the cells where the formula needs to return them. Clear or move anything blocking the spill range, unmerge cells in that area, and place the formula outside an Excel table; then Excel can display the list. The exact cause is often shown beside the formula cell when you select its error icon.

What the UNIQUE function does

UNIQUE returns distinct items from a range or array. Instead of requiring a formula in every result cell, it uses dynamic arrays: one formula produces a result that spills into neighboring cells. The function is available in Microsoft 365 and Excel 2021 or later, including supported newer releases. In Excel for the web and current Excel for Mac, the same basic dynamic-array behavior applies, although ribbon layout and some interface details can differ.

With names in A2:A10, enter this formula in an empty cell outside the source list:

=UNIQUE(A2:A10)

Excel returns one copy of each distinct name, normally in the order the values first appear in the source. The formula cell is the top-left cell of the output, and Excel marks the resulting range with a blue outline when you select it. That whole output is called the spill range.

UNIQUE also has optional arguments. With values arranged across columns in A2:D2, this formula compares columns rather than rows:

=UNIQUE(A2:D2,TRUE)

With names in A2:A10, this version returns items that occur exactly once, excluding names that appear more than once:

=UNIQUE(A2:A10,FALSE,TRUE)

The third argument changes the meaning of “unique”: the default returns one copy of every distinct item, while TRUE returns only items whose count is one. It is not a setting to use merely to remove duplicates.

First identify which error you have

“UNIQUE spill error” often refers specifically to #SPILL!, but a UNIQUE formula can encounter other errors. The distinction matters because clearing cells fixes an obstructed spill, not an unavailable function or an invalid source calculation. Click the formula cell and read Excel’s error message before changing the workbook.

#SPILL! means Excel cannot place the complete dynamic-array result in the required cells. A blocked cell, merged cell, table limitation, or output extending beyond the worksheet may be responsible.
#CALC! can occur when an array calculation produces no results, such as a FILTER operation with no matches and no specified empty-result value. A range containing blank cells is not necessarily an empty array.
#NAME? may mean Excel does not recognize UNIQUE, often because the workbook is open in an older version that does not support the function or because the formula was entered incorrectly.
An error such as #VALUE! may come from an error in the source data or from another function used to construct the array. Check the source formula as well as UNIQUE.

Check what is blocking the spill range

The most common cause of #SPILL! is existing content in one or more cells where the results need to appear. That content may be an obvious value or formula, but it can also be a space or another character that is difficult to see. A formula returning an empty string still occupies its cell, so it can block a spill even though the cell looks blank.

Select the formula cell. If Excel displays an error indicator, open its drop-down and choose the option to select the obstructing cells when available. Excel highlights cells preventing the output. Inspect each one before clearing it: it may contain information you need. Move that content elsewhere, or clear only the cells that are genuinely safe to empty. You can also select likely spill-range cells and use Home > Clear > Clear Contents.

Remember that the required output can grow or shrink when source values change. A formula that works today may show #SPILL! tomorrow if a newly added distinct value needs an occupied row. Leave enough clear space below or beside the formula for the largest result you reasonably expect.

Other common causes of #SPILL!

Merged cells: A dynamic-array result cannot spill through merged cells. Unmerge cells within the output area, or put the formula where the full result can occupy unmerged cells.
Formula inside an Excel table: Dynamic arrays do not spill inside tables. Put the UNIQUE formula in a regular worksheet area outside the table. It can still refer to the table’s data.
Not enough room at the worksheet edge: A vertical result cannot extend past the last row, and a horizontal result cannot extend past the last column. Move the formula so the output has room.
Unpredictable output dimensions: If the input array or a related formula changes size, the spill range may cover more cells than before. Check the intended output area, not just the cells that were occupied when the formula was first entered.
Spill range is being edited: The individual cells in a spilled result are calculated by the formula in its top-left cell. To change the result, edit that formula or its source data rather than typing into one of the output cells.

If the error message says the spill range is unknown or Excel cannot determine its size, check whether the formula depends on a changing or volatile calculation. The safest troubleshooting step is still to inspect Excel’s specific error detail and verify that the intended output area is clear.

Fix the error without damaging data

Use this sequence to find the cause while preserving information in the worksheet:

Select the cell containing UNIQUE and read the full error description shown by Excel.
Determine how many rows or columns the result should occupy. For a vertical list, inspect the cells below the formula; for a horizontal list, inspect the cells to its right.
Use Excel’s obstructing-cell selection option if it is offered. Review highlighted cells rather than clearing an entire area blindly.
Move necessary values or formulas to another location. Clear obstructing contents only when you have confirmed they are not needed.
Check for merged cells and whether the formula is inside a table. Unmerge the relevant cells or move the formula outside the table.
Re-enter or recalculate the formula if needed. If the error returns after new data is added, leave more room for the changing result or choose a different output location.

Excel recalculates a dynamic array when its inputs change. You do not normally need to copy the formula down: doing so can create duplicate formulas and additional obstructions. Keep one formula in the top-left output cell and let the result spill.

Useful examples and spill-range references

Suppose a source list in A2:A10 contains Ava, Ben, Ava, and Mia, followed by other entries, with Ava and Ben recurring. With the formula in C2 and enough empty cells below it, UNIQUE returns each distinct source value once. Its output begins at C2 and grows down as needed.

With product names in A2:A20, this formula sorts the distinct list alphabetically:

=SORT(UNIQUE(A2:A20))

Place it in an empty cell outside the source range and leave room below. SORT wraps the UNIQUE result; it does not prevent a spill error if the output cells are blocked. If the result order should match the source’s first-appearance order, use UNIQUE without SORT.

You can refer to a complete spilled result with the spill-range operator. If the unique list starts in C2, enter this formula in another suitable cell to count the returned items:

=COUNTA(C2#)

The # in C2# refers to the entire spill range anchored at C2, not to an error. If C2 currently returns three names, COUNTA returns 3; if the list later grows, the reference adjusts with it. Keep the referencing formula outside the spill range so it does not block its own source output.

Using UNIQUE with Excel tables

Tables are useful for source data because they expand as rows are added, but the spill output has to be outside the table. For example, a table named Sales with a column named Product can supply the source values, while the UNIQUE formula sits in an ordinary worksheet cell beyond the table. This lets the table grow without placing the dynamic result inside its structured rows.

Before relying on a formula, confirm that the table name and column heading match the workbook. If the source table occupies the cells where the result would spill, move the output to a clear area or a separate worksheet. A separate output sheet can be a practical choice for long or frequently changing lists.

Unexpected results are not always spill errors

A formula can calculate successfully and still return a list that looks wrong. Blank source entries may produce a blank item in the results, and values that look alike can be stored differently—for example, a number may be stored as a number in one row and as text in another. Clean or standardize source data if those entries should be treated as the same item.

UNIQUE is not a case-sensitive tool for separating words that differ only by letter case. Also, its default behavior is not the same as “show only values that appear once”: it keeps one representative of every distinct value. Use the third argument set to TRUE only when you want values occurring exactly once. These differences explain many reports that a UNIQUE formula is “wrong” when it is actually following a different interpretation of uniqueness.

When UNIQUE is unavailable or unsuitable

If Excel displays #NAME? for a correctly entered UNIQUE formula, check the application version and where the file is being opened. UNIQUE is supported in Microsoft 365 and Excel 2021 or later, but older perpetual versions may not recognize it. If a workbook will be used by people with older Excel, consider a compatible method and verify it in the versions they use.

For a one-time list, Excel’s Remove Duplicates command can modify a copied data range, but it is not a live formula: the result does not automatically update when the source changes. Advanced Filter can also extract distinct records, depending on the task and Excel version. A PivotTable is another option when the goal is to summarize categories rather than maintain a formula-driven list. Choose based on whether the output must update automatically, preserve the original data, and fit the users’ Excel versions.

Frequently Asked Questions

Why does UNIQUE say #SPILL! when the cells look empty?
One or more cells may contain a space, a formula returning an empty string, or other content that is not visually obvious. Select the formula and use Excel’s option to identify obstructing cells, then inspect them before clearing anything.

Can UNIQUE spill inside an Excel table?
No. Put the formula outside the table in a clear worksheet area. It can refer to table data, but the dynamic-array result itself needs ordinary cells in which to spill.

How do I stop UNIQUE from returning a blank?
Remove blank source rows or filter them out before applying UNIQUE. If you use a combined FILTER and UNIQUE formula, account for the possibility that the filter finds no matching rows; otherwise FILTER can return #CALC!.

What is the difference between UNIQUE and Remove Duplicates?
UNIQUE produces a formula-driven result that updates when its source changes. Remove Duplicates edits the selected data range and does not keep a live connection to the original list, so use a copy if preserving the source matters.

Why does UNIQUE show repeated-looking values?
The source may contain values with different underlying types or other data inconsistencies. Check whether an entry is stored as text or as a number, and clean the source if those values should be standardized.

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.