No, the Excel UNIQUE function does not return duplicates—it is specifically designed to extract unique values from a range or array. If you are seeing duplicates in the output, the cause is almost always extra spaces, different data types (text vs. number), or line breaks inside cells that make values look identical but actually differ. This article explains how UNIQUE works, why it might appear to fail, and how to get truly unique results.
What the UNIQUE Function Does
UNIQUE is a dynamic array function introduced in Excel 365 and Excel 2021. It returns a list of distinct values from a given range or array, removing duplicates automatically. The syntax is:
=UNIQUE(array, [by_col], [exactly_once])
- array – the range or array to extract unique values from (required).
- by_col – a logical value: FALSE (default) compares rows, TRUE compares columns.
- exactly_once – a logical value: FALSE (default) returns all unique values; TRUE returns only values that appear exactly once in the data.
For example, with a list of fruit names in A2:A10, the formula =UNIQUE(A2:A10) returns each fruit once, in the order they first appear. If you use =UNIQUE(A2:A10,,TRUE), it returns only fruits that appear once in the entire list.
Why UNIQUE Might Show Duplicates (Real Causes)
If your UNIQUE output still shows what look like duplicates, check these common culprits:
- Invisible spaces: Trailing or leading spaces make “Apple” and “Apple ” different. Use
=TRIM(A2:A10)to clean data first. - Non-breaking spaces: Sometimes copied from web pages, these are character 160, not regular space (32). Use
=SUBSTITUTE(A2:A10,CHAR(160)," "). - Line breaks: A cell with a line break (CHAR(10)) may look like a single word but contains a hidden break. Use
=CLEAN(A2:A10)to remove non-printable characters. - Different data types: A number stored as text (e.g., “123”) and a real number (123) are treated as different. Convert text to numbers with
=VALUE(A2:A10)or use the Text to Columns wizard. - Case sensitivity: UNIQUE is case-insensitive by default, so “Apple” and “apple” are considered the same. If you need case-sensitive uniqueness, you need a different approach (see below).
How to Clean Data Before Using UNIQUE
The best way to avoid false duplicates is to clean your source data first. Here is a step-by-step approach:
- Select a helper column and enter
=TRIM(CLEAN(A2))to remove spaces and non-printable characters. - If you have non-breaking spaces, wrap with SUBSTITUTE:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). - Copy the helper column and paste as values (Ctrl+C, then Paste Special > Values).
- Now apply
=UNIQUE(helper_range)to the cleaned data.
For a one-step formula, you can nest functions: =UNIQUE(TRIM(CLEAN(A2:A10))) works in Excel 365 because TRIM and CLEAN are array functions that spill results. Note that this formula does not handle non-breaking spaces; you would need to add SUBSTITUTE.
Case-Sensitive Unique Values
UNIQUE is case-insensitive, meaning it treats “Excel” and “excel” as the same value. If you need to treat them as different, you can use a helper column with a formula that distinguishes case, then use UNIQUE on that. One method:
- Add a helper column with
=EXACT(A2,A3)? No, that compares two cells. Instead, use a formula that returns a unique key based on case. For example, use=A2&"|"&ROW()? That would make every row unique, which is not what you want.
A better approach is to use a combination of COUNTIF and array formulas, but that gets complex. For most users, the simplest is to convert all text to a consistent case using UPPER or LOWER, then use UNIQUE. For example, =UNIQUE(UPPER(A2:A10)) will treat “Excel” and “EXCEL” as the same. If you need to keep original case, you would need a more advanced formula using LET and FILTER, but that is beyond the scope of this article.
UNIQUE vs. Remove Duplicates Command
Excel also has a Remove Duplicates command (Data tab > Data Tools > Remove Duplicates) that physically deletes duplicate rows from a range. The key differences:
- UNIQUE is a formula that updates automatically when source data changes; Remove Duplicates is a one-time action that permanently alters your data.
- UNIQUE can return values that appear only once (with exactly_once=TRUE); Remove Duplicates always keeps the first occurrence and deletes the rest.
- UNIQUE works with arrays and can be combined with other functions like SORT and FILTER; Remove Duplicates only works on a selected range.
For a dynamic, formula-based approach, use UNIQUE. For a quick cleanup of a static dataset, Remove Duplicates is fine, but be sure to make a backup copy first.
Using UNIQUE with Multiple Columns (Rows)
When you apply UNIQUE to a multi-column range, it considers the entire row as a single record. For example, if you have names in column A and cities in column B, =UNIQUE(A2:B10) returns each unique combination of name and city. If two rows have the same name but different cities, they are both kept. This is often what you want for data like orders or contacts.
If you want unique values from one column based on criteria in another, you need to combine UNIQUE with FILTER. For instance, to get unique products from column B where sales region in column A is “West”, use:
=UNIQUE(FILTER(B2:B20, A2:A20="West"))
This formula assumes region in A2:A20 and product in B2:B20. It returns the distinct products for the West region.
Common Errors and How to Fix Them
- #SPILL! error: This happens when something is blocking the spill range. Clear the cells below and to the right of the formula, or move the formula to a location with enough empty space.
- #CALC! error: This occurs when you use exactly_once=TRUE but no value appears exactly once. The function returns a #CALC! error because the result is an empty array. You can wrap it with IFERROR to show a friendly message, e.g.,
=IFERROR(UNIQUE(A2:A10,,TRUE),"No unique values"). - #VALUE! error: Usually means the array argument is not valid, such as referencing a range that contains errors. Check for #N/A or #DIV/0! in the source data.
Frequently Asked Questions
Why does my UNIQUE formula show duplicate values?
As explained above, the most likely reason is hidden characters like spaces or line breaks. Clean your data with TRIM and CLEAN, and also check for non-breaking spaces using SUBSTITUTE with CHAR(160). If the values are numbers, ensure they are not stored as text.
Can UNIQUE handle blank cells?
Yes, UNIQUE will return a blank (empty) value as one of the unique values if there are blank cells in the range. If you want to exclude blanks, wrap the range with FILTER: =UNIQUE(FILTER(A2:A10,A2:A10<>"")).
Does UNIQUE work in Excel 2019 or older?
No, UNIQUE is only available in Excel 365 and Excel 2021. In older versions, you can use the Advanced Filter (Data tab > Sort & Filter > Advanced) to extract unique values to another location, or use a pivot table.
How do I get a list of values that appear only once?
Use the third argument: =UNIQUE(A2:A10,,TRUE). This returns values that occur exactly once in the range. If you get a #CALC! error, it means no value appears only once.
Can I sort the unique list automatically?
Yes, combine UNIQUE with SORT: =SORT(UNIQUE(A2:A10)). This will return the unique values in alphabetical order. For numeric data, it sorts numerically.
Why does UNIQUE treat “123” and 123 as different?
Because one is text and the other is a number. Excel stores them as different data types. To fix, convert text to numbers using the VALUE function or by selecting the column and using Text to Columns (Data tab > Data Tools > Text to Columns, then Finish).
Related
- Excel Sales Tracking Template by Region
- Free Excel Sales Tracking Templates: What Works
- Excel RV Window CH1006 Repair: Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Page Breaks Messed Up? How to Fix Print LayoutIf Excel page breaks are messed up, switch to View > Page Break Preview to see what is…
- SUMIFS Not Working With Blanks? Fix the CriteriaWhen you want to sum rows whose criteria cells are blank, use an explicit empty-string criterion: with categories…
- Excel Text to Columns Not Working? Fix Common CausesIf Text to Columns is splitting data incorrectly—or not splitting it at all—select the source cells, go to…
- Excel Spill Error When Creating a Table: Causes and FixesExcel shows #SPILL! when a dynamic-array formula tries to return results inside an Excel Table, because spilled arrays…
- Excel Not Working on iPad? Fixes for Common ProblemsIf Excel is not working on your iPad, check your internet connection and Microsoft account, update Excel and…
- Ctrl+F Not Working in Excel? Common Causes and FixesIf Ctrl+F is not finding what you expect in Excel for Microsoft 365 on Windows, open Home >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.