How to Remove Duplicates in Google Sheets (3 Ways)

To remove duplicates in Google Sheets, select your data and go to Data → Data cleanup → Remove duplicates — Sheets finds matching rows and deletes all but the first one. If you’d rather not touch the original, use =UNIQUE() in a spare column to produce a de-duplicated copy alongside it.

Which method should you use?

Method 1: Remove duplicates (deletes them)

This is the built-in tool, and it’s the one most people want.

1. Select your data, including the header row and every column you care about.
2. Go to Data → Data cleanup → Remove duplicates.
3. Tick Data has header row if your first row holds headings — this protects the header from being treated as data.
4. Tick the columns to match on. By default all selected columns are checked, meaning a row is only a duplicate if every value matches.
5. Click Remove duplicates.
6. Sheets reports how many duplicates were found and removed, and how many rows remain.

Before you run it, take a copy. Ctrl + Z undoes it, but only in that browser session. File → Make a copy first, or work on a duplicate tab, is the safe habit.

The key setting is step 4. If you tick only Email, Sheets deletes rows with a repeated email even when everything else differs — keeping the first and discarding the rest. If you tick every column, a row is only removed when it’s identical across all of them. Decide which you mean before clicking.

Method 2: UNIQUE() — a de-duplicated copy

UNIQUE leaves your source data alone and returns one row per distinct value:

=UNIQUE(A2:C100)

Put it somewhere out of the way — a new sheet, or columns to the right — and it spills the unique rows automatically.

• Header row included? UNIQUE keeps the first row as-is, so include the header in the range and it appears once at the top.
• Updates automatically: if the source changes, the UNIQUE output follows. The Remove duplicates tool does not — it’s a one-time action.

Use this when you want to preserve the original for reference. Use the built-in tool when you want the originals gone.

Method 3: Count them first with COUNTIF

To see duplicates without deleting anything, add a helper column:

=COUNTIF($A$2:$A$100, A2)

Fill it down. Any row showing 1 is unique; anything 2 or higher is a duplicate. You can then sort by that column (see how to sort in Excel — the idea is the same) and decide manually.

Better: flag rather than count, so you can scan at a glance:

=IF(COUNTIF($A$2:$A$100, A2) > 1, "DUPLICATE", "")

That prints “DUPLICATE” next to every repeated value. Pair it with conditional formatting to shade those rows red.

Remove duplicates in one column only

If your data has several columns but a duplicate is defined by one of them — the classic case is an email list:

1. Select the range.
2. Data → Data cleanup → Remove duplicates.
3. Untick Select all, then tick only the column that defines a duplicate.
4. Click Remove duplicates.

Sheets keeps the first occurrence and deletes later rows sharing that value. The other columns’ data goes with them — which is usually what you want (one row per contact), but is worth knowing before you click.

Common problems

“Remove” vs “delete” — the same job

Google’s own help page calls this Data cleanup → Remove duplicates, but people search for “delete duplicates” and “deduplicate” too. In Sheets, these are one feature. Unlike removing duplicates in Excel, which keeps its tool under the Data tab rather than a cleanup submenu, the behaviour is otherwise similar.

If the values you’re cleaning came from one combined cell, split them first — see how to split text into columns in Google Sheets. And if you’re building the sheet from scratch, start with Google Sheets formulas for beginners.

FAQ

How do I remove duplicates in Google Sheets?

Select your data → Data → Data cleanup → Remove duplicates → tick Data has header row and the columns to match on → Remove duplicates.

Does removing duplicates delete my original data?

Yes — the built-in tool deletes the duplicate rows from the sheet. To keep the original intact, use =UNIQUE(range) in a separate area instead.

Can I remove duplicates without deleting anything?

Yes. Add a =COUNTIF(...) or =IF(COUNTIF(...)>1,"DUPLICATE","") helper column to flag them, then decide what to remove yourself.

Why weren’t my duplicates detected?

Usually a slight difference: a trailing space, different letter case, or a number stored as text. Sheets treats “Apple” and “apple” as identical, but 1.00 and 1 as different.

How do I remove duplicates based on one column only?

Select your data, open Remove duplicates, untick Select all, then tick only the column that defines a duplicate. The first occurrence is kept and later matches are deleted, along with their other column values.