How to remove duplicates in Google Sheets

Google Sheets has this built in, two ways: =UNIQUE(range) returns a deduplicated copy of your data, and Data → Data cleanup → Remove duplicates deletes duplicate rows in place, keeping the first occurrence. Both match exactly, so for messy data (extra spaces, capitalization, reordered names) you'll want a normalizing tool.

Method 1: the UNIQUE function (non-destructive)

In an empty column, type =UNIQUE(A2:C100). Sheets spills a copy of the range with duplicate rows removed, and it recalculates whenever the source changes. Your original data is untouched. Copy the result and use Edit → Paste special → Values only if you want a frozen clean copy. UNIQUE compares entire rows; to dedupe on one column while keeping the others, you need the next method.

Method 2: Data cleanup (in place)

  1. Select your data range (or one cell inside it).
  2. Open Data → Data cleanup → Remove duplicates.
  3. Tick Data has header row if it does, then choose which columns define a duplicate.
  4. Click Remove duplicates. Sheets reports how many rows were removed; the first occurrence of each is kept. Undo (Ctrl/Cmd+Z) restores everything if the count looks wrong.

While you're in that menu, Data cleanup → Trim whitespace is worth running first: a trailing space is the most common reason two "identical" cells don't match.

When the built-ins aren't enough

Both methods match exactly: "Acme Inc." and "acme inc" survive as two rows. For that, download the sheet as CSV (File → Download → .csv) and drop it into our CSV dedupe tool, which offers case-insensitive matching and your choice of key columns. No sign-in, and the file is processed in your browser, never uploaded. Then import the cleaned CSV back into Sheets. Reordered names and near-misses ("Smith, Jane" vs "jane smith") are a job for fuzzy matching in the API.

FAQ

Frequently asked questions

What's the fastest way to remove duplicates in Google Sheets?

Data → Data cleanup → Remove duplicates. Select your range, tick whether the data has a header row, choose the columns to compare, and Sheets deletes duplicate rows in place, keeping the first occurrence.

What does the UNIQUE function do?

=UNIQUE(A2:A100) returns a copy of the range with duplicate rows removed, leaving the original data untouched. It updates live as the source changes, but matching is exact, so "Ann" and "ann " count as different values.

How do I find duplicates without deleting them?

Flag them with a helper column: =COUNTIF($A$2:$A$100,A2)>1 shows TRUE on every value that appears more than once. Or use Format → Conditional formatting with the same formula to highlight them.

When is an external tool worth it?

When exact matching isn't enough: trailing spaces, different capitalization, or names written in different orders. The tool on this site normalizes those and adds a fuzzy similarity threshold, with no sign-in and no upload: export the sheet as CSV, clean it, re-import.

Last updated