Excel UNIQUE Formula Not Working? Fix Common Issues

If Excel’s UNIQUE formula is not working, first check that you have Microsoft 365 or Excel 2021 or later, that the formula begins with =UNIQUE, and that the cells where the results need to spill are empty. Then check the source range for errors, unexpected spaces, or data that only looks duplicated; those are among the most common reasons the result is wrong or an error appears.

What the UNIQUE function does

UNIQUE returns distinct values or rows from a range or array. In a vertical list, it normally returns one copy of each different value, preserving the order in which values first appear. It is a dynamic array function: enter the formula once, and Excel places the results in neighboring cells automatically. The first cell containing the formula is the top-left cell of that output area, often called the spill range.

For example, suppose A2:A7 contains Pear, Apple, Pear, Plum, Apple, and Orange. With values in A2:A7, this formula in C2 returns Pear, Apple, Plum, and Orange in C2:C5:

With the source list in A2:A7, enter in C2: =UNIQUE(A2:A7)

The formula does not remove duplicates from the original list. It creates a separate result, so the source stays unchanged. That distinction matters when you use the output for a report or dropdown: changes to the source can update the results, but the original records remain available.

Check your Excel version and formula entry

UNIQUE is available in Microsoft 365 and Excel 2021 or later. It is not available as a built-in worksheet function in Excel 2019 or earlier. If Excel displays #NAME?, confirm that the function name is spelled correctly and check the version or update status. In an older version, opening a workbook that contains UNIQUE may not produce a usable formula result.

Enter the formula in a normal worksheet cell and press Enter. In Microsoft 365 for Windows, Excel should show a single formula in the first cell and populate the neighboring cells with the results. Do not select a whole output range and press Ctrl+Shift+Enter: UNIQUE is a dynamic array function and does not need the legacy array-formula shortcut.

In some regional settings, Excel uses semicolons rather than commas between function arguments. That difference affects formulas with more than one argument, not the single-argument example above. Excel may also show localized function names in some language versions. If you copied a formula from a website, re-enter it using the separators and function naming that your installation expects.

Resolve a #SPILL! error

#SPILL! means Excel cannot place the complete result in the cells it needs. The formula may be correct, but something is blocking the spill range. Click the error cell: Excel usually outlines the intended output area and provides an error explanation. Clear or move any content in those cells, including a space that looks blank, or move the formula to an area with enough room.

Look for existing values or formulas in the spill area and clear them if they are not needed.
Check for merged cells in the intended output range; unmerge them or choose another location.
Make sure the result will not run beyond the worksheet edge.
If the formula is inside an Excel Table, place it outside the table. Dynamic array formulas cannot spill inside table cells.

Only the formula’s first cell is editable as the source formula. The other cells in the spill range display results but cannot be edited individually; trying to type over one can produce a spill-related error. To move the output, cut or re-enter the formula in a cell with clear space. You can refer to the full result elsewhere with the spill-range operator, such as C2#, if C2 contains the UNIQUE formula.

Understand the optional arguments

UNIQUE has two optional settings. The first, by_col, tells Excel whether to compare columns instead of rows. Its default behavior compares rows. The second, exactly_once, changes the result to include only values that occur exactly once. These settings solve different problems: exactly_once is not simply another way to remove duplicates.

With the list Pear, Apple, Pear, Plum, exactly_once set to TRUE returns Apple and Plum, because each appears only once. With the source list in A2:A5, enter in C2: =UNIQUE(A2:A5,,TRUE)

In this example the result is Apple and Plum. The empty argument between the two commas leaves by_col at its default. If instead you want the distinct values including one Pear, leave out the third argument and use the basic formula.

For a table of records, UNIQUE compares entire rows by default. Two rows count as duplicates only when their corresponding values match across the compared columns. If you want to compare items arranged across columns rather than down rows, set by_col to TRUE. Check the direction of your data before assuming the function is ignoring duplicates.

Find duplicates that look identical but are different

UNIQUE compares cell values, not just what your eyes see on the screen. A trailing space, a nonbreaking space copied from a web page, a different punctuation mark, or a different underlying value can make two entries distinct. Conversely, text comparisons in Excel’s UNIQUE function are generally case-insensitive, so Pear and PEAR are not usually treated as separate values just because their capitalization differs.

Use a temporary helper column to inspect or clean text rather than changing original records without a backup. TRIM removes ordinary leading and trailing spaces and reduces repeated spaces between words; CLEAN removes many nonprinting characters. It may not remove every nonbreaking space copied from web content, so inspect troublesome data when the results still seem unexpected.

With original text in A2:A20, enter this in B2 and fill it down to create cleaned values: =TRIM(CLEAN(A2))

Then, with the cleaned values in B2:B20, enter in D2: =UNIQUE(B2:B20)

The result is a distinct list based on the cleaned helper values. Use caution with cleanup: spaces can be meaningful in some codes or identifiers. Also check whether values that look the same are actually stored differently, such as a number stored as text versus a numeric value, or dates that have different underlying times. Formatting can make different values look alike without making them equal.

Check source data, blanks, and errors

A formula can return a surprising result because the source range includes more than the intended records. Selecting A:A, for example, includes the entire column, including many empty cells. A blank source cell may appear as a blank or as a zero in a result depending on the data and formula context. Use a range that matches the actual data, or convert the source into a Table and refer to its data column so new records are included more reliably.

Errors in the source can also affect the result. If an included cell contains an error such as #N/A, UNIQUE may pass that error into its output instead of producing a clean list. Find and correct the source error, or deliberately exclude or handle erroneous rows before generating the distinct list. Avoid hiding errors without understanding them, particularly when the results support a business report.

If you are combining UNIQUE with FILTER, an empty filtered result can produce #CALC! unless you provide an if_empty result to FILTER. That is a FILTER issue rather than evidence that UNIQUE is unavailable. Check the criteria and whether any rows actually match before changing the unique-list formula.

Why sorting and source updates may seem wrong

UNIQUE preserves the order of first occurrence; it does not automatically sort the output. If you expect alphabetical results, the unsorted list is not a failure. In Microsoft 365 or Excel 2021 and later, you can wrap UNIQUE in SORT. With source values in A2:A20, enter in C2: =SORT(UNIQUE(A2:A20))

The formula returns the distinct values in ascending order. If you add or edit values inside the referenced range, the result recalculates automatically. However, a formula referring only to A2:A20 will not include a new entry typed in A21. Expand the range or use a Table-based reference when the source is expected to grow.

If the output does not refresh after a source change, check that calculation is set to Automatic. In Excel for Windows, use Formulas > Calculation Options > Automatic. Excel for Mac has a corresponding Calculation Options control on the Formulas tab; the exact ribbon layout can vary by release. Manual calculation can leave formulas showing old results until recalculation. Press F9 on Windows to recalculate; on Mac, use the recalculation shortcut shown for your Excel version or choose Calculate Now on the Formulas tab.

Use UNIQUE in a Table, dropdown, or another workbook

Excel Tables are useful for maintaining source data because structured references can expand as rows are added. The UNIQUE formula itself should be entered outside the Table so it has room to spill. For example, you can put a distinct-list formula in a separate worksheet area and reference the Table’s column there. Confirm that the table and column names match your workbook.

For a dropdown list, the Data Validation source field does not always accept a dynamic array formula directly. A common approach is to put UNIQUE outside the Table, then use the spilled range as the validation source, for example by referencing the first result cell with a # spill reference. If Excel rejects that reference in the dialog, create a defined name that refers to the spill range and use that name as the list source. The exact setup depends on workbook structure and Excel version.

Dynamic arrays linked to another workbook have a limitation: the source workbook may need to be open for the linked dynamic-array formula to update correctly. If an external UNIQUE formula shows #REF! after the source workbook is closed, open both files and consider keeping the source data in the same workbook or using a different import workflow.

Alternatives for older Excel versions

If you have Excel 2019 or earlier, there is no built-in UNIQUE function. You can use Data > Remove Duplicates to create a one-time distinct list, but this changes the selected data, so copy the source to a separate area first if you need to preserve it. Advanced Filter can also copy unique records to another location, depending on the version and the selected range.

For a list that must update automatically in an older version, options include a PivotTable or a more complex legacy formula approach. A PivotTable is often easier to maintain for summaries, though it may need refreshing after source data changes. Avoid replacing the source data with a manual deduplicated copy if you need a traceable record of every original entry.

Frequently Asked Questions

Why does Excel say UNIQUE is not a valid function?
Most often, the workbook is being opened in Excel 2019 or an earlier version, or the function name or argument separators do not match the installation. UNIQUE is supported in Microsoft 365 and Excel 2021 or later. Check your version and update Excel where possible.

Why does UNIQUE return a #SPILL! error?
One or more cells in the output area are occupied, merged, or otherwise preventing the array from expanding. Clear the blocking cells or move the formula to a larger empty area. A UNIQUE formula inside an Excel Table also cannot spill; place it outside the Table.

Why are duplicate names still appearing?
The entries may contain extra spaces or invisible characters, may have different underlying values, or may be different complete rows rather than duplicate names. Check the exact source range and try a cleaned helper column. Remember that UNIQUE compares entire rows when the input has multiple columns.

Can UNIQUE return values that occur only once?
Yes. Set the third argument, exactly_once, to TRUE. The result excludes values that occur two or more times; it does not keep one copy of each repeated value. Leave that argument out when you want one copy of every distinct value.

Does UNIQUE sort the results automatically?
No. It returns distinct values in order of first appearance. If you need sorted output, combine it with SORT in Microsoft 365 or Excel 2021 and later, and confirm the sort order is appropriate for your data.

Why does my unique list not include new rows?
The formula may refer to a fixed range that ends before the new records. Expand the referenced range or use a Table column reference. Also check that calculation is not set to Manual if changes are not appearing after the source range updates.

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.