Remove duplicate rows with UNIQUE
UNIQUE returns the rows of a range with duplicates removed, as a live copy that updates when the source changes and leaves the original untouched.
=UNIQUE(A2:C100)The sheet: A2:C100 holds a contact list with Name, Email and Company. Some rows appear twice.
How it works, step by step
- 01A2:C100 is the range to de-duplicate.
- 02UNIQUE compares whole rows. A row is dropped only if every column matches an earlier row.
- 03The first occurrence of each row is kept, in its original order.
The same logic in four tools
=UNIQUE(A2:C100)=UNIQUE(A2:C100)SELECT DISTINCT name, email, company
FROM contacts;deduped = df.drop_duplicates() # or subset=["email"] to key on one columnMistakes that break it
- !Excel treats Ada@x.com and ada@x.com as the same, but pandas drop_duplicates treats them as different. Lowercase the column first for the same result.
- !Rows differing only by a trailing space count as different. Clean with TRIM first.
- !To de-duplicate on one column only, use =UNIQUE(B2:B100) or a different approach, since UNIQUE compares the entire row.
Common questions
- How is this different from the Remove Duplicates button?
- The button deletes rows from your data. UNIQUE builds a separate list and keeps the source.
- How do I find which rows are duplicated?
- See the COUNTIF duplicates entry, which flags them.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.