Excel UNIQUE Spill Error: Causes, Fixes, and Examples

If Excel shows #SPILL! for a UNIQUE formula, it means Excel cannot place the formula’s results in the cells it needs. Clear or move anything blocking the output range, unmerge cells in that range, or put the formula outside an Excel Table; then check that enough worksheet rows and columns remain for the results.

What the UNIQUE spill error means

UNIQUE is a dynamic array function: one formula can return several values, and Excel fills neighboring cells with those results. The formula cell is the anchor. The cells Excel intends to fill form the spill range. A #SPILL! error means that range cannot be used as required; it does not, by itself, mean that the source data contains duplicates or that the function is written incorrectly.

For example, with the values Pear, Apple, Pear, Banana, Apple, Kiwi, and Banana in A2:A8, place the formula in D2. The formula returns Pear, Apple, Banana, and Kiwi in D2:D5, preserving the order in which each value first appears:

With those seven fruit names in A2:A8 and an empty output area beginning at D2, enter =UNIQUE(A2:A8). The result is four distinct names, spilling from D2 through D5.

If any required output cell is occupied or otherwise unavailable, Excel cannot display the complete result and reports the error at the anchor cell. The result may change when the source data changes, so a range that is clear today can become blocked later.

How UNIQUE and dynamic arrays work

In Microsoft 365 and Excel 2021 or later, including Excel 2024, UNIQUE returns distinct rows or columns from a range or array. Its basic form accepts a source array, an optional choice to compare by column, and an optional setting to return values that occur exactly once. In ordinary lists, the default is to compare rows and return each distinct value once. The function recalculates as its source changes.

A key difference from older array formulas is that you enter a dynamic array formula in one cell. You do not select an output range and press Ctrl+Shift+Enter. Excel determines the result size and displays the values in adjacent cells. The anchor cell contains the formula; the other cells are part of its spill range and cannot be edited independently while the spill is intact.

With fruit names in A2:A8 and an empty output range beginning at D2, =UNIQUE(A2:A8,,TRUE) returns only values that appear exactly once. In this example, the result is Kiwi in D2, because Pear, Apple, and Banana each occur twice. The two commas intentionally leave the optional by-column argument blank and set the third argument to TRUE.

This distinction matters when troubleshooting: a valid formula can still show #SPILL! if the worksheet layout prevents the returned array from appearing. Conversely, a formula can spill successfully but return a value the user did not expect because the source range, comparison direction, or exactly-once setting is not what they intended.

Find the exact obstruction

Start with the cell showing #SPILL!, not with the source list. Select the error cell and look for Excel’s error indicator. Hover over it or open the error menu to read the specific explanation. Excel can identify common causes, such as a nonblank spill cell, a merged cell, or a formula placed in a table. The message narrows the search, but inspect the entire expected output area when needed.

Select the formula’s anchor cell and note the direction and likely size of the result. A one-column source generally produces a vertical list; a source with multiple columns can produce a two-dimensional result.
Inspect the cells where the returned values would go. Look for values, spaces, formulas that display an empty string, or other content. A cell that looks blank can still contain something that blocks the spill.
Check for merged cells in the intended output area and whether the formula sits inside an Excel Table.
Clear, unmerge, or relocate the relevant cells, then check whether the result appears. If the error remains, read the updated error message and investigate the next cause.

On Windows, pressing Ctrl+G opens Go To, where you can enter a cell address to inspect a distant part of the expected range. On Mac, use Control+G or F5, depending on the keyboard settings. These shortcuts help navigate; they do not remove the obstruction. Be careful with Delete: it removes cell contents, so confirm that the cells contain no information you need before clearing them.

Common causes and how to fix them

Occupied cells: A value or another formula in the output range is the most common practical cause. Move or clear the blocking content, or place the UNIQUE formula in a different area. If the blocker is important, do not overwrite it just to make the formula work.
Cells that look empty: A space, an apostrophe, or a formula returning an empty string can make a cell appear blank while still preventing a spill. Select the affected cell and inspect the formula bar. Clear the cell only if its contents are not needed.
Merged cells: A spill range cannot occupy merged cells. Unmerge the relevant cells using Home > Merge & Center > Unmerge Cells, or move the formula to a clear, unmerged area. Unmerging changes the layout, so check which cell retains any existing content.
Formula inside a Table: Dynamic array formulas that return multiple cells cannot spill from within an Excel Table. Put the UNIQUE formula in a normal worksheet range outside the table. It can still refer to table data as its source; the restriction concerns where the spilling formula is entered.
Not enough room on the sheet: A result cannot spill beyond the worksheet boundary. This can happen when a long list starts near the bottom of a column or a wide result starts near the right edge. Move the anchor cell to an area with adequate space.
Unpredictable or large results: If the number of returned values changes, the required spill range can expand into cells that were previously clear. Check the source size and leave a sensible buffer, or move the output to a dedicated sheet. A very large source can also make calculation slower even when it does not cause a spill error.

When the error message says the spill range is not blank, Excel may show or outline the blocking cell. Use that location as a clue rather than assuming that the visible first output cell is the only one involved. A block several rows below the formula can prevent the whole array from appearing.

A practical repair example

Suppose A2:A8 contains the fruit list described above, D2 contains the UNIQUE formula, and D4 contains the note “Review.” The expected results need D2:D5, so the note blocks part of the spill. Excel cannot return a partial list while leaving the note in place. Move the note to another cell, or put the formula in a different clear column. Once D2:D5 is available, Excel displays the four distinct fruit names.

If the output is meant to sit beside a table, keep the source data in the table and place the formula in an ordinary cell beyond its boundaries. For example, with fruit values in a table column named Fruit and an empty output area beginning at G2, enter =UNIQUE(Table1[Fruit]) in G2. This returns the distinct table values vertically, provided the spill area below G2 is clear and large enough. If your table has a different name or column heading, replace those references with the actual names shown in your workbook.

After a repair, add a new source value or remove a duplicate to confirm that the output updates as expected. This check can reveal a second obstruction farther down the range, or show that a result changing size is normal rather than an error. If the formula cell itself is inside the table, moving it outside is more reliable than trying to reserve empty cells within the table.

Errors, expected results, and useful checks

Do not confuse #SPILL! with a normal UNIQUE result containing fewer items than the source. Removing duplicates is the function’s job, so a shorter output is expected. Nor does #SPILL! mean Excel has deleted source data: it signals that the returned array cannot currently be displayed at the formula’s location.

Check the selected source range too. If it includes a header, the header can appear as one of the distinct results. If it includes blank cells, a blank may appear in the output. Those are range-selection or data-cleaning questions, not necessarily spill obstructions. Adjust the source reference if you want to exclude a header or unwanted blank values.

Also verify that the formula is in a version of Excel that supports dynamic arrays. UNIQUE is available in Microsoft 365 and Excel 2021 or later; Excel 2019 and older perpetual versions do not support it as a built-in worksheet function. In an unsupported version, the result may be a function-name error rather than #SPILL!. Excel for the web and current Mac versions support dynamic arrays, though ribbon appearance and keyboard behavior can differ from Windows.

Alternatives when a spill is not suitable

If you need a live, automatically updating list, the best fix is usually to make room for the spill or put the formula on a separate worksheet. This preserves the dynamic behavior and avoids maintaining a manually sized output range. A dedicated results sheet is especially useful when the source or number of distinct values grows over time.

For a one-time list, Excel’s Remove Duplicates command can create a static result, but it changes the selected data and does not keep the result linked to later source changes. Make a copy first if you need to preserve the original list. A PivotTable can also summarize distinct labels, but it is a different tool with refresh behavior and layout choices; it is not simply a replacement formula. Choose based on whether the result must update automatically, remain in a table, or be a fixed snapshot.

Frequently Asked Questions

Why does UNIQUE show #SPILL when the cells look blank?
A cell in the required output range may contain a space, an invisible character, or a formula that returns an empty string. Select the suspected cell and inspect the formula bar. Also check for merged cells and whether the formula is inside a Table.

Can UNIQUE spill inside an Excel Table?
No, a multi-cell dynamic array formula cannot spill from a cell inside a Table. Enter the formula in a normal worksheet range outside the table. The formula can still use a table column as its source.

How do I find which cell is blocking a UNIQUE result?
Select the anchor cell and read the #SPILL! message from Excel’s error indicator. For a blocked range, Excel may identify or outline the obstructing cell. Inspect the full expected spill area, including cells that appear empty.

Does UNIQUE remove duplicates from the original data?
No. It returns a separate array of distinct values and leaves the source list unchanged. If you want to permanently remove duplicate rows, use a data-cleaning command on a copy or use another workflow that suits your needs.

Why is UNIQUE unavailable in my Excel version?
UNIQUE is supported in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older versions such as Excel 2019 do not include the function, so use an alternative such as Remove Duplicates or a PivotTable for a static or summarized result.

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.