Excel UNIQUE Function Not Working? Fix Common Errors

If Excel’s UNIQUE function is not working, first check that your Excel version supports dynamic arrays, then inspect the formula’s spill area for anything blocking the results. In Microsoft 365, Excel 2021 or later, and Excel for the web, a basic test with names in A2:A10 is =UNIQUE(A2:A10); enter it in an empty cell and let Excel fill the results below.

Start with the error message

The message or result usually points to the cause. A #NAME? error commonly means Excel does not recognize UNIQUE, often because the workbook is open in an older Excel version. A #SPILL! error means Excel cannot place the full result in the cells it needs. If the formula returns a list but still shows apparent duplicates, the source values may not actually match: spaces, hidden characters, or different data types can make two entries look alike.

Try these checks in order:

Confirm that the formula is spelled UNIQUE and that the workbook is open in a supported version.
Click the formula cell and inspect any spill warning or outlined spill range.
Check the source values for extra spaces, nonprinting characters, or text-versus-number differences.
Review the optional UNIQUE arguments to make sure they match the result you want.

UNIQUE is a dynamic array function: one formula can return multiple results into neighboring cells. You generally enter it once, not once per row, and you do not need to press Ctrl+Shift+Enter. That behavior is different from older array formulas.

Check Excel version and function support

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024, as well as Excel for the web. It is not available as a normal worksheet function in older perpetual releases such as Excel 2019 or Excel 2016. If an unsupported version opens a workbook containing the function, it may display an unrecognized-function error or a compatibility form such as _xlfn.UNIQUE.

On Windows, check the version under File > Account, then look at the Product Information area. On Mac, use Excel > About Microsoft Excel. Excel for the web runs in a browser and supports UNIQUE. The ribbon layout differs on Mac and in the web app, but the formula itself is the same. Updating an older installation will help only if your license includes a version that supports dynamic arrays.

If you need to share the workbook with someone using an older release, the formula may not calculate for them. Consider sending a static copy of the results or using an older-version method, such as Advanced Filter, rather than assuming the recipient can use UNIQUE.

Understand the syntax and options

The function syntax is =UNIQUE(array,[by_col],[exactly_once]). The first argument is the range or array to examine. The second tells Excel whether to compare columns instead of rows; the third asks for values that occur exactly once. Both optional arguments default to FALSE.

With product names in A2:A20, the basic formula returns each distinct name once, even if it occurs many times: =UNIQUE(A2:A20). With product names in A2:A20 and a requirement to show only names appearing exactly once, use =UNIQUE(A2:A20,FALSE,TRUE). For example, if the range contains Tea, Coffee, Tea, and Juice, that formula returns Coffee and Juice, not Tea.

By default, UNIQUE compares rows. If your values run horizontally across A1:F1 and you want distinct columns, use =UNIQUE(A1:F1,TRUE). For a table of records, such as names in A2:A20 and departments in B2:B20, use =UNIQUE(A2:B20) to return distinct name-and-department row combinations. A person in two departments counts as two different rows.

Fix a #SPILL! error

UNIQUE needs a clear output area because it may return more than one value. If a cell that the results need is occupied, Excel displays #SPILL!. Select the error cell and use the warning icon or inspect the outlined spill range to locate the blockage. Clear the obstructing cells, move the formula, or create enough empty space for the result. Even a cell containing a space or a formula that displays an empty string can prevent a spill.

Merged cells in the output area can also block a dynamic array. Unmerge them or place the formula somewhere with an unobstructed range. If the formula is inside an Excel table, move it outside the table: spilled array formulas cannot spill within table cells. A formula outside the table can still use a structured reference. With a table named Sales and a column named Region, enter =UNIQUE(Sales[Region]) in an empty cell outside that table.

Do not fill the UNIQUE formula down as if it were a conventional row-by-row formula. The first formula creates the entire spill range, and copying it down can cause overlapping results or new spill errors. To refer to all results elsewhere, with the formula in D2, use the spill reference =D2# in another open area.

Find duplicates caused by messy source data

UNIQUE removes repeated values only when Excel considers them equal. A trailing space in “North ”, for example, can make that entry different from “North”. Nonbreaking spaces copied from web pages are another frequent source of look-alike values. CLEAN can remove certain nonprinting characters, but it does not remove every kind of whitespace; SUBSTITUTE with CHAR(160) can handle the common nonbreaking space.

With entries in A2:A100, this formula cleans common control characters, replaces nonbreaking spaces, trims extra ordinary spaces, and then returns distinct cleaned entries: =UNIQUE(TRIM(SUBSTITUTE(CLEAN(A2:A100),CHAR(160)," "))). Review the cleaned results before relying on them, because trimming changes the text. If the original values are important, create a helper column rather than overwriting the source.

Numbers stored as text can also differ from actual numbers, even when they appear identical on screen. Check alignment, number formats, and how the values were imported. A format change alone does not convert text into a number. If you need a dependable comparison, normalize the source data deliberately in a helper column and check a few converted values before applying UNIQUE.

Excel’s ordinary text comparisons are not case-sensitive for this purpose, so capitalization alone generally will not preserve separate entries such as “west” and “West”. If case-sensitive distinctions matter, UNIQUE by itself is not a case-sensitive deduplication tool; you will need a different approach that explicitly compares character case.

Exclude blanks or sort the results

A source range can include empty cells, and formulas that return an empty string can look blank while still being part of the range. To omit blank-looking entries, with values in A2:A100, use =UNIQUE(FILTER(A2:A100,A2:A100<>"","")). FILTER keeps values that are not empty strings before UNIQUE processes them. Its third argument supplies an empty result if there are no matching values, avoiding a no-results error from FILTER.

UNIQUE does not promise an alphabetical sort; it returns distinct values in the order they first appear in the source. If you want an alphabetical list, with entries in A2:A100, use =SORT(UNIQUE(A2:A100)). To sort a filtered unique list, with the same layout, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"",""))). Sorting changes the display order, not which entries are considered duplicates.

Check calculation and workbook behavior

UNIQUE normally recalculates when its source data changes. If results appear stale, check whether calculation is set to Manual: on Windows, select Formulas > Calculation Options > Automatic. The ribbon path is similar in current desktop Excel for Mac, though its placement can vary with the ribbon layout. Press F9 on Windows to recalculate; on Mac, the recalculation shortcut can depend on the keyboard and Excel settings, so the Formulas ribbon command is a reliable alternative.

Dynamic array formulas that refer to another workbook can have limitations, particularly when the source workbook is closed. If an external reference produces an error, open both workbooks and recalculate, or bring the source data into the workbook containing the UNIQUE formula. Also verify that the formula points to the intended range: expanding a range to include a new row can change the results, while a fixed range may omit data added below it.

For a growing dataset, an Excel table can make the input reference easier to maintain. Put the UNIQUE formula outside the table and use the table’s column reference, such as =UNIQUE(Sales[Region]). New rows added to the table are included in its structured reference, while the output still spills into ordinary worksheet cells.

Alternatives when UNIQUE is unavailable

If you cannot use a supported Excel version, the best alternative depends on whether you need a live formula or a one-time cleaned list. Data > Sort & Filter > Advanced opens Advanced Filter, where you can choose to copy results to another location and select Unique records only. This is useful for a static list, but it does not behave like a formula that updates automatically when the source changes.

Data > Remove Duplicates is another built-in option for cleaning a data range. It removes duplicate rows from the selected data, so make a copy first if you need to preserve the original list. A PivotTable can also summarize distinct category labels, though it is a report rather than a direct replacement for a spilling formula. For an updating result in a newer Excel version, UNIQUE is usually the more direct choice.

Frequently Asked Questions

Why does Excel say UNIQUE is not a valid function?
Most often, the workbook is open in Excel 2019, Excel 2016, or another version without dynamic array support. Check File > Account on Windows or Excel > About Microsoft Excel on Mac, and use a supported version or an alternative such as Advanced Filter.

Why does UNIQUE return #SPILL!?
One or more cells needed for the results are not empty, the output area contains merged cells, or the formula is inside an Excel table. Clear or move the obstruction and enter the formula outside the table.

Why are similar-looking values still duplicated?
The source entries may contain leading or trailing spaces, nonbreaking spaces, hidden characters, or a text-versus-number difference. Clean or normalize the source values, then run UNIQUE again.

Does UNIQUE sort the results alphabetically?
No. It returns distinct values in the order they first occur. Wrap it in SORT when you want an ordered list, for example, with entries in A2:A100: =SORT(UNIQUE(A2:A100)).

Can I put a UNIQUE formula inside an Excel table?
A dynamic array formula cannot spill within a table. Enter the formula in a normal worksheet range outside the table, and refer to a table column if you want the source to expand as table rows are added.

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.