cleanmyexcel LogoCleanMyExcel.io

How to Remove Duplicates in Excel

From exact matches in one click to fuzzy duplicates in a CSV of 50,000 rows: this guide covers every deduplication method, including the AI shortcut.

Deduplicate my file with AI

Method 1: Remove exact duplicates with one click

The fastest built-in option. Removes rows where every selected column matches another row exactly.

  1. Select your data range, including headers.
  2. Open the Data tab.
  3. Click Remove Duplicates.
  4. Tick the columns that define uniqueness (e.g. Email, or First Name + Last Name + Date).
  5. Click OK.

Excel reports how many duplicates were removed and how many unique rows remain.

Method 2: Highlight duplicates without deleting them

Useful when you want to review duplicates manually before removing them.

  1. Select the column or range to check.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. Pick a colour and click OK.

Sort or filter by colour to inspect them. This is the safest method when duplicates may actually be legitimate records.

Method 3: Normalise text before deduplicating

Excel treats "john@email.com " and "john@email.com" as different values because of the trailing space. To catch these:

  1. Add a helper column with =TRIM(CLEAN(LOWER(A2))).
  2. Copy the helper column and Paste Special > Values.
  3. Run Remove Duplicates on the helper column.

This catches whitespace, case and non-printable-character duplicates that the default tool misses.

Method 4: Fuzzy matching with Power Query

For near-duplicates (different spellings, abbreviations, typos), Power Query has a fuzzy matching feature:

  1. Load your table into Power Query (Data > From Table/Range).
  2. Use Merge Queries with the same table and tick Use fuzzy matching.
  3. Adjust the similarity threshold and review matches.

Powerful but requires Power Query fluency. The similarity threshold is also tricky to tune: too high and you miss duplicates, too low and you delete legitimate records.

Method 5: AI deduplication (the shortcut)

For large files, fuzzy duplicates or recurring deduplication jobs, an AI tool handles every method above automatically. It normalises values, understands semantic equivalence ("NY" = "New York"), and flags ambiguous cases for review. See our guide on AI data cleaning for the full picture, or jump straight to the tool.

Deduplicate with CleanMyExcel.io

Related guides

FAQ

How do I remove duplicates in Excel quickly?
Select your data range, then click Data > Remove Duplicates. Choose the columns that define a unique record and click OK. Excel deletes exact-match duplicate rows in one step.
How do I find duplicates in Excel without deleting them?
Use Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel will colour every duplicate cell so you can review them before deciding what to remove.
How do I remove case-insensitive duplicates?
Excel's Remove Duplicates is case-insensitive by default for text. But trailing whitespace and special characters still count as different. Use TRIM and CLEAN to normalise values first, then run Remove Duplicates.
Can Excel detect near-duplicates or fuzzy matches?
Native Excel cannot. For near-duplicates like 'John Smith' vs 'john smith ' vs 'J. Smith', you need Power Query's Fuzzy Matching or an AI deduplication tool that understands semantic similarity.
What is the fastest way to deduplicate large Excel files?
For files with thousands of rows or fuzzy duplicates, manual deduplication takes hours. An AI-powered deduplicator processes any size file in seconds and catches near-duplicates that Excel misses.