Remove Duplicates in Excel With Formulas
Remove Duplicates With Formulas is a Excel function that unique returns a dynamic array of distinct values from the input range. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Extract a clean list of unique values without modifying your original data. UNIQUE for Excel 365, COUNTIF tricks for older versions.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.