How to Remove Duplicate Rows in Excel: 3 Methods (and What Each One Misses)

The fastest fix: Select your data → Data tab → Remove Duplicates → pick which columns to check → click OK. Excel removes exact duplicate rows and tells you how many were removed. This works for clean, consistent data and takes under 30 seconds.

For fuzzy matches, case variants, or trailing-space duplicates, read on — Excel's built-in tool misses all of those.

---

Method 1: Data Tab → Remove Duplicates (Fastest)

1. Click anywhere inside your dataset
2. Go to Data → Remove Duplicates (in the Data Tools group)
3. A dialog appears — check the columns you want to use to identify duplicates (uncheck columns you want to ignore, like an auto-generated ID)
4. Click OK

Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.

Excel will delete duplicate rows, keeping the first occurrence of each duplicate set. It shows a summary: "X duplicate values found and removed."

What it misses:

• "Acme " and "Acme" (trailing space — Excel sees these as different)
• "ACME" and "Acme" (different casing — also different to Excel)
• "John Smith" and "Jon Smyth" (fuzzy duplicates — Excel has no concept of similarity)
• "john@gmail.com" and "JOHN@GMAIL.COM" (case in email addresses)

---

Method 2: UNIQUE() Formula (Non-Destructive)

If you want to extract unique values to a new column or range without destroying the original data:

=UNIQUE(A2:D100)

This returns a unique list of rows from the range — it's non-destructive and updates dynamically if your source data changes.

Limitation: UNIQUE() is available in Excel 365 and Excel 2021+. It uses exact matching only — same case-sensitivity and whitespace issues as Method 1.

For a single column (e.g., finding unique email addresses): =UNIQUE(B2:B100)

---

Method 3: Power Query (For Repeatable Cleanup)

Power Query is the right approach when you're cleaning the same file format repeatedly:

1. Select your data → Data → From Table/Range (or Get Data → From Table/Range)
2. In Power Query Editor: Home → Remove Rows → Remove Duplicates
3. Click Close & Load

Power Query creates a query you can re-run when new data arrives — click Refresh and duplicates are removed automatically. Good for recurring exports from a CRM or database.

Limitation: Still exact-match only. And it can be overkill for a one-time clean.

---

The Problem Excel's Remove Duplicates Can't See

Here's a real example. Paste this into Excel and run Remove Duplicates on the company column:

| Name | Company | Email | |------|---------|-------| | Sarah Chen | Acme Corp | sarah@acme.com | | Sarah Chen | Acme Corp | sarah@acme.com | | Sarah Chen | Acme Corp | sarah@acme.com | | James Park | Acme Corp | james@acme.com |

Excel correctly finds and removes the three Sarah Chen duplicates — great.

Now try this version:

| Name | Company | Email | |------|---------|-------| | Sarah Chen | Acme Corp | sarah@acme.com | | Sarah Chen | Acme Corp | sarah@acme.com | | Sarah Chen | ACME CORP | sarah@acme.com | | Jon Smith | Acme Corp | j.smith@acme.com | | John Smith | Acme Corp | john.smith@acme.com |

Excel reports: "0 duplicate values found and removed."

Because "Acme Corp" and "Acme Corp " (with a trailing space) are different strings. And "ACME CORP" is different again. Jon Smith and John Smith are different names entirely — Excel has no way to know they might be the same person.

---

Fixing the Misses Manually

You can work around Excel's limitations with helper columns:

For whitespace: Add a column: =TRIM(A2) — removes leading/trailing spaces. Then dedup on the helper column.

For casing: Add a column: =LOWER(A2) — normalizes to lowercase. Then dedup on that column.

For both: =LOWER(TRIM(A2))

This works but it's tedious for multi-column dedup, and it does nothing for "Jon" vs "John" or "Smyth" vs "Smith."

---

Catching the Duplicates Excel Misses

For fuzzy duplicate detection — typos, name variants, case differences, whitespace — use a tool with fuzzy matching. Sohovi's duplicate row remover runs in your browser, processes everything locally (nothing uploaded), and lets you set a similarity threshold so you review matches before merging.

The workflow:

1. Paste your data or upload your CSV
2. Choose which columns to match on
3. Set the similarity threshold (strict to catch only near-identical records; looser to catch more variants)
4. Review flagged matches — you approve or reject each merge before anything changes

This catches the "0 duplicates found" false negatives that Excel produces on messy real-world data.

---

Frequently Asked Questions

Q: Does Excel's Remove Duplicates delete the first or second occurrence? It keeps the first occurrence and deletes subsequent duplicates. If the order matters (e.g., you want to keep the most recently updated record), sort your data by the relevant date column — descending — before running Remove Duplicates, so the "first" occurrence is the most recent one.

Q: How do I find duplicates without deleting them (just highlight them)? Use conditional formatting: Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. This highlights duplicates in the selected range without removing anything. Useful for review before committing to deletion.

Q: Why does Excel say "no duplicates found" when I can clearly see duplicate entries? Almost always one of three reasons: (1) trailing whitespace in one record but not the other, (2) casing difference ("ABC" vs "abc"), or (3) a non-printing character (like a line break or non-breaking space) embedded in one cell. Use =LEN(A1) on both cells — if the lengths are different, there's a hidden character.

Q: Can I remove duplicates from only part of a column, not the whole thing? Yes — select just that range before opening Data → Remove Duplicates, and Excel will only check within that selection.

Q: Does Remove Duplicates work across multiple sheets? No — Remove Duplicates only works on the current sheet's selected range. To find duplicates across sheets, you'd need COUNTIF formulas referencing both sheets, or a Power Query that combines them.

---

Excel's Remove Duplicates is the right first step for clean data. The moment you're dealing with real-world messy data — contacts exported from a CRM, leads from a form, customers from multiple sources — you'll want fuzzy matching. Paste your data into our free duplicate remover — it runs in your browser, nothing is uploaded, and it catches the cases Excel can't see.