Excel's UNIQUE function returns a list of the distinct values in a range, so you can skip Remove Duplicates and avoid changing your original data. Below are the formula, a sorted two-column version, and the two limits most likely to affect you.
Check that your Excel version has UNIQUE
Microsoft's UNIQUE function page lists the function for Excel for Microsoft 365, Excel 2024 and Excel 2021, plus Excel for the web, iPad, iPhone and Android.
If your copy of Excel isn't on that list, the formula won't work there. Use Excel for the web or a newer desktop version instead.
The UNIQUE syntax
Microsoft gives the syntax as:
=UNIQUE(array,[by_col],[exactly_once])
Only array is required. That's the range you want deduplicated. by_col and exactly_once are optional, which is what the square brackets mean. This guide covers only the basic one-argument form. For the exact behaviour of the two optional arguments, read their descriptions on Microsoft's page.
Steps to list unique values from one column
- Click an empty cell where you want the list to start. Pick one with blank cells below it.
- Type the formula with your own range, for example:
=UNIQUE(A2:A12)
- Press Enter.
You type the formula once and the results fill the cells below it. Microsoft calls this spilling. Don't copy the formula down the column. Only the first cell holds the formula, and the rest of the list comes from that one cell.
Sort the unique list, or combine two columns first
Microsoft's page includes this example:
=SORT(UNIQUE(B2:B12&" "&A2:A12))
It works from the inside out:
B2:B12&" "&A2:A12joins each value in column B to the matching value in column A, with a space between them.UNIQUE(...)keeps one copy of each joined result.SORT(...)puts the final list in order.
Use this pattern when one column alone isn't enough to tell rows apart. If you only need a sorted list from one column, wrap the basic formula:
=SORT(UNIQUE(A2:A12))
If you want to keep only rows that match a condition before you deduplicate them, see how to use FILTER in Excel with two conditions. To turn the result into a single comma-separated cell, see how to join cells with TEXTJOIN.
When the formula points at another workbook
This setup breaks most often. According to Microsoft, dynamic arrays that reference another workbook work only when both workbooks are open. If you close the source file, the UNIQUE result stops working.
You have two ways to fix it:
- Keep both workbooks open while you work with the list.
- Copy the source data into a sheet in the same workbook, and point UNIQUE at that sheet instead.
What this guide doesn't cover
Those details weren't in the source material for this article, so check Microsoft's UNIQUE page directly if you need them.
