Excel UNIQUE Not Working? Causes and Fixes

Excel’s UNIQUE function usually stops working because the installed Excel version does not support it, the formula has an error, or the results cannot spill into neighboring cells. Check your version first, then look for a blocked spill range or a mismatch between the formula’s options and the result you expect.

What UNIQUE Does—and Which Versions Support It

UNIQUE returns a list of distinct values from a range or array. Instead of removing duplicates from the source data, it creates a separate result that updates when the source changes. This behavior is part of Excel’s dynamic array features: one formula can return several results, which fill adjacent cells automatically.

The function is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. It is not included in older perpetual versions such as Excel 2019. If an older installation does not recognize the function, it may display #NAME? or treat the entry as an unrecognized formula. Check the installed product under File > Account > About Excel on Windows. The exact account and update details can vary by license.

Updating Excel can help if your Microsoft 365 installation is out of date, but an update will not add dynamic array functions to a version that does not include them. Excel for the web also supports dynamic arrays, though some interface details differ from the Windows desktop app.

Check the Formula’s Syntax and Options

The function has three arguments: the source array, an optional setting for whether to compare by row or by column, and an optional setting for whether to return values that occur exactly once. For a basic list of distinct entries, only the source range is needed.

With source values in A2:A100 and the result intended to begin in C2, enter =UNIQUE(A2:A100) in C2. Excel should return one copy of each distinct value and spill the list downward. Check that the range is correct and that parentheses are balanced; a misspelled function name or a missing parenthesis can prevent the formula from calculating.

The optional arguments can produce results that seem wrong if they are misunderstood:

by_col is FALSE or omitted by default, so Excel compares rows. Set it to TRUE when the values to compare are arranged horizontally in columns.
exactly_once is FALSE or omitted by default, so Excel returns one copy of every distinct value. TRUE returns only values that appear exactly one time in the source.

With entries in A1:D1 and the output starting in A3, use =UNIQUE(A1:D1,TRUE) to compare values across the row. With values in A2:A8 and the output beginning in C2, =UNIQUE(A2:A8,,TRUE) returns only the entries that occur once. For example, if A2:A8 contains 12, 8, 12, 15, 8, 20, and 21, the result is 15, 20, and 21—not 12, 8, 15, 20, and 21.

Diagnose Errors and Unexpected Results

The visible error is a useful clue. If Excel shows #NAME?, first confirm that your version supports UNIQUE and then check the spelling. If the formula appears as plain text instead of calculating, the cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula. You can also select the cell, press F2, and press Enter on Windows to make Excel interpret the entry again.

If Excel displays the formula in the cell rather than its result, check whether Show Formulas is enabled on the Formulas tab. Also confirm that the entry starts with an equals sign. In some regional settings, Excel expects semicolons rather than commas between arguments. The examples here use commas; follow the separator style Excel accepts in your installation.

If the formula calculates but returns more or fewer entries than expected, inspect the source range. It may include a header, omit newly added rows, or include cells outside the data you meant to analyze. A fixed range such as A2:A100 will not automatically expand past row 100. An Excel Table can expand with added rows, but the UNIQUE formula itself needs a place where its results can spill.

Fix a Blocked Spill Range

A valid UNIQUE formula can return #SPILL! when Excel cannot place all the results. Select the error cell and use Excel’s error indicator to see whether the output area is obstructed. Common blockers include existing values, formulas, merged cells, or the edge of the worksheet. Clear or move the obstructing cells, or move the formula to a location with enough empty space.

Dynamic arrays cannot spill from a formula entered inside an Excel Table. Put the UNIQUE formula in a normal worksheet cell outside the table, leaving an empty area for its results. The source data can still be in a table. For example, if a table is named Sales and its Region column contains the entries, and the result begins in G2 outside the table, enter =UNIQUE(Sales[Region]) in G2. The table reference can adjust as table rows are added, while the output spills into ordinary cells.

Do not copy a spilling formula down one row at a time as if each cell were a separate result. The formula belongs in the first output cell; Excel fills the rest of the spill range. If you need to refer to the entire returned list in another formula, Excel’s spill-reference operator can refer to the output beginning at a cell such as G2 with G2#.

Understand Blanks, Text, and Apparent Duplicates

Values that look identical on screen may not be identical in the source. One entry might contain a leading or trailing space, a nonprinting character copied from a website, or a different data type. For example, a number stored as text can behave differently from a numeric value in calculations and comparisons. Inspect the underlying cells or clean the source data before concluding that UNIQUE has missed a duplicate.

Text comparisons in Excel are generally case-insensitive, so entries such as “North” and “NORTH” are normally treated as the same value by UNIQUE. If case is meaningful to your task, UNIQUE alone is not a case-sensitive deduplication tool. You would need a different approach designed around case-sensitive comparison.

Blank cells in the source can also affect what appears in the result. If you want to exclude blanks, combine UNIQUE with FILTER. With the source list in A2:A100 and the result beginning in C2, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")). FILTER first keeps entries that are not empty, and UNIQUE then returns the distinct remaining values. If the source contains formulas that display an empty string, this condition excludes those results too.

Check Calculation and Dynamic Array Behavior

Excel normally recalculates formulas automatically. If the source changes but the UNIQUE result does not, check Formulas > Calculation Options > Automatic. In Manual calculation mode, formulas may not update until recalculation. Press F9 on Windows to recalculate formulas; on Mac, the shortcut can depend on the keyboard and Excel settings, so the Formulas menu is another option.

Dynamic array results are linked to the formula in the top-left cell. If you select a cell farther down the spilled list, Excel may show the result as part of the spill range rather than as an independent formula. Edit the original formula cell to change the calculation. If you delete or overwrite part of the output, the spill may be blocked or replaced, so clear the entire output area before moving the formula.

Sorting the source does not change which values are distinct, but it can change the order in which UNIQUE returns them. UNIQUE does not sort the results automatically; it returns the distinct values in the order they first occur. If a sorted list is needed, combine it with SORT. With source entries in A2:A100 and the result starting in C2, use =SORT(UNIQUE(A2:A100)). SORT and UNIQUE are available in Microsoft 365 and Excel 2021 or later.

Alternatives for Older Excel Versions

If the installed version does not support UNIQUE, a formula copied from a newer workbook will not become compatible simply by changing its syntax. One built-in option is Advanced Filter, which can copy distinct records to another location. On Windows, go to Data > Advanced, choose Copy to another location, select the list range and destination, and check Unique records only. The precise dialog layout can vary slightly across Excel versions.

For a summary of distinct categories with counts or other aggregates, a PivotTable may be a better fit. Select the source data and choose Insert > PivotTable, then place the category field in the Rows area. A PivotTable does not behave exactly like a live UNIQUE formula, and you may need to refresh it after the source changes. If you use Microsoft 365 or Excel 2021 or later, UNIQUE is usually simpler when you just need a formula-driven list.

Frequently Asked Questions

Why does Excel say UNIQUE is not a valid function?
Most often, the workbook is open in a version that does not support the function, such as Excel 2019 or an earlier release. UNIQUE is available in Microsoft 365 and Excel 2021 or later. Verify the installed version under File > Account on Windows.

Why is UNIQUE returning #SPILL?
Excel cannot place the full result in the required cells. Clear the cells below or beside the formula, remove merged cells in the spill area, and make sure the formula is outside an Excel Table.

Why does UNIQUE show a blank or zero in the result?
The source range may contain blank cells or formulas that return empty strings. Exclude empty entries with FILTER before passing the remaining values to UNIQUE, and confirm that your selected range does not include unintended rows.

How do I return values that occur only once?
Set UNIQUE’s third argument, exactly_once, to TRUE. That differs from the usual distinct list: it excludes values that appear two or more times and keeps only one-time entries.

Can I use UNIQUE inside an Excel Table?
The source data may be in a Table, but a dynamic array formula cannot spill its results from a cell inside the Table. Enter the formula in a regular worksheet cell outside it and leave room for the output.

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.