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 jane@acme.com and Jane@Acme.com 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.

Compare two columns in Google Sheets for duplicates

Deduplicating one column asks which rows repeat. Comparing two columns asks a different question: which values in list A also appear in list B. That is the shape of most real jobs — this month's export against last month's, a new lead list against the customers you already have, registrations against attendees.

COUNTIF handles it once you point the range at the other column. Select column A, open Format > Conditional formatting, choose Custom formula is, and enter one of these [4]:

• =COUNTIF($B:$B, $A1)>0 highlights every value in A that also exists in B — the overlap.
• =COUNTIF($B:$B, $A1)=0 inverts it and highlights what appears only in A. This is usually the more useful of the two, because it is the list of things that are genuinely new.

To pull the answer out as data rather than colour, use MATCH, which Google documents as returning "the relative position of an item in a range that matches a specified value" [6]. Putting =IF(ISNA(MATCH(A2, B:B, 0)), "new", "duplicate") beside each row writes a label you can then sort or filter on, which beats scrolling for highlights on a long list. The 0 as the third argument is not optional in practice: it demands an exact match, and leaving it out tells MATCH to assume the range is sorted, which returns confidently wrong answers on a list that is not.

Both approaches compare the raw cell contents, so a trailing space on one side and not the other stops a real match from being found. The whitespace trap below applies here with more force than it does within a single column, because a mismatch between two columns looks identical to a legitimate difference — there is nothing on screen to tell you which one you are looking at. Trim both columns before you trust either result.

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. "jane@acme.com " with a trailing space is a different string from "jane@acme.com", 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. That round trip has its own losses in both directions — what Sheets does to a CSV on the way in and on the way out covers the conversion checkbox that strips leading zeros on import, and the first-sheet-only limit that quietly drops tabs on export. 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. For a list you never want to upload in the first place, the same browser-based csv cleaner handles the whole pass locally. We lay out the trade-offs both ways in our Sheets comparison. Trim whitespace, fix casing, then dedupe: the CSV cleaning software hub has one tool per step, in that order.

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 on its way into a CRM, do the pass before the import rather than after: what duplicate records cost you once they are inside a CRM covers why merging them later is the expensive route. Deduplication is only the first of seven checks to run before a CRM import — email casing, phone format, and encoding are the ones that reject rows outright.

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. When the near-duplicates are spelling variants rather than spacing — Acme Inc, ACME Incorporated, Acme, Inc. — no spreadsheet function collapses them, and the job belongs to a clustering workbench; when OpenRefine's install is worth paying for sets out where that line falls.

Once the sheet is deduplicated, exporting it as a CSV is usually the last step — and if whoever receives it expects a workbook instead, you can convert that CSV to Excel in the same browser tab without the file touching a server. If the list arrived as an .xlsx in the first place and Sheets keeps reformatting it on import, going the other way and exporting the workbook to a comma-delimited file first gives the import dialog far less to guess at. Deduplicating first also changes any number you report from the sheet: a csv plotter run over the raw export counts the same customer twice in every total, and the inflated bar looks perfectly plausible until someone checks it.