← All postsHow Do You Remove Duplicates in Google Sheets?

How Do You Remove Duplicates in Google Sheets?

Carlos GarciaCarlos GarciaPublished

How Do You Remove Duplicates in Google Sheets?

Removing duplicates in Google Sheets takes four clicks, and the four clicks are the least interesting part of the job.

What matters is what Google counts as a duplicate before it deletes anything, because the answer is broader than most people expect and it is not symmetrical across the tools Sheets gives you. One of them ignores letter case by Google's own documentation. One of them is documented as ignoring letter case too. The third is silent on the question entirely.

This guide gives you the menu path first, then the rules the feature is actually operating under, then the three things Google's own help page declines to tell you about what happens after you click the button.

The Short Answer

Select your data, then go to Data > Data cleanup > Remove duplicates. Choose which columns to compare and tell the dialog whether your data has a header row, then confirm.

That is the whole operation. There is no add-on to install, no formula to write, and nothing to enable first.

Two details in Google's own wording are worth having before you use it. The first is a convenience: if you select a single cell inside a block of data rather than dragging across the whole thing, Google says duplicates "will be removed for the whole range" anyway. Sheets works out the boundaries of your data for you.

The second is the one that catches people. Google states that cells with identical values but different letter cases, different formatting, or different formulas are all considered duplicates. So ACME Corp and acme corp are the same row as far as this feature is concerned, and a cell holding a typed number and a cell holding a formula that produces the same number are also the same row.

That is usually what you want. It is occasionally a disaster, and the only way to know which you are dealing with is to look before you delete.

Cleaning up duplicated records is the same discipline as cleaning up duplicated pages. Start with a free audit and see where your site is competing against itself.

Free audit

Where do you stand in AI search?

A manual SEO + AI search audit of your site: how ChatGPT, Gemini, Perplexity and Claude read and cite it today, and what to fix first. Report within 48 hours, no credit card.

Get my free audit

Or have it all done: AI SEO services, $999 once

What Google Actually Counts as a Duplicate

The feature compares the columns you tick, row by row, and treats two rows as the same when every compared value matches. The subtlety is entirely in the definition of "matches".

Letter case does not protect a row

Google's documentation is explicit that differing letter case does not prevent a match. If your sheet has a Name column with Smith, SMITH and smith in it, and you run Remove duplicates across that column, you will come out with one of them.

Which one? Google does not say. More on that below, because it is the single most important thing the help page leaves out.

Formatting and formulas do not protect a row either

The same sentence in Google's help covers formatting and formulas. A cell displayed as currency and a cell displayed as a plain number are duplicates if the underlying values are identical. A hard-typed 1500 and a =SUM(...) that evaluates to 1500 are duplicates too.

This is the opposite of the intuition most people bring from manual checking, where a cell that looks different feels different. Remove duplicates is looking at values, not at presentation.

The one thing that genuinely does break a match

Invisible characters. Google's own documentation for the UNIQUE function warns that rows which look identical may differ by hidden text such as trailing spaces, and advises checking for exactly that when a dedupe does not behave as expected.

Sheets has a tool for it: Data > Data cleanup > Trim whitespace, which strips extra leading, trailing and excessive spaces from a selected range.

And here is the gotcha, in Google's words rather than mine: a non-breaking space "will not be trimmed". If your data arrived by pasting from a web page or an exported report, non-breaking spaces are exactly the kind of thing it arrived with, and Trim whitespace is documented as leaving them in place. Two rows that are visually identical can stay stubbornly distinct through both Trim whitespace and Remove duplicates, and nothing in the interface will tell you why.

The same UNIQUE documentation adds a second cause worth checking: inconsistent number formatting. If some values in a column are stored as percentages and others as plain numbers, they are not the same value even when they read the same way on screen.

How to Remove Duplicates Step by Step

Step 1: Make a copy of the sheet first. This is not in Google's instructions and it is the step you will be glad of. Duplicate the tab, name the copy something obvious, and work on the original.

Step 2: Select the data. Drag across the range you want cleaned, or click a single cell inside it and let Sheets expand to the full range.

Step 3: Open the tool. Go to Data > Data cleanup > Remove duplicates.

Step 4: Tell it about your headers. The dialog asks you to select which columns to include and whether the data has headers. Tick the header box if row one holds column names, or row one will be compared as data like any other row.

Step 5: Choose the columns deliberately. This is the real decision in the whole process. Comparing every column means a row has to match on all of them to be deleted, which is conservative. Comparing one column — an email address, an order ID — means any second row with that value goes, whatever else is in it.

Step 6: Confirm. Click Remove duplicates.

The column choice in step five is where people get results they did not intend. If you tick every column on a customer list where one record has a slightly different phone number, nothing gets removed, because the rows are not identical. If you tick only the email column, the row that survives takes its phone number with it and the other version is gone.

Decide which column defines identity in your data before you open the dialog, not while you are looking at it.

Picking the column that defines a unique record is the same judgement call as picking the canonical version of a page. Get a free audit and find out which duplicates are splitting your traffic.

How to See Duplicates Before You Delete Anything

Deleting is the last step, not the first. Sheets gives you three documented ways to look at the problem before you act on it.

UNIQUE, for a clean copy alongside the original

The UNIQUE function returns the deduplicated rows as a new output, leaving your source data untouched. Google documents the syntax as UNIQUE(range, by_column, exactly_once).

A plain =UNIQUE(B2:D5) returns the unique rows of that range. Google's own description is that it returns unique rows "discarding duplicates", with rows returned in the order in which they first appear in the source range, so the first occurrence is the one that survives.

The second argument, by_column, defaults to false and switches the comparison to columns instead of rows. The third is the one worth knowing about: exactly_once also defaults to false, and setting it to true returns only the entries that have no duplicates at all. So =UNIQUE(B2:D5, FALSE, TRUE) gives you the rows that appear exactly once — the inverse view, and a fast way to see how much of your data is actually repeating.

Because UNIQUE is a formula, it updates as the source changes. That makes it the right tool for a sheet that keeps receiving new rows.

COUNTIF, for flagging and for highlighting

COUNTIF(range, criterion) returns a conditional count across a range. Pointed at a column and a cell in that column, it tells you how many times that value occurs, which turns a dedupe into a visible column of numbers you can sort and read.

Google states plainly that COUNTIF "is not case sensitive", which puts it in agreement with Remove duplicates on the letter case question. It also notes that COUNTIF can only work with a single criterion — for more than one you need COUNTIFS.

The criterion itself accepts wildcards when the range holds text: ? matches any single character and * matches zero or more contiguous characters, and you escape either by prefixing it with a tilde, as ~? or ~*. A string criterion has to be in quotation marks.

This is also the engine behind highlighting duplicates. Sheets does not ship a one-click "highlight duplicates" button; what it has is conditional formatting with a custom formula, and a COUNTIF that returns more than one is the condition you give it. The color is cosmetic — the counting is the part doing the work.

COUNTUNIQUE and Column Stats, for scale

If you only want to know how bad the problem is, COUNTUNIQUE(value1, [value2, ...]) counts the number of unique values across the values and ranges you give it. Compare that to your total row count and you have the size of the duplication in one number.

Google also documents a sidebar for this: Data > Column Stats shows insights about the values in a column, including frequency tables and distribution. A frequency table is a list of your values with counts beside them, which is a duplicate report by another name and requires no formula at all.

There is a fourth tool nearby, Data > Data cleanup > Cleanup suggestions, which Google describes as identifying common errors you might want to act on — including extra spaces and duplicates — with anomalies and errors shown in red and other suggestions in green. Read it as a prompt rather than an action: Google's page describes it as identifying and suggesting, and does not describe it removing rows on its own.

When to Use Each Method

Use Remove duplicates for a one-off cleanup of a static list where you are confident which column defines a record, and where you do not need to keep the original rows.

Use UNIQUE when the data keeps arriving, when you want the original preserved, or when you need the deduplicated version to feed something else such as a chart or a pivot table. It is also the only one of the three that will show you the rows appearing exactly once.

Use COUNTIF with conditional formatting when a human needs to decide row by row. Some duplicates are errors and some are legitimately repeated events, and that distinction is not something a button can make for you.

Use Column Stats or COUNTUNIQUE first, before any of the above, when you do not yet know whether you have a duplication problem worth solving.

What Google Does Not Tell You

Three things are missing from Google's own help page for Remove duplicates, and they are not small.

It does not say which row survives. The page explains that case, formatting and formula differences all count as duplicates, but it never states which of the matching rows is kept and which are deleted. For UNIQUE, Google does document the answer — rows come back in the order they first appear, so first occurrence wins. For Remove duplicates, the behavior is undocumented, which matters enormously if the duplicate rows differ in some column you did not tick.

It does not say what you are told afterwards. No count of removed rows, no confirmation summary, nothing about what feedback the feature gives. Whatever appears on your screen, Google has not committed to it in writing.

It does not say whether you can undo it. The page is silent on reversing the operation. That silence is the whole argument for duplicating the tab before you start, which is why it is step one above rather than a footnote.

Two more absences are worth naming. Google's page says nothing about how Remove duplicates behaves with merged cells, and nothing about whether a filtered view limits what it touches. If your sheet has either, test on a copy.

And on the question that matters most for consistency: Remove duplicates and COUNTIF are both documented as ignoring letter case, but Google's UNIQUE page does not state its case behavior at all. Three tools for the same job, two documented rules, one gap. Do not assume they agree.

Undocumented behavior in a tool you depend on is a risk you can at least measure. A free audit shows you where the same kind of silent duplication is costing you search visibility.

How Does It Compare to the Alternatives?

Against Excel's Remove Duplicates, the Sheets version is close to equivalent for everyday work and better for collaboration, since the formula approach updates live for everyone in the file. Excel's advantage is Power Query, which is genuinely better when the same cleanup has to run again next month on new data.

Against a marketplace add-on, the honest answer is that the built-in tools cover more ground than add-on marketing suggests. Add-ons are worth it for jobs the native tools do not do: fuzzy matching on near-identical text, comparing duplicates across separate sheets or files, and merging the surviving rows rather than discarding the losers. If your dedupe is occasional and exact, the built-in path is enough.

Against doing it in a database, there is no contest once the data is large or the dedupe is recurring. A spreadsheet is the right place to dedupe something you are looking at; it is the wrong place to dedupe something on a schedule.

The real alternative, though, is upstream: stop the duplicates arriving. Data validation rules can reject a repeated value as it is typed rather than leaving you to find it weeks later, and a COUNTIF-based custom rule is the standard way to build one. Every duplicate you prevent is a dedupe you never run.

Fixing a problem at the source beats cleaning it up on a schedule, in spreadsheets and in search alike. Get a free audit and see which fixes stop the problem recurring.

Final Thoughts

The four clicks are easy. The thinking happens before them: which column defines a record, whether any invisible characters are hiding in your data, and whether you can afford to lose whichever version of a row Google decides to discard — a decision it has never documented.

Copy the tab, look at the duplicates with COUNTIF or Column Stats, decide what identity means in your data, then delete. In that order it is a two-minute job you can trust.

If you are cleaning data to analyze it rather than just to tidy it, deduplicating is the step before summarizing. Our guide on how to create a pivot table in Google Sheets picks the story up from there, and a pivot built on deduplicated data is the only kind worth reading.

Want this done for you?

SEO Stuff gets businesses cited by ChatGPT, Gemini, Perplexity and Claude, and ranking in Google. Start with the free audit, a call, or the package.

See where you stand

A free, manual SEO + AI search audit of your site, with a report within 48 hours. No credit card, no sales call.

Get my free audit

Talk it through

A free 15-minute call with the founder about whether AI search is a meaningful opportunity for your business.

Free strategy call

Have it done

The Done-For-You Package: audit, 10 pages of content, 3 DR50+ placements, dashboard. $999 once, delivered in 21 business days.

See the package