How to remove duplicates in Google Sheets

To remove duplicates in Google Sheets, select your range and click Data > Data cleanup > Remove duplicates. That is the destructive option. Sheets also has UNIQUE(), which writes a clean copy without touching the original, and COUNTIF, which only highlights. The three behave differently, and picking the wrong one costs you data.

This guide covers all three, the formatting problems that quietly defeat them, and the point where the spreadsheet stops being the right tool. If you work in Excel instead, the same ground is covered in our walkthrough of how to remove duplicates in Excel.

The built-in way: Data cleanup > Remove duplicates

This is the fastest option and the only one that edits your sheet directly.

Select the range you want to clean, then click Data, Data cleanup, Remove duplicates [1]. A dialog appears with two decisions.

The first is the "Data has header row" checkbox. Sheets does not detect a header automatically — you tick this yourself, and Google's documentation is explicit that you select "whether or not the data has headers" [1]. Leave it unticked and your column titles become an ordinary row that can be deleted like any other.

The second is the column list. Every column you leave checked has to match for two rows to count as duplicates. Leave all of them checked and only rows that are identical across the board are removed. Check a single column — email, say — and Sheets collapses rows that share that email even when the other columns differ, silently discarding the values in those other columns. That behaviour is what you want when email is the identity of a record, and a disaster when you did not realise the other columns held different information.

One thing to know before you click: Google's help page states that "cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates" [1]. So a bolded row is not protected, and a hardcoded 500 and a =SUM(...) that returns 500 collapse into one. Several widely-read guides claim the tool is case-sensitive. Google's documentation says it is not. We have not verified the runtime behaviour ourselves, so the honest advice is to not depend on either answer — normalise case first and the question stops mattering.

The tool deletes immediately. Ctrl+Z works while the tab is open; after that you are relying on File > Version history, and Sheets caps named versions at 15 per spreadsheet. Duplicate the sheet before a large pass.

Find duplicates without deleting anything

Often the real question is not "delete the duplicates" but "show me what is duplicated". Deleting blind is how a list loses records nobody notices for a month.

Conditional formatting with COUNTIF does this. Select your column, open Format > Conditional formatting, choose Custom formula is from the "Format cells if" menu, and enter a formula [4].

Two variants are worth knowing:

• =COUNTIF(A:A, A1)>1 highlights every member of a duplicate group, including the first occurrence. Use this to see the full scale of the problem.
• =COUNTIF($A$1:$A1, $A1)>1 highlights only the second and later copies. Use this when you plan to delete by hand, because what stays unhighlighted is exactly what you are keeping.

COUNTIF returns a conditional count across a range and is explicitly not case sensitive [3], which means it flags [email protected] and [email protected] as the same value. That is usually what you want when hunting duplicate people, and worth knowing when the difference in case is meaningful — product SKUs, for instance.

This method changes nothing. It is the right first pass on any list you did not assemble yourself.

Deduplicate with a formula using UNIQUE

UNIQUE writes a clean copy and leaves your source data alone. Google documents it as returning "unique rows in the provided source range, discarding duplicates", with rows returned in the order in which they first appear [2].

Put =UNIQUE(A2:C) in an empty column and you get a deduplicated version of that range beside the original. Two arguments make it more useful than it first looks:

• UNIQUE(range, by_column, exactly_once). Setting by_column to TRUE deduplicates across columns rather than rows.
• Setting exactly_once to TRUE inverts the job entirely: instead of collapsing duplicate groups down to one row, it returns only the rows that never had a twin. That is the fast way to answer "which of these records is unambiguous?"

The catch is that the output is a live array formula. It recalculates as the source changes, you cannot edit cells inside it, and copying it elsewhere carries the formula rather than the values. Before you export, copy the result and use Edit > Paste special > Values only.

The whitespace trap that defeats every method

This is the single biggest reason a deduplication pass looks like it did nothing.

Every method above compares text. "[email protected] " with a trailing space is a different string from "[email protected]", so it survives Remove duplicates, survives UNIQUE, and does not get flagged by COUNTIF. Nothing about the sheet tells you the space is there.

Sheets ships a fix as a sibling menu item: Data > Data cleanup > Trim whitespace [1]. Run it before you deduplicate, not after.

It has a real limitation, though, and Google states it plainly: non-breaking spaces are not trimmed [1]. A non-breaking space is what you get when you paste out of a web page, a PDF, or a formatted document — which describes most lists that arrive as an email attachment. Those characters look identical to a normal space and defeat both the trim tool and a plain TRIM() formula. Removing them needs a REGEXREPLACE and knowledge of the character code, which is a fair distance past what most people want to do on a Tuesday afternoon.

The same trap applies to case (Acme vs ACME), to punctuation (Acme, Inc. vs Acme Inc), and to any near-match a human reads as the same thing. Deduplication is only as good as the normalisation you do first. That order — normalise, then dedupe — is the core idea behind the wider data cleaning process.

What Google Sheets cannot do here

Two hard limits are worth knowing before you commit to doing this in Sheets.

Scale. A Google Sheets spreadsheet holds up to 10 million cells or 18,278 columns, and the same ceiling applies to spreadsheets imported from Excel [5]. Ten columns wide, that is about a million rows — but the sheet becomes slow long before the cap, and a deduplication pass on a few hundred thousand rows is an exercise in watching a progress spinner.

The file has to go to Google first. If your data started as a CSV, using Sheets means uploading it to Drive, converting it, cleaning it, and exporting it back. For a list of customers, candidates, or patients, that upload is the part worth thinking about: the file now lives on a third-party server, inside your organisation's retention and sharing rules, and outside your direct control.

That is the gap Sigmera fills. It runs the same deduplication in a browser tab with nothing uploaded — the CSV duplicate remover reads the file locally, lets you pick the matching column, and ignores case and spacing while showing you every flagged row before you download. There is no server to send the file to, which makes it a reasonable default for anything containing personal data. We lay out the trade-offs both ways in our Sheets comparison.

Which method should you use

Match the method to what you actually know about the file.

If you assembled the list yourself and trust it, Data cleanup > Remove duplicates is fine — trim whitespace first, tick the header box, and duplicate the sheet as a backup.

If someone sent you the file, start with the COUNTIF highlight. Seeing 4,000 flagged rows in a 5,000-row list tells you something is wrong with your matching column, which is much better learned before the delete than after.

If the sheet feeds a dashboard or another formula, use UNIQUE so the source stays intact and the clean copy updates itself.

And if the list is genuinely messy — mixed formats, pasted-in text, near-duplicates that differ by a space — the normalisation matters more than the deduplication step. Standardise the column that identifies a record first, whether that is running the email column cleaner or fixing the phone formats, and the duplicates collapse on their own.