Ai for Sheets.

Published May 23, 2026 — AI for Sheets

How to Find Duplicates in Google Sheets (5 Methods + Fuzzy Matching)

Duplicate rows are the most common data problem in spreadsheets. Two entries for the same customer, three copies of an order, a contact list pasted from multiple sources — all of it clutters reports and skews totals.

Google Sheets has five solid ways to find duplicates, each suited to a different situation. This guide walks through all of them, plus the harder problem most articles ignore: what to do about "fuzzy" duplicates where the rows aren't exactly identical (John Smith vs john smith vs J. Smith).

Quick Answer

The fastest method for most cases:

1. Select the entire column (click the letter A at the top)
2. Go to Format → Conditional formatting
3. Under "Format cells if", choose Custom formula is
4. Enter: =COUNTIF(A:A, A1)>1 (replace A with your column)
5. Pick a fill colour and click Done

Every duplicate value in the column is now highlighted. Read on for the other four methods and when to use each.

Method 1: Highlight Duplicates with Conditional Formatting

The most common technique. It works without changing your data — useful when you want to see duplicates but decide manually what to do about them.

Step by step

1. Select the column or range to check (for example, A2:A1000)
2. Open Format → Conditional formatting
3. Under "Format cells if", choose Custom formula is
4. Enter:

   =COUNTIF($A$2:$A$1000, A2)>1

5. Choose a fill colour and click Done

Every cell that appears more than once is highlighted. Sort by colour to bring duplicates together.

Variations

Across multiple columns (e.g., first and last name):

=COUNTIFS($A$2:$A$1000, $A2, $B$2:$B$1000, $B2)>1

Only second-and-later occurrences (keep the first as "original"):

=COUNTIF($A$2:A2, A2)>1

The changed reference — $A$2:A2 is a growing range, so the count is 1 the first time the value appears and goes up only on later rows.

Highlight unique values instead:

=COUNTIF($A$2:$A$1000, A2)=1

Method 2: Use UNIQUE for a Clean List

If you want a new column containing only the unique values — duplicates stripped out automatically — use UNIQUE.

Basic usage

In an empty cell:

=UNIQUE(A2:A1000)

Returns every distinct value, one per row, in the order they first appeared.

Variations

=UNIQUE(A2:C1000)          <!-- Unique combinations across multiple columns -->
=SORT(UNIQUE(A2:A1000))    <!-- Sorted alphabetically -->
=UNIQUE(FILTER(A2:A1000, A2:A1000<>""))  <!-- Ignoring blanks -->

UNIQUE doesn't change your original data — it creates a separate clean list.

Method 3: Count How Many Times Each Value Appears

Sometimes you need to know how many times each value appears, not just whether it's duplicated. In a new column next to your data:

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

Drag down. You'll see a count for every row: 1 for unique entries, 2 or higher for duplicates.

Useful when reporting on "how many customers placed more than one order", figuring out which duplicates appear most, or sorting by count to see the worst offenders.

For multi-column matching, use COUNTIFS:

=COUNTIFS($A$2:$A$1000, A2, $B$2:$B$1000, B2)

Method 4: The Built-In "Remove Duplicates" Tool

Sheets has a one-click option to delete duplicate rows. Use this only when you're certain you want the data gone — it modifies your sheet directly.

How to use it

1. Select the range containing duplicates
2. Go to Data → Data cleanup → Remove duplicates
3. Check the box for "Data has header row" if applicable
4. Tick the columns to consider when matching
5. Click "Remove duplicates"

Sheets reports how many rows were removed.

When to use it

• You've already verified which duplicates are safe to remove
• The duplicates are exact matches (this tool doesn't do fuzzy matching)
• You don't need to keep duplicates for any audit trail

When not to use it

• You're not yet sure which is the "real" record
• Duplicates differ slightly (one has more complete data than the other)
• You need to keep some duplicates and remove others based on a rule

Method 5: Filter to See Only Duplicates

To look at just the duplicate rows without scrolling through the whole sheet — combine COUNTIF with a filter view.

Step by step

1. In a helper column (say column Z):

   =COUNTIF($A$2:$A$1000, A2)>1

2. Drag down so every row has TRUE or FALSE
3. Click Data → Create a filter
4. Click the filter icon on column Z and uncheck FALSE

You're now looking at only the duplicate rows. Sort them, review them, or copy them to another sheet for cleanup.

The Hard Problem: Fuzzy Duplicates

Every method above finds exact matches. Real-world data rarely cooperates:

• John Smith vs john smith vs John Smith vs Smith, John
• acme@gmail.com vs Acme@Gmail.com
• (555) 123-4567 vs 5551234567 vs 555-123-4567
• Acme Corp vs Acme Corporation vs ACME CORP.

Built-in tools see these as different values. You need to normalize first.

Trick 1: Normalize before comparing

Add a helper column that strips the formatting noise:

Lowercase and trim spaces:

=LOWER(TRIM(A2))

Strip non-numeric characters (good for phone numbers):

=REGEXREPLACE(A2, "[^0-9]", "")

Remove common business suffixes:

=TRIM(REGEXREPLACE(LOWER(A2), " (inc|corp|llc|ltd|co|corporation)\.?$", ""))

Then run COUNTIF on the helper column instead of the raw one.

Trick 2: Approximate matching for names

For names with minor spelling variations, try stripping vowels to compare consonant skeletons:

=SUBSTITUTE(REGEXREPLACE(LOWER(A2), "[aeiou\.]", ""), " ", "")

This also removes periods and spaces, so values like J. Smith and J.Smith normalize to the same skeleton.

Trick 3: AI for true fuzzy matching

This is where built-in formulas hit a wall. Genuinely fuzzy matching — recognising that J. Smith — Acme Corp and John Smith at Acme are probably the same person — requires understanding meaning, not just string manipulation.

A workable approach: normalize aggressively with the methods above, deal with the obvious duplicates first, then manually review the suspicious leftovers.

Common Mistakes to Avoid

Forgetting to anchor your range. A formula like =COUNTIF(A2:A1000, A2) shifts as you drag down, giving wrong counts. Always use =COUNTIF($A$2:$A$1000, A2) — the dollar signs lock the range.

Including the header row. If your data has headers, start at row 2 (A2:A1000), not row 1. Otherwise the header value gets counted with the data.

Applying "Remove duplicates" without a backup. Make a copy of your sheet first. Once rows are deleted there's no undo beyond version history.

Assuming duplicates are always bad. Some datasets intentionally have repeated values (a customer who placed three orders should appear three times in the orders table). Only remove duplicates you've confirmed are errors.

Frequently Asked Questions

What is the fastest way to find duplicates in one column?

Use conditional formatting with =COUNTIF(A:A, A1)>1. It highlights every repeated value without deleting any rows.

Does Google Sheets remove fuzzy duplicates automatically?

No. The built-in remove-duplicates tool only catches exact matches. For fuzzy duplicates, normalize values first (for example with LOWER, TRIM, and REGEXREPLACE) and then compare helper columns.

Should I use Remove duplicates or formulas?

Use formulas when you need to review matches before deleting. Use Remove duplicates only when you're sure duplicate rows are safe to remove.

More Guides

How to Use Gemini AI in Google Sheets

Free API vs. paid side panel — step-by-step setup, real-world use cases, and which method fits your workflow.

May 23, 2026

AI Formula Generator for Google Sheets

Write any formula in plain English. Free and paid options compared, with prompt patterns that get better results.

May 23, 2026

ChatGPT in Google Sheets

5 methods compared — Apps Script, add-ons, browser extensions, Zapier, and copy-paste. Honest pros and cons at every price point.

May 23, 2026

How to Find Duplicates in Google Sheets

5 methods including conditional formatting, UNIQUE, COUNTIF, the built-in tool, and fuzzy matching for near-duplicates.

May 23, 2026

Send Feedback