Excel UNIQUE Function Not Showing? Causes and Fixes

Formulas & functions · Updated November 18, 2026

If Excel does not recognize or display UNIQUE, first check your version: the function is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or earlier. In a supported version, enter a formula such as =UNIQUE(A2:A8) in an empty cell; if the formula is recognized but the results do not appear, check for a blocked spill range.

What it means when UNIQUE is not showing

The phrase can describe several different problems. UNIQUE might be missing from Excel’s function suggestions, Excel might reject it with a name error, or the formula might be accepted but fail to display its results. These symptoms have different causes, so identify what happens after you type the function before changing your workbook.

  • No suggestion appears while typing: autocomplete may be off, the function name may not have been typed correctly, or your Excel version may not include it.
  • You see a name error: the installed Excel version may not support UNIQUE, or the formula may contain a spelling or syntax error.
  • The formula is accepted but shows an error or only one visible result: a spill obstruction, a table, or another layout issue may be involved.

UNIQUE is a dynamic-array function. It returns distinct items from a range and, when more than one result is found, places the results in cells below or beside the formula automatically. It is not an add-in that you need to install separately.

Check whether your Excel version supports UNIQUE

Microsoft 365 and Excel 2021 or later include UNIQUE. Excel 2019, Excel 2016, and earlier perpetual versions do not. Excel for the web also supports the function. A workbook created in a newer version can contain a UNIQUE formula even if an older installation cannot calculate it, so receiving a file from someone else does not guarantee that your copy of Excel can use every formula in it.

On Windows, check the installation by opening File > Account and looking at the product information and version. If you have Microsoft 365, install available updates using File > Account > Update Options > Update Now. On Mac, check the version in Excel’s About window and use Help > Check for Updates to open Microsoft AutoUpdate. The exact wording can vary slightly with the installed release.

Updating will not add UNIQUE to an unsupported perpetual edition such as Excel 2019. If your computer is managed by an employer or school, updates may be controlled by an administrator, and a Microsoft 365 installation may not receive every feature at the same time. Confirm the product edition as well as the version number before assuming that a formula is broken.

Enter the function correctly

UNIQUE has three arguments: the source range or array, an optional setting for comparing columns instead of rows, and an optional setting that returns only items occurring exactly once. For the common case, enter the source range as the first argument and leave the optional settings out. Excel normally returns distinct rows in the order they first appear in the source.

For example, assume cells A2:A8 contain Red, Blue, Red, Green, Blue, Yellow, and Yellow, in that order. With those values in A2:A8, enter =UNIQUE(A2:A8) in an empty cell. The spilled result, from top to bottom, is Red, Blue, Green, and Yellow. The original range is not sorted or changed; UNIQUE simply returns one instance of each distinct value.

If you want only values that occur once in the source, use the third argument. With the same values in A2:A8, enter =UNIQUE(A2:A8,,TRUE) in an empty cell. The result is Green, because it occurs once; Red and Blue each occur twice, and Yellow occurs twice. The empty argument between the commas leaves the optional by-column setting at its default.

Make sure the formula begins with an equals sign and uses the correct range. Excel’s formula separator is usually a comma, but some regional settings use semicolons instead. If Excel immediately marks the commas as invalid, check your regional list-separator settings rather than changing the function name.

Why UNIQUE may not appear in autocomplete

Excel’s function autocomplete is a typing aid, not a complete test of every feature available in the application. Start a formula, type the function name carefully, and check whether Excel displays a suggestion. On Windows, the Formula AutoComplete setting is under File > Options > Formulas. If it is disabled, turn it on and try again. On Mac, autocomplete preferences are managed in Excel’s settings, though the exact location can vary by release.

If autocomplete is enabled but UNIQUE is still absent, confirm the version before troubleshooting the list of suggestions. A function that the installed edition does not support will not become available just because autocomplete is turned on. Also check that you are entering the formula in a worksheet cell or the formula bar, not in a text box, a cell formatted as text, or a dialog that expects a value rather than a worksheet formula.

A cell formatted as Text can cause Excel to display the characters you type instead of calculating them. Change the cell format to General, then re-enter the formula; changing the format alone may not recalculate text already entered. On Windows, press F2 and then Enter to re-commit the cell. On Mac, use the equivalent edit-and-confirm action for your keyboard and Excel settings.

Diagnose common errors and unexpected results

#NAME? usually means Excel does not recognize a name in the formula. If the spelling is correct, check whether your version supports UNIQUE. An older Excel version may show the compatibility prefix _xlfn. before a function it cannot calculate. This is a version limitation, not evidence that the source data is invalid.

#SPILL! means Excel cannot place the full array of results in the cells it needs. The formula cell may be fine; inspect the intended output area for occupied cells, merged cells, or another obstruction. Clear the obstruction or move the formula to an area with enough room. Selecting the error cell may show a warning with more detail about what is blocking the result.

If only one result appears, check whether you entered the formula in an Excel Table. Dynamic-array formulas cannot spill from inside a table. Put the UNIQUE formula in a normal worksheet cell outside the table; the source can still be table data. Also look at the cells below or to the right of the formula: the remaining results may be present but hidden by formatting, narrow columns, or a worksheet boundary.

UNIQUE does not remove the source data or change its order. It treats rows as records when given a multi-column range, so two rows are considered duplicates only when the row values match as a whole. If you expected a distinct list from one particular field, select that column rather than the entire table. For a horizontal source, the function can compare columns instead of rows using its optional second argument.

Make room for dynamic-array results

A UNIQUE formula occupies one cell, called the anchor cell, while its returned values fill a spill range. The spill range grows or shrinks as the source data changes. Do not type separate results into those cells: the output is controlled by the formula in the anchor cell. If the formula returns several items, those cells are part of one calculated array, not independent formulas.

To fix a spill problem, move the formula to a clear part of the sheet or remove the blocking contents. Check for cells that look empty but contain a space or an old formula, as well as merged cells that overlap the intended output. If a result needs to appear in a report or a table, leave enough blank cells for it to expand, or place the formula outside the table and reference its results where appropriate.

Dynamic arrays recalculate as their source changes when workbook calculation is automatic. If formulas seem stale, check File > Options > Formulas > Workbook Calculation on Windows and select Automatic if that is the intended setting. Calculation settings can affect when a formula updates, but switching them does not make an unsupported function available.

Choosing between UNIQUE and other ways to remove duplicates

Use UNIQUE when you want a live list that updates as source data changes. It is especially useful for summaries, dropdown-source lists, and preparing distinct categories without altering the original records. For a one-time cleanup of a dataset, Excel’s Data > Remove Duplicates command may be more appropriate, but it deletes duplicate records from the selected data. Make a copy first if you need to preserve the original.

For a distinct list that should be sorted, UNIQUE alone preserves the order of first appearance rather than sorting alphabetically. You can sort its output separately, or combine it with SORT in a version that supports both functions. If compatibility with Excel 2019 or earlier matters, consider a PivotTable or the Remove Duplicates command instead; those approaches do not create the same automatically expanding formula result.

Another common misconception is that UNIQUE is the same as removing duplicates from the source. It is not: its output is a separate calculated list, while the original values remain intact. That distinction is useful when you need both a complete transaction log and a shorter list of categories derived from it.

Frequently Asked Questions

Is UNIQUE available in Excel 2019?

No. Excel 2019 and earlier perpetual editions do not include the UNIQUE dynamic-array function. Use Microsoft 365 or Excel 2021 or later, or choose an alternative such as Remove Duplicates or a PivotTable.

Why does Excel show #NAME? for UNIQUE?

The most common reason is that the installed Excel version does not support the function. If you are using a supported release, check the spelling, the leading equals sign, and whether the formula was entered into a worksheet cell as a formula rather than as text.

Why does UNIQUE return #SPILL?

Excel cannot place the complete result in the required cells. Clear the occupied or merged cells in the spill area, or move the formula to a larger empty area. A dynamic-array formula also cannot spill from inside an Excel Table.

Can I use UNIQUE in Excel for the web or on Mac?

Yes. Excel for the web and supported Mac versions of Excel include UNIQUE. The key requirement is a version with dynamic-array support, such as Microsoft 365 or Excel 2021 and later, rather than a particular operating system.

Does UNIQUE sort the results alphabetically?

No. By default, it returns distinct items in the order they first occur in the source. Use a sorting method separately if alphabetical order is required.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.