Why UNIQUE Is Not Working in Excel

Excel’s UNIQUE function usually fails because the installed Excel version does not support it, the formula’s spill range is blocked, or the source data is not arranged as expected. Check the error shown in the formula cell first: #NAME? often points to an unsupported version or a misspelled function, while #SPILL! means Excel cannot place the results in the cells it needs.

What UNIQUE does—and which versions support it

UNIQUE returns distinct values or rows from a range as a dynamic array. Instead of copying a formula down a column, you enter it once and Excel spills the results into neighboring cells. The list can change automatically when the source data changes. The function is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual versions such as Excel 2019 and Excel 2016 do not support it.

Its syntax is UNIQUE(array,[by_col],[exactly_once]). The first argument is the range or array to examine. The optional second argument determines whether Excel compares columns rather than rows; the third determines whether to return every distinct item or only items that occur exactly once. In the usual case, omit both optional arguments.

For example, with names in A2:A8 in this order—Mia, Leo, Mia, Ava, Leo, Noor, Ava—the formula =UNIQUE(A2:A8) returns Mia, Leo, Ava, and Noor, in the order each first appears. It removes repeated entries from the result; it does not delete or change the original cells.

Check your Excel version and the error message

If Excel displays #NAME?, first confirm that the formula is spelled UNIQUE and begins with an equals sign. If the formula is correct, the most likely explanation is that the workbook is open in a version of Excel without the function. On an older version, Excel may show _xlfn.UNIQUE or report that the function is not recognized. Updating Office will help only if your license and update channel provide a supported version; otherwise use one of the alternatives below.

In Microsoft 365 for Windows, check the installed product under File > Account, then look at the product information and update options. The exact labels can vary with your organization’s update policy. Excel for Mac and Excel for the web also support UNIQUE when the account or installed edition includes the function. If you need to share the workbook with someone using an older Excel release, tell them that the formula may not calculate there; consider sending a static copy of the results or using a compatible method.

A formula showing as literal text rather than calculating is a different problem. The cell may be formatted as Text, or the formula may have an apostrophe before the equals sign. Change the cell format to General, then edit and re-enter the formula. Changing the format alone does not always convert a formula already stored as text.

Fix a blocked spill range

#SPILL! means Excel recognizes the dynamic-array formula but cannot display its full result. UNIQUE needs empty cells below or beside the formula, depending on the result’s orientation. Select the error cell and, where available, use the error indicator to inspect the spill range Excel identifies. Remove or move anything obstructing that range, then check again.

Common obstructions include:

Existing values, formulas, or even a space in a cell where a result needs to appear.
Merged cells in the intended output area.
A formula placed inside an Excel Table. Dynamic-array results cannot spill within a table, so put the formula in a normal worksheet range outside the table.
A spill area that runs into the worksheet edge or another part of the layout that cannot accept the result.

Do not clear the source range just because it contains duplicates. The source values are inputs; the output needs its own unobstructed space. If you want the list next to a table, leave enough blank cells for it to grow as new distinct values appear.

Make sure the formula uses the intended range

A formula can work correctly and still appear wrong if its input includes unintended cells. With a heading in A1 and names in A2:A8, =UNIQUE(A1:A8) treats the heading as a value and includes it in the result. Use =UNIQUE(A2:A8) to exclude it. Likewise, a range that stops at row 100 will not include a new entry in row 101 unless the formula’s input is expanded.

UNIQUE returns distinct rows by default when the input has multiple columns. With names in A2:A5 and departments in B2:B5, =UNIQUE(A2:B5) returns distinct name-and-department combinations, not just distinct names. If Mia appears once in Sales and once in Support, those are different rows and both remain. To get names alone, use the one-column range A2:A5.

The function does not automatically ignore filtered-out or hidden records. A filter may make some source rows invisible, but UNIQUE still evaluates the range supplied to it. Also, a blank included in the source can appear once in the output. Check the selected range for blank cells, headers, totals, or records hidden by a filter before assuming Excel has invented an item.

Choose the right UNIQUE options

By default, UNIQUE returns one copy of each distinct value, including values that appeared multiple times in the source. The optional exactly_once argument changes that behavior: set it to TRUE when you want only values that occur once and want to exclude values that repeat.

With product codes in A2:A7—P10, P20, P10, P30, P40, P30—the formula =UNIQUE(A2:A7,FALSE,TRUE) returns P20 and P40. It does not return one copy of P10 and P30, because those items occur more than once. This distinction is a frequent source of confusion: ordinary deduplication and finding single-occurrence items are different tasks.

For a range arranged horizontally, set by_col to TRUE so Excel compares columns. For a vertical list, leave it FALSE or omit it. If you want the distinct results in alphabetical or numerical order, combine UNIQUE with SORT. With scores in A2:A8, =SORT(UNIQUE(A2:A8)) returns the distinct scores in ascending order. Without SORT, UNIQUE preserves the order in which each distinct item first appears.

Look for differences in the source data

Entries that look alike on screen may not be identical. A name can contain a leading or trailing space, a nonbreaking space copied from a website, or a hidden character. As a result, two apparent copies may be treated as distinct. Conversely, ordinary differences in letter case are generally not enough to make text values distinct: UNIQUE is not a case-sensitive way to separate entries such as Mia and MIA.

Inspect suspicious cells by editing them or comparing their lengths. You can also make a cleaned helper column before applying UNIQUE. For instance, with imported names in A2:A20, enter =TRIM(A2) in B2 and fill it down, then use UNIQUE on B2:B20. TRIM removes extra ordinary spaces but does not remove every kind of nonprinting or nonbreaking character, so imported data may need additional cleaning. Keep the original column until you have checked the cleaned output.

Numbers stored as text can also behave differently from numeric values in calculations and sorting. If a list mixes imported text with real numbers, check the cell types and standardize them before drawing conclusions from the result. A number format can make values look alike without changing the underlying value. UNIQUE reports distinct entries; it does not repair inconsistent data.

Check calculation, separators, and formula entry

If the source changes but the displayed list does not, check whether calculation is set to Automatic. In Excel for Windows, go to Formulas > Calculation Options > Automatic. In Manual mode, formulas may wait for recalculation; press F9 to recalculate the workbook. This is less common than a blocked spill range, but it can make an otherwise valid result look stale.

Excel normally uses commas between function arguments in English-language settings. Some regional settings use semicolons instead. If Excel rejects a formula copied from a comma-based example, check the argument separator expected by your installation rather than changing the function name. Enter the formula in a cell and confirm that Excel accepts it; a separator mismatch usually produces a formula-entry error, not a UNIQUE-specific result.

Also confirm that the formula is in the cell you think it is and that it starts with =. A leading space, apostrophe, or text format can prevent calculation. If the formula appears correct but the result is an error, read the precise error code: #REF! can indicate an invalid reference, whereas #VALUE! may point to an issue in the supplied array or an enclosing formula. The error code is more useful than treating every failure as a problem with UNIQUE itself.

Alternatives when UNIQUE is unavailable

If the workbook must work in Excel 2019 or an earlier release, use a method supported by that version. For a one-time cleanup, select the source data and choose Data > Remove Duplicates. This removes duplicate records from the selected data, so make a copy first if you need to preserve the original list. It is a destructive operation, unlike UNIQUE, which produces a separate result.

For a distinct list that you can refresh, use the Advanced Filter feature. Select the source range, go to Data > Advanced, choose Copy to another location, specify the destination, and select Unique records only. The wording or dialog layout can vary slightly by Excel release. Unlike a dynamic-array formula, an Advanced Filter result does not automatically update whenever the source changes; run the filter again after the data changes.

Power Query is another option for recurring imports or larger cleanup tasks. Load the data into Power Query, select the relevant column or columns, and use the remove-duplicates command before loading the result back to Excel. It offers a repeatable transformation process, but the result updates when the query is refreshed rather than recalculating as a worksheet spill formula.

Frequently Asked Questions

Why does Excel say UNIQUE is not a valid function?
Usually the workbook is open in an Excel version that does not include dynamic-array functions, or the function name is misspelled. UNIQUE is supported in Microsoft 365 and Excel 2021 or later. Check the Excel edition and update availability, or use Advanced Filter, Remove Duplicates, or Power Query.

Why am I getting a #SPILL! error with UNIQUE?
Excel cannot place all the results in the required cells. Clear obstructing content, unmerge cells in the output area, and move the formula outside any Excel Table. The original source range can remain unchanged.

Does UNIQUE ignore blank cells or filtered rows?
A blank in the input range can appear once in the result, and filtered-out or hidden rows are still evaluated. Use a range that excludes unwanted blanks or records if you do not want them represented.

How do I return only items that appear once?
Set the third argument, exactly_once, to TRUE. For a vertical list in A2:A20, =UNIQUE(A2:A20,FALSE,TRUE) returns entries with exactly one occurrence and excludes repeated entries entirely.

Can I use UNIQUE inside an Excel Table?
A dynamic-array formula cannot spill its results inside a table. Enter the formula in a regular worksheet range outside the table, with enough empty cells for the list to expand.

Why does UNIQUE show two copies that look the same?
The source entries may differ because of spaces, nonprinting characters, or inconsistent imported values. Check and clean the underlying data; formatting alone may only make different values look alike.

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.