Excel UNIQUE Formula Not Working? Fix Common Causes

If Excel’s UNIQUE formula is not working, check that you are using Microsoft 365 or Excel 2021 or later, that the formula begins with an equals sign, and that the cells where results should spill are clear. The most common errors are an unsupported Excel version, a blocked spill range, or source data that is not quite as consistent as it looks.

Check Whether Your Excel Version Supports UNIQUE

UNIQUE is a dynamic array function available in Microsoft 365 and Excel 2021 or later. It is not available in Excel 2019 or Excel 2016, so a workbook containing the function may show a name error such as #NAME? when opened in those versions. The formula may also appear as ordinary text if the cell is formatted as Text or the formula was entered without an equals sign.

On Windows, check your installed version under File > Account. In Microsoft 365, updates can add functions and fixes, so installing available Office updates may help if the version appears supported. Excel for the web supports UNIQUE as well. Excel for Mac has the function in supported Microsoft 365 and Excel 2021-or-later editions, although ribbon layout and update settings can differ. If a recipient uses an older Excel release, consider providing a values-only copy or a compatible alternative rather than assuming the formula will calculate for them.

Confirm the Formula and Its Arguments

For a basic list, UNIQUE takes a range or array and returns its distinct entries in the order they first appear. With names in A2:A8 and the result intended to start in C2, enter this in C2:

With names in A2:A8, enter this formula in C2: =UNIQUE(A2:A8). If A2:A8 contains Ana, Ben, Ana, Chi, Ben, the result is Ana, Ben, Chi, in that order.

Make sure the formula starts with =, the parentheses are balanced, and the reference points to the intended cells. If the formula displays literally instead of returning a result, select the cell, change its number format from Text to General using Home > Number Format, then re-enter the formula. Merely changing the format may not recalculate text already entered as a string; press F2 and then Enter on Windows to re-enter it. On Mac, use the equivalent cell-editing action, commonly Control+U, then Return, depending on keyboard settings.

Excel normally uses commas between function arguments. Some regional settings use semicolons instead. If Excel rejects a formula with commas, check the list separator configured for your system and use the separator Excel expects. Do not add quotation marks around a cell reference: the reference should point to cells, not be treated as text.

Understand What UNIQUE Returns

By default, UNIQUE compares rows when the source is more than one column wide. With product and category data in A2:B6, enter this in D2 to return each distinct product-category row once:

With product and category pairs in A2:B6, enter this in D2: =UNIQUE(A2:B6). If the pair Widget, Hardware appears twice, that pair appears once in the output; Widget, Office remains a separate row.

The optional second argument changes the comparison direction. With monthly values across A2:F2, enter this in A4 to return distinct columns horizontally:

With values in A2:F2, enter this in A4: =UNIQUE(A2:F2,TRUE). The result keeps the first occurrence of each distinct column value and spills across the row.

The optional third argument asks for entries that occur exactly once in the source, rather than one copy of each distinct entry. With names in A2:A8, enter this in C2:

With Ana, Ben, Ana, Chi, Dee, Dee, and Eli in A2:A8, enter this in C2: =UNIQUE(A2:A8,,TRUE). The result is Ben, Chi, and Eli because each occurs once. Ana and Dee are excluded because each appears more than once. This setting is a frequent source of confusion: it does not mean “show one copy of every duplicated item.”

Fix a Blocked Spill Range

UNIQUE is a dynamic array formula: one formula in the first output cell can return multiple results in neighboring cells. Excel calls the destination area the spill range. If any cell in that area is occupied, the formula may return #SPILL! instead of the list. Look for existing values, formulas that return an empty-looking string, spaces, or merged cells in the expected output area. Clear or move the obstruction, and the results should spill automatically.

Select the cell showing #SPILL! to see Excel’s error indicator and, where available, information about the obstruction. Ensure there is enough room below the formula for a vertical list or to its right for a horizontal result. A spill range cannot expand inside an Excel Table, so place the UNIQUE formula in the worksheet grid outside the table. The source data can be in a table; it is the formula’s output area that must not be inside one.

Spill references can also affect later formulas. If the UNIQUE formula is in C2, other formulas can refer to its full current output with C2 followed by the spill operator, written as C2#. That reference expands or contracts as the unique list changes. If a copied workbook has a blocked spill range, clear the obstruction rather than copying the formula repeatedly down the column.

Check Blanks, Spaces, and Inconsistent Data

Values that look identical may not be identical to Excel. A name can contain a leading space, a trailing space, a nonbreaking space copied from a website, or a different punctuation mark. Numbers stored as text may also behave differently from numeric values. UNIQUE compares the underlying cell values; it does not automatically clean or standardize them. Check a few source cells in the formula bar and compare their formats and contents.

To remove ordinary extra spaces from a single cell, use a helper column. With the original text in A2, enter this in B2 and fill it down:

With the uncleaned value in A2, enter this in B2: =TRIM(A2). Then use the cleaned helper range as the source for UNIQUE. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one; it does not remove every special whitespace character that may come from imported data.

Blank cells in the source can produce a blank entry in the unique results. If the source is A2:A20 and you want to omit blanks, enter this in C2:

With source values in A2:A20, enter this in C2: =UNIQUE(FILTER(A2:A20,A2:A20<>"")). This filters out cells that are empty or return an empty string before listing distinct values. If the filter finds no matching entries, FILTER can return #CALC! unless you provide its optional if-empty argument; check that the source actually contains a nonblank value.

Use a Reliable Source Range

A reference that is too short can make the formula seem unreliable: new records added below a fixed range will not be included. In Microsoft 365 or Excel 2021 and later, a structured table reference can expand as table rows are added. With a table named Sales and a column named Customer, enter this outside the table:

With customer names in the Customer column of a table named Sales, enter this in a worksheet cell outside the table: =UNIQUE(Sales[Customer]). The formula returns the distinct customer values from that table column and includes new table rows as the table grows.

Check that the source reference is on the intended worksheet and includes the whole data set, but avoid including headers unless you want the header returned as an entry. If the formula uses an entire column, the large number of blank cells can make a blank result appear; a table column or a suitable bounded range is often clearer.

Diagnose Calculation and Error Messages

If a result does not update after the source changes, check calculation settings. In Excel for Windows, use Formulas > Calculation Options > Automatic. If the workbook is set to Manual, formulas may wait until recalculation. Also check whether the source cells themselves contain formulas that have not recalculated.

#NAME?: verify that the Excel version supports UNIQUE and that the function name is spelled correctly.
#SPILL!: clear cells or merged cells in the output area, or move the formula outside an Excel Table.
#CALC!: check whether a nested function such as FILTER has returned no results.
A formula shown as text: change the cell from Text to General and re-enter the formula with an equals sign.
Unexpected extra entries: inspect the source for spaces, blank cells, different punctuation, or values stored in different formats.

When the formula works in a new blank cell but not in its original location, the problem is often the output area or the original cell’s format. When it fails in a new cell too, check version support, the formula’s range, and the source data before rebuilding the workbook.

Alternatives When UNIQUE Is Unavailable

If you have Excel 2019 or an earlier release, use a method that your version supports. Data > Remove Duplicates can permanently remove repeated records from a selected range, so copy the source first if you need to preserve it. This command changes the data; it is not a live formula result that updates as new records arrive.

Advanced Filter can copy unique records to another location without using the UNIQUE function. In Windows Excel, find it under Data > Advanced in the Sort & Filter group. Select the source range, choose Copy to another location, specify the destination, and select Unique records only. The exact dialog presentation can vary by release. Excel for Mac also offers filtering tools, but their placement and available options can differ. For a list that needs to refresh dynamically, upgrading to an edition with dynamic arrays or using a compatible data workflow is more suitable than repeatedly removing duplicates by hand.

Frequently Asked Questions

Why does UNIQUE return #NAME?
Most often, the workbook is open in Excel 2019 or an older version that does not support the function. Confirm the installed Excel edition, then open the file in Microsoft 365, Excel 2021 or later, or Excel for the web.

Why does UNIQUE show #SPILL?
Excel cannot place the full result in the cells needed for the output. Clear any occupied cells or merged cells in that spill area, or move the formula to an open area outside a table.

Can UNIQUE ignore blank cells?
Not by itself. Filter the source to remove blanks before passing it to UNIQUE, as in the FILTER example above. If every source cell is blank, account for FILTER’s no-results behavior.

Does UNIQUE distinguish uppercase and lowercase?
UNIQUE’s standard comparison is not case-sensitive, so entries differing only in letter case are generally treated as the same value. If capitalization itself must define a distinct entry, you need a case-sensitive approach rather than relying on the basic function.

Why are two entries that look the same both listed?
They may contain different underlying characters, such as a trailing space, a nonbreaking space, or different punctuation. Inspect or clean the source values before generating the distinct list.

Will a UNIQUE list update when I add data?
It updates when the formula’s source reference includes the new data and Excel recalculates. A table column reference can expand with new table rows, while a fixed range will not include entries added beyond its last referenced cell.

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.