Remove Duplicates in Excel With Formulas
Extract a clean list of unique values without modifying your original data. UNIQUE for Excel 365, COUNTIF tricks for older versions.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Get a list of unique values from column A"
=UNIQUE(A2:A100)UNIQUE returns a dynamic array of distinct values from the input range. It automatically spills into adjacent cells below, creating a clean deduplicated list.
Step-by-step breakdown
- A2:A100 is the source range (can include duplicates)
- Results spill downward automatically (Excel 365 dynamic arrays)
- Optional 2nd argument: TRUE to return values that appear exactly once
- Optional 3rd argument: TRUE to compare by columns instead of rows
- For older Excel: =INDEX($A$2:$A$100,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$100),0)) with Ctrl+Shift+Enter
Edge cases & warnings
- UNIQUE requires Excel 365 or Excel 2021
- Empty cells in the range may appear as a blank entry in results
- Case-insensitive: "Apple" and "apple" are treated as duplicates
- The spill range must be empty or you get a #SPILL! error
Examples
"["Apple","Banana","Apple","Cherry","Banana"]"
["Apple","Banana","Cherry"]"["A","a","B","b"]"
["A","B"] (case-insensitive)Frequently asked questions
Does UNIQUE work in Google Sheets?
Yes. Google Sheets has UNIQUE with identical syntax. It's been available there longer than in Excel.
How do I count unique values instead of listing them?
Use =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) for a count of distinct values. Or =ROWS(UNIQUE(A2:A100)) in Excel 365.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.