Excel — Remove Duplicates misses rows

Duplicate rows I can't remove — why Excel's Remove Duplicates sometimes misses them

Q: Duplicate rows I can't remove — why Excel's Remove Duplicates sometimes misses them

Remove Duplicates compares the literal underlying value in every selected column, not the way it looks on screen. Two rows that display identically can still differ by a trailing or leading space (including an invisible non-breaking space), or by one cell holding a number stored as text (‘100’ the string) while the matching cell in the other row holds a real number (100 the value) — same display, different underlying value, so Excel keeps both rows. It also compares text case-insensitively by default, so ‘Yes’ and ‘YES’ are treated as the same and would be removed — the surviving surprise duplicates are almost always a whitespace or data-type mismatch, not a casing one.

You run Remove Duplicates, and rows that look absolutely identical are both still there afterward. Excel's Remove Duplicates compares the underlying value of every cell exactly — an invisible trailing space or a number stored as text is enough to make two "identical" rows count as different. Drop your file in below to see what's actually different between them.

Remove Duplicates checks the value, not the appearance

When Excel evaluates whether two rows are duplicates across your chosen columns, it compares each cell's actual stored value — the same thing a formula like =A2=A3 would compare. Two cells can render identically on screen and still fail that test, because whitespace and data type are both part of the underlying value even though neither is visible in the cell.

The two usual culprits

The most common is a trailing space: "Chicago" and "Chicago " look identical in the grid but are different strings. The second is a mismatched data type carrying the same apparent number: one row's quantity came in as a real number, the other's came in as text (often from a CSV import or a VLOOKUP result formatted differently) — 100 and "100" are equal in a loose formula comparison but not in Remove Duplicates' exact match. Non-breaking spaces (character 160, common in text copied from web pages) cause the same problem as regular trailing spaces but are even harder to spot by eye.

What Remove Duplicates does NOT care about

It's worth knowing this isn't a capitalization issue — Excel's duplicate comparison is case-insensitive by default, so "Chicago" and "CHICAGO" are correctly treated as duplicates and one gets removed. If rows are surviving that you expect to be case-based duplicates too, the cause is elsewhere.

How to find and fix the mismatch

Run TRIM and CLEAN across the suspect columns (or TRIM nested with a SUBSTITUTE for CHAR(160)) to strip whitespace before running Remove Duplicates again, and use Text to Columns to force any text-stored numbers back to real numbers. Upload your file above and the checker will point out exactly which "duplicate" rows differ by whitespace or type, so you're not hunting for an invisible character by eye.

More Clean up a messy Excel export — free. guides

← All Clean up a messy Excel export — free. guides