Excel's Remove Duplicates Not Working? Here's Why

You run Remove Duplicates, and rows that look exactly the same are still sitting there afterward. This is one of the most common Excel frustrations, and it almost always comes down to the same handful of causes: something invisible is different between the two cells, even though nothing visible is.

Quick answer

Excel's Remove Duplicates compares stored content, not visual appearance. Rows that look identical usually differ in ways you can't see: a trailing or leading space, a non-breaking space instead of a regular one, a zero-width character from a web paste, or a number stored as text in one row and a real number in another. Find the exact difference with the Invisible Character Detector, then normalize the list with the Text Fixer (trim, remove extra spaces, remove duplicates) before pasting it back in.

Find the hidden character causing the mismatch.

Open Invisible Character Detector →

The most common cause: trailing or leading spaces

"Apple" and "Apple " (with a trailing space) look identical in a cell but are different strings as far as Excel's comparison is concerned. This happens constantly with data pasted from web pages, copied from PDFs, or exported from another system that pads values with whitespace. Remove Duplicates does not trim whitespace before comparing, so both rows survive.

The sneaky one: non-breaking spaces

A non-breaking space (used in HTML as  ) renders visually identical to a normal space but has a completely different underlying character code. Text copied from a web page frequently carries these in without any visual sign that anything unusual happened. Two cells that appear character-for-character identical - even when you zoom in - can still fail to match because one has a regular space and the other has this different character entirely.

Zero-width characters

Less common but real: zero-width spaces and similar invisible Unicode characters occasionally make it into pasted text, particularly from certain web sources or copy-paste chains through multiple apps. These take up no visible space at all, so there's no way to spot them just by looking at the cell - they only show up when something like a duplicate check or an exact-match filter fails unexpectedly.

Numbers stored as text

A value typed with a leading apostrophe, imported from a CSV, or pulled from certain database exports can end up stored as text even though it displays as a plain number. The same number entered normally elsewhere in the sheet is stored as an actual numeric type. Visually these are indistinguishable - both show 1024 - but depending on how the comparison is done, Excel may or may not treat them as equal.

Case sensitivity working against you (not for you)

Remove Duplicates is generally case-insensitive, treating "Apple" and "apple" as the same value by default. This is the opposite problem: instead of missed duplicates, it can merge rows you actually wanted kept distinct, such as case-sensitive codes or identifiers where capitalization is meaningful.

Diagnosing the exact problem

Rather than guessing, paste the suspect list into the Invisible Character Detector. It annotates every line with visible markers for non-breaking spaces, zero-width characters, tabs, and carriage returns, plus a summary count of each type found. This turns "these look the same but don't match" into a concrete, visible answer.

Fixing it in one pass

Once you know what's different, the fastest fix is usually not to hunt down individual cells but to normalize the whole list at once. Paste the list into the Text Fixer, enable trim start/end and remove extra spaces, add a case change if case sensitivity is part of the problem, and check remove duplicates. The output is a cleaned, deduplicated list you can paste straight back into your column - no manual per-cell editing required. Google Sheets has the same blind spot for hidden characters; its own options are covered in how to remove duplicates in Google Sheets.

Want the full breakdown of every cause?

A general guide to why identical-looking lines fail to match, across any tool.

Read the guide →

Frequently Asked Questions

Why does Excel's Remove Duplicates leave rows that look identical?

Remove Duplicates compares the exact stored content of a cell, not how it visually appears. A trailing space, a non-breaking space instead of a regular one, or a value stored as text in one row and a real number in another all look identical on screen but are different at the byte level, so Excel treats them as distinct rows.

What is a non-breaking space and how does it get into my data?

It's a space character that looks exactly like a normal space but has a different underlying character code, commonly pasted in from web pages, PDFs, or Word documents. Excel's dedup logic sees it as a different character than a regular space, so "Apple " (regular space) and "Apple " (non-breaking space) are treated as two distinct values.

Is Remove Duplicates case-sensitive?

Generally no - it treats "Apple" and "apple" as duplicates by default. This can create the opposite problem: rows you actually wanted to keep as distinct (say, a case-sensitive product code) get merged when you didn't want them to be.

How do numbers stored as text cause missed duplicates?

A number typed or imported as text (often left-aligned with a small warning indicator) is stored differently from the same number entered directly. Even though "1024" displays identically either way, Remove Duplicates can treat them as different values depending on how the comparison is set up.

How do I find exactly which hidden characters are causing the problem?

Paste the affected list into the Invisible Character Detector. It highlights every non-breaking space, zero-width character, tab, and carriage return directly in your text, with a count of each type found - so you can see exactly what's different before trying to fix it.

What is the fastest way to fix a list before deduplicating?

Run it through the Text Fixer with trim start/end and remove extra spaces enabled, then remove duplicates in the same pass. This normalizes whitespace and removes exact repeats in one step, so Excel's Remove Duplicates - or a fresh paste back into Excel - has consistent data to work with.

Can I dedupe an entire list without Excel at all?

Yes - pasting a list into the Text Fixer with Remove Duplicates checked returns a cleaned list directly, with options for case normalization and whitespace trimming applied before the comparison, so you don't need to open Excel for this step at all.

Will fixing whitespace break the rest of my spreadsheet if I paste the cleaned list back in?

No - trimming spaces and removing exact-duplicate lines only changes the values of the affected cells, not the structure of your sheet. Paste the cleaned output back into the same column and everything else stays intact.