SheetSense

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.

fx
=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

  1. 01A2:C100 is the range to de-duplicate.
  2. 02UNIQUE compares whole rows. A row is dropped only if every column matches an earlier row.
  3. 03The first occurrence of each row is kept, in its original order.

The same logic in four tools

Excel
=UNIQUE(A2:C100)
Google Sheets
=UNIQUE(A2:C100)
SQL
SELECT DISTINCT name, email, company
FROM contacts;
Python (pandas)
deduped = df.drop_duplicates()  # or subset=["email"] to key on one column

Mistakes 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.