How to Remove Duplicates in Google Sheets (5 Easy Ways)

 To remove duplicates in Google Sheets, select your range and click Data > Data cleanup > Remove duplicates. Want a live, auto updating clean list instead? Type =UNIQUE(A2:A100) in an empty cell.

Both take under a minute.

Now let me show you which one to pick, and how to dodge the traps.

Why Duplicate Data Wrecks Your Spreadsheet

You open a sheet with 4,000 rows. Half your customer emails appear twice. Your report is due in an hour.

I've lived that moment, and it's awful.

Duplicate rows quietly inflate totals, break charts, and make you email the same person twice. Most of them sneak in through imports, copy and paste jobs, and form submissions.

The fix is quick once you know your options. So let's compare them.

Which Method Should You Use?

Before you click anything, you need to match the method to your situation. A one time cleanup needs a different tool than a weekly report. Some methods delete your data permanently, while others leave it alone. Here's the quick breakdown, so you can choose in ten seconds and skip the trial and error.

The Remove duplicates tool is best for a one time cleanup. It deletes the original rows, and it suits beginners. The UNIQUE function is best for live, updating lists. It leaves your original data alone, and it also suits beginners.

Conditional formatting is best when you want to review duplicates before deleting anything. It never deletes data, and it is beginner friendly. TRIM plus UNIQUE is best for messy imported data. It leaves the source untouched, and it needs intermediate skill.

Apps Script or an add on is best for jobs you repeat every week. Whether it deletes your original data depends on how you set it up, and it also needs intermediate skill.

Method 1: Use the Built In Remove Duplicates Tool

This is the fastest route, and it's where I'd start. The Data cleanup menu is documented on Google's official help page, so you know it's the supported way. It suits any one time cleanup where you're happy to change the original sheet.

Here's the step by step:

1. Select the data range you want to clean.
2. Click Data, then Data cleanup, then Remove duplicates.
3. Tick Data has header row if your range has headers.
4. Choose which columns to analyze.
5. Click Remove duplicates.

Sheets then reports how many rows it removed.

Two details from Google's documentation matter here. First, if you click a single cell inside your range, Sheets applies the cleanup to the whole range. Second, cells with the same value but different letter case, formatting, or formulas count as duplicates.

In practice, the tool keeps the first row and deletes the later repeats, as Ablebits' guide describes. Google's page doesn't spell that out, so test it on a copy.

And that's the catch. This method permanently deletes rows. Duplicate your sheet first. Zapier's walkthrough also warns about forgetting the header checkbox, which is the classic rookie mistake.

Method 2: Pull a Clean List With the UNIQUE Function

Maybe you'd rather not delete anything at all. Fair. The UNIQUE function builds a fresh list beside your data and leaves the original untouched.

That makes it perfect for dashboards, dropdown lists, and reports that need to refresh themselves. Add new rows to your source, and the clean list updates automatically. I reach for this one more than any other.

The syntax is simple:

=UNIQUE(range, [by_column], [exactly_once])

Type =UNIQUE(A2:A100) into an empty cell and press Enter. You'll get each value once, in order of first appearance.

For multiple columns, widen the range. A row only counts as a duplicate when every column matches, which Sheetgo's UNIQUE guide demonstrates with SORT.

Two quick warnings:

• UNIQUE needs empty cells to spill into, or you'll see a #REF! error.
• You can't delete individual output cells. You have to edit the formula cell itself.

Want just a count? Pair it with COUNTUNIQUE, or wrap a QUERY inside UNIQUE, like Lido shows. Struggling to write a formula? Our list of AI tools for Excel formulas can draft one for you.

Method 3: Highlight Duplicates First With Conditional Formatting

Sometimes you want to see the damage before you delete anything. That's smart. Conditional formatting colors every repeat so you can eyeball them, and nothing changes in your data. I use this whenever the sheet belongs to someone else, because deleting blindly in a client's file is a bad day waiting to happen.

Try this:

1. Select your column.
2. Go to Format > Conditional formatting.
3. Choose Custom formula is.
4. Enter =COUNTIF($B$2:$B$15,B2)>1 (adjust the range).
5. Pick a bright fill color.

Zapier uses this exact formula, so you're in good company.

Prefer filtering? Add a helper column with =COUNTIF(A$2:A2, A2) > 1. ListDiff explains that it returns TRUE for every occurrence after the first. Filter for TRUE, review the rows, and delete them.

Method 4: Fix Invisible Duplicates (Spaces and Letter Case)

Ever had two rows that look identical, yet Sheets won't treat them as duplicates? Frustrating, I know.

The culprit is usually a stray space or an invisible character. And case sensitivity adds a second layer of confusion, because different tools behave differently. Clean the data first, then dedupe it.

Start with Google's own fix: select the range and click Data > Data cleanup > Trim whitespace. One catch from Google's documentation is that non breaking spaces won't be trimmed.

For formulas, wrap your range in TRIM:

=ArrayFormula(UNIQUE(TRIM(A2:B17)))

Spreadsheet Point recommends this exact combo.

Need case sensitive matching? Ablebits offers this formula:

=IF(SUMPRODUCT(--EXACT($A$2:$A, A2)) > 1, "Duplicate", "")

It flags "Cherry" and "CHERRY" as different. Remember that COUNTIF ignores case, as Labnol notes.

Method 5: Automate With Apps Script or an Add On

If you repeat this cleanup every week, stop clicking. Automate it. You have two routes: write a short script, or install a ready made add on. The script is free and fully customizable. The add on costs money but needs zero code. Either way, you'll save hours across a year.

Google Apps Script lets you add a custom menu item that removes duplicate rows in one click. Ben Collins shares a sample script, and Coupler.io shows how to point it at a specific range. Copy, paste, tweak the range.

Prefer no code? The Remove Duplicates add on in the Google Workspace Marketplace can keep the first or last occurrence, match case, and compare sheets. How-To Geek covers the add on route too. If you'd rather let software handle the busywork, browse our picks of AI tools for spreadsheet tasks.

How to Remove Duplicates Across Two Sheets

What if the duplicates live in two different tabs? The built in tool can't handle that, because it only works inside one selected range. You need a lookup instead. It takes two minutes to set up, and then you can flag matches without copying data around. This is where plenty of tutorials stop short, so pay attention.

Use MATCH to flag overlaps:

=ARRAYFORMULA(IF(ISERROR(MATCH(A2:A, Sheet2!A:A, 0)), "No Match", "Match"))

The Bricks guide walks through this. Filter for "Match," then delete those rows.

Heads up: COUNTIF and MATCH are case insensitive, and trailing spaces break matches, according to ListDiff. So run TRIM first.

Stop Duplicates Before They Happen

Cleaning is good. Prevention is better. A shared sheet collects duplicates the way a desk collects coffee cups, slowly and then all at once. Data validation lets you block repeat entries at the moment someone types them. It takes one rule and about thirty seconds, and it spares you every future cleanup on that column.

Here's how:

1. Select the column (say, B2:B100).
2. Click Data > Data validation > Add rule.
3. Choose Custom formula is.
4. Enter =COUNTIF($B$2:$B$100, B2)=1.
5. Set it to Reject the input.

One limitation: InfoInspired points out that copy and paste bypasses validation. So pair it with Method 3 for safety.

Test It Yourself in 60 Seconds

Guides disagree on edge cases. I've seen conflicting claims about whether UNIQUE cares about capital letters. So don't take anyone's word for it, mine included.

Run this quick test:

1. Type Apple, apple, Apple (with a trailing space), and Banana into A1:A4.
2. In C1, enter =UNIQUE(A1:A4).
3. Copy the column to a new tab and run Remove duplicates.
4. Compare the results.

Now you know exactly how your version of Sheets behaves. That beats guessing, and it makes a great screenshot for your own notes.

Frequently Asked Questions

How do I remove duplicates in Google Sheets?

Select your range, click Data > Data cleanup > Remove duplicates, choose your columns, and confirm. Use =UNIQUE(range) if you'd rather keep your original data intact.

What is the formula to remove duplicates in Google Sheets?

The formula is =UNIQUE(range). It returns each row once, in order of first appearance, without touching your source data.

Does Remove duplicates keep the first occurrence?

In practice it keeps the first row and removes later repeats. Google's help page doesn't state this, so test on a copy of your sheet.

Is Remove duplicates case sensitive?

No. Google's documentation says cells with identical values but different letter cases count as duplicates.

How do I remove duplicates across multiple columns?

Select all the relevant columns before running the tool. A row only counts as a duplicate when every selected column matches.

How do I find duplicates without deleting them?

Use conditional formatting with =COUNTIF($B$2:$B$15,B2)>1, or add a COUNTIF helper column. Both leave your data untouched.

Why do identical looking rows survive?

Usually it's a trailing space or an invisible character. Run Data > Data cleanup > Trim whitespace first, then dedupe again.

Ready to Stop Cleaning Duplicates by Hand?

Here's my honest take.

The built in tool is great for quick jobs. But the moment you're cleaning every week, matching case, comparing tabs, or keeping the last entry, manual methods get slow.

That's where the Remove Duplicates add on earns its place. It handles first or last occurrence, case matching, multiple sheet comparison, and scheduled runs. And it offers a 30 day free trial, so you can test it on your messiest sheet at no cost.

Install it today.

Then watch your next 4,000 row nightmare turn into a two minute task.

You've got the tools now.

Go clean that sheet.