Why Is UNIQUE Not Working in Excel?

UNIQUE usually stops working because your Excel version does not support the function, its results cannot spill into nearby cells, or the formula’s range and options do not match what you mean by “unique.” In Microsoft 365 or Excel 2021 and later, check the error shown in the formula cell first, then confirm that the formula is in a clear area and refers to the intended data.

What the UNIQUE function does

UNIQUE is a dynamic-array function: you enter one formula, and Excel returns a list of distinct values into the cells below or beside it. It is useful for making a current list of customers, products, categories, or other entries without removing anything from the original data. When the source list changes, Excel can update the result automatically.

With names in A2:A8, where the values are Ana, Ben, Ana, Chen, Ben, Dev, and Chen, enter this formula in an empty cell outside the source list:

With names in A2:A8, the formula =UNIQUE(A2:A8) returns Ana, Ben, Chen, and Dev, in the order of their first appearances.

The function has three arguments: the array to examine, whether to compare columns instead of rows, and whether to return only entries that appear exactly once. The latter two arguments default to FALSE. That means a normal UNIQUE formula compares rows and returns one copy of each distinct value, even if it appeared many times in the source.

Check your Excel version first

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including supported versions of Excel for Mac and Excel for the web. It is not available in Excel 2019, Excel 2016, or earlier perpetual versions. If an older copy of Excel opens a workbook containing the function, it may show #NAME? or fail to calculate the formula. The formula can still work when the file is opened in a supported version.

To check your Windows version, go to File > Account and look under Product Information. The exact labels vary by license and update channel, but the important distinction is whether the app includes the dynamic-array UNIQUE function. On Mac, open Excel > About Microsoft Excel. Excel for the web supports UNIQUE when you open the workbook in a browser.

For a workbook that must work for people using older Excel versions, consider a PivotTable or Power Query rather than relying on UNIQUE. Those options take more setup, but they can provide a distinct-value list in versions that do not recognize the function.

Fix a #SPILL! error

#SPILL! means Excel understands the formula but cannot place all its results in the required output cells. For example, if a formula in D2 returns four names, D2:D5 must be available for the spill. The formula cell is the anchor; the other results are not separate formulas you need to copy down.

Click the error cell. Excel may outline the intended spill range and display an error menu that identifies the obstruction. Clear or move any content in that range, including a space or a formula that displays an empty string. A cell can look blank but still contain something that blocks the result. Merged cells in the spill area can also prevent an array from expanding.

Another common cause is placing a spilling formula inside an Excel Table. Dynamic-array results cannot spill within a table’s data body. Put the formula in a normal worksheet cell outside the table, or convert the table to a regular range if that is appropriate. You can still refer to the table’s columns from a formula outside it.

Before deleting anything, inspect the outlined cells. They may contain data you need. If the formula’s output should go somewhere else, move the formula to a larger clear area instead.

Make sure the formula is comparing the right thing

By default, UNIQUE compares rows. If the source is a vertical list, this is normally what you want. If your data is arranged across columns and you want to find distinct columns, set the second argument to TRUE. For a horizontal range in A1:F1, enter the formula in an empty cell with room to spill across:

With values across A1:F1, the formula =UNIQUE(A1:F1,TRUE) compares columns and returns distinct columns from that horizontal range.

For a two-dimensional range, the default comparison is by row: Excel returns distinct complete rows, not a separate unique list for every column. For example, if a range has a name in its first column and a region in its second, two rows with the same name but different regions are different rows. If you only want unique names, point the formula at the name column instead of the entire table.

There is also a difference between “one copy of every distinct value” and “values that occur only once.” With the same names in A2:A8 from the earlier example, enter this formula in a clear cell:

With names in A2:A8, the formula =UNIQUE(A2:A8,,TRUE) returns Dev only, because Ana, Ben, and Chen each appear more than once.

The third argument TRUE means exactly once; it does not mean “remove repeated copies but keep one.” Leave it out, or use FALSE, for the usual distinct list.

Investigate blanks, spaces, and inconsistent data

A blank in the source range can appear as an item in the result. If your selected range includes empty rows below the real data, the output may therefore include a blank entry. The quickest fix is to limit the source to the actual data rather than selecting an entire column. For a changing list, an Excel Table can make the source range expand as you add records, while the UNIQUE formula itself should sit outside the table.

Values that look identical may not be identical in the underlying data. One entry might have a trailing space, a nonbreaking space copied from a website, or a different data type. For example, a number stored as text can behave differently from the same number stored as a numeric value. UNIQUE does not automatically clean or standardize every source value before comparing it.

Check suspicious entries by editing the source cells, using the formula bar, or comparing their lengths. You can clean a separate helper column with functions such as TRIM for ordinary extra spaces and CLEAN for certain nonprinting characters, then run UNIQUE on that cleaned column. TRIM does not remove every kind of invisible character, including all nonbreaking spaces, so imported data may need additional cleaning.

Excel’s text comparisons are generally not case-sensitive for this purpose, so changing “North” to “NORTH” is not a reliable way to create a separate unique entry. If capitalization itself matters, UNIQUE alone is not a case-sensitive deduplication tool; you need a more specialized formula or data-cleaning approach.

Understand other errors and unexpected results

#NAME? usually points to an unsupported Excel version, a misspelled function name, or text that Excel is treating as an unknown name. Check that the formula begins with an equals sign and that the function is spelled UNIQUE. If colleagues use older Excel, ask them to open the workbook in a supported edition before concluding that the workbook is damaged.

If Excel displays the formula characters in the cell instead of calculating them, the cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then re-enter the formula. On Windows, select the cell, press F2, and then press Enter. On Mac, use Control+U to edit the active cell where supported, or click in the formula bar and confirm the formula again; keyboard mappings can differ by keyboard and Excel settings.

If the result does not update after the source changes, check Formulas > Calculation Options and choose Automatic if the workbook is set to Manual calculation. You can also recalculate with F9 on Windows; on Mac, the recalculation shortcut can vary with the keyboard and function-key settings, so use Formulas > Calculate Now if needed. A result that seems incomplete may also come from a source range that stops before the latest rows.

In regional settings that use semicolons as argument separators, Excel may require semicolons rather than commas when you enter formulas with multiple arguments. If a formula is rejected even though its structure is sound, check the separator used by formulas that already work in your installation.

Use UNIQUE with filters and external workbooks

UNIQUE can be combined with FILTER when you want distinct values that meet a condition, such as the customers in a particular region. The FILTER condition must return a compatible set of TRUE or FALSE values. If there are no matching rows, FILTER can return an empty-result error unless you provide its optional result-for-empty argument. This is a FILTER issue, not necessarily a problem with UNIQUE.

Dynamic-array formulas that refer to another workbook have an important limitation: linked array results may require the source workbook to be open. If it is closed, Excel can return #REF! when the formula needs to retrieve the array. For a dependable report, consider bringing the source data into the destination workbook or using Power Query to import and refresh it.

When combining UNIQUE with other functions, diagnose the pieces separately. Confirm that the input range returns the expected values, then check that any filter or sort function works on its own, and finally combine them. This makes it easier to tell whether the issue is the source data, the filter criteria, or the spill area.

Alternatives when UNIQUE is unavailable

If your Excel edition does not include UNIQUE, choose a method based on whether you need a one-time result or a refreshable report:

Remove Duplicates: Select a copy of the data and choose Data > Remove Duplicates. This changes the selected data, so work on a copy if you need to preserve the original list. It is useful for a one-time cleanup, not a live formula result.
PivotTable: Put the field you want to summarize in the Rows area. The resulting row labels provide a distinct list, and you can refresh the PivotTable after the source changes. This is often a good choice for summaries.
Power Query: Load the data with Data > From Table/Range, select the relevant column, and use Remove Rows > Remove Duplicates in the Power Query Editor. Choose Close & Load, then refresh the query when the source changes. Menu wording can vary slightly between Excel builds.
Advanced Filter: Excel’s Advanced Filter can copy unique records to another location. It is useful for a manual task, although it does not behave like a live spilling formula.

These methods do not all mean the same thing. Remove Duplicates edits data, while UNIQUE displays a formula-driven result. A PivotTable or Power Query is generally more suitable when the list is part of a repeatable reporting process.

Frequently Asked Questions

Why does UNIQUE show #NAME? in Excel?
Most often, the installed Excel version does not support UNIQUE. The function is available in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or earlier. Also check for a misspelling or a formula entered as text.

Why does UNIQUE return #SPILL!?
Excel cannot place the full result in the cells required. Clear obstructing values or formulas, check for merged cells, and move the formula outside an Excel Table’s data body. The formula needs space for every result it returns.

How do I keep one copy of each duplicate?
Use the normal form, such as =UNIQUE(A2:A20) with the source list in A2:A20. Do not set the third argument to TRUE; that option returns only values that occur exactly once.

Why is a blank showing in my UNIQUE results?
The source range probably includes one or more empty cells or rows. Narrow the range to the populated data, or use a maintained table column as the source. A formula that returns an empty-looking string can also affect what Excel treats as an entry.

Can UNIQUE distinguish upper- and lowercase text?
Not as a case-sensitive comparison. Excel generally treats text comparisons as case-insensitive, so entries that differ only in capitalization are not a dependable way to produce separate results. Use a case-sensitive method if that distinction is essential.

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.