Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to get a sorted list of unique values in Excel and Google Sheets
Topic:Duplicates & uniquenessExcel & Google Sheets
=SORT(UNIQUE(A2:A4))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your data with potential duplicates in column A.
- 2In an empty cell in another column, type =SORT(UNIQUE(A2:A100)) and press Enter.
- 3The formula extracts unique values and sorts them alphabetically (for text) or numerically (for numbers), spilling into as many rows as needed.
- 4In older Excel, use a Pivot Table (Insert → Pivot Table → add field to Rows) to get a unique sorted list, then copy-paste as values.
Tips
- To sort in reverse order, use =SORT(UNIQUE(A2:A100),1,-1) — the third argument -1 sorts descending.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Does UNIQUE work in Excel 2019 or earlier?
No — UNIQUE is an Excel 365 and Excel 2021 function. In older versions, use Remove Duplicates (Data → Remove Duplicates) or a Pivot Table.
How do I count unique values in a range without listing them?
Use =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) to count unique values without extracting them — it works in all Excel versions.
More on Duplicates & uniqueness
See all →How-to
How to remove duplicates
Remove duplicate values with one formula using UNIQUE, or strip them in place with the built-in tool. Works in Excel 365 and Google Sheets.
How-toHow to compare two columns
Check whether two columns match row by row, or find values in one column missing from another, using IF and COUNTIF.
How-toUNIQUE function vs Remove Duplicates in Excel and Sheets
Learn when to use the UNIQUE formula versus the Remove Duplicates tool in Excel and Google Sheets to eliminate or surface distinct values.
How-toHow to flag duplicate values
Use COUNTIF to flag duplicate values in a list with a simple formula that marks any entry appearing more than once.
How-toHow to find duplicate values
Identify which values appear more than once in a list using COUNTIF to add a Duplicate/Unique label to each row.
How-toHow to compare two lists for differences
Find values that exist in one list but not the other using COUNTIF to label each item as Matched or Unmatched.