The answer in one sentence: Excel's Remove Duplicates uses exact string matching — two cells must be byte-for-byte identical to be considered duplicates. Real-world data is almost never that clean.
The Three Invisible Culprits
1. Trailing Spaces
"Acme Corp" and "Acme Corp " (with a trailing space) are different strings in Excel's view. LEN("Acme Corp") = 9. LEN("Acme Corp ") = 10. One extra invisible character means Excel treats them as different values — even though they look identical on screen.
How common is this? Extremely common. Copy-paste from web forms, CRM exports, and data entry by humans all produce trailing spaces regularly. On some data sources, 15–25% of records have at least one field with leading or trailing whitespace.
Sohovi finds gaps, duplicates, and format errors in your CRM data — so your team is working from records they can trust.
The fix: Apply =TRIM(A2) to the column, paste the results as values, then run Remove Duplicates on the cleaned column.
2. Casing Differences
"ACME CORP", "Acme Corp", and "acme corp" are three different strings to Excel. Remove Duplicates is case-sensitive by default. If your data has mixed casing — which happens constantly in real-world imports — you'll miss duplicates.
Example:
- "john@gmail.com" and "JOHN@GMAIL.COM" — same email address, not caught
- "New York" and "new york" and "NEW YORK" — same value, not caught
- "Smith, John" and "smith, john" — same name, not caught
The fix: Normalize to lowercase first with =LOWER(A2), paste as values, dedup on the normalized column.
3. Near-Duplicates (Name Variants, Typos)
This is the fundamental limitation: Excel has no concept of similarity. "John Smith" and "Jon Smyth" are completely different strings to Excel — it doesn't know they might be the same person with a typo. Same with:
- "Acme Corporation" and "Acme Corp"
- "St. Louis" and "Saint Louis"
- "MacDonald" and "McDonald"
These are your most valuable duplicates to find — the cases where a person is in your system twice with slightly different data — and Excel cannot find any of them.
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
The Combined Problem: Why "0 Duplicates Found" Is Almost Always Wrong
Take a contact list from a real CRM export:
- 30% of records have at least one field with trailing whitespace
- 20% have inconsistent casing in the company name field
- 8% have name variant issues (abbreviations, middle name included/excluded, etc.)
Run Remove Duplicates and you might get "0 duplicates found" on a dataset with a real duplicate rate of 15–20%. The tool worked correctly — it found exactly zero exact duplicates. The problem is that exact matching is the wrong standard for real customer data.
The Workaround for Whitespace and Casing
You can get Excel to find more duplicates with helper columns:
- Add column:
=LOWER(TRIM(A2))— normalizes both casing and whitespace - Copy this column and Paste Special → Values to get static text
- Run Remove Duplicates on the normalized helper column
- Use the identified duplicates to clean the original column
This catches whitespace and casing issues. It still doesn't catch name variants, typos, or format differences.
What Actually Fixes the Problem
For real-world customer data deduplication, you need fuzzy matching — a tool that scores how similar two records are rather than checking if they're identical. Sohovi's free duplicate remover uses edit distance and phonetic matching to find "Jon Smyth" as a potential match for "John Smith" — and shows you the match with a similarity score before any change is made.
Frequently Asked Questions
Q: Is there a way to make Excel's Remove Duplicates case-insensitive? Not directly. The workaround is to create a LOWER() helper column and run Remove Duplicates on that column rather than the original.
Q: If I use TRIM() and LOWER() first, will Remove Duplicates find all my duplicates? It will find significantly more — essentially all whitespace and casing duplicates. It will still miss name variants, abbreviations, and typos.
Q: Is there an Excel formula that does fuzzy matching? Not natively. Power Query has a "Fuzzy Merge" option that does approximate matching for table joins, but it's not designed for in-table deduplication and has limited threshold control.
Q: How do I know how many duplicates I actually have? Profile your data with a tool that supports fuzzy matching. Set the similarity threshold to 80% and see how many pairs get flagged. That gives you a realistic picture of your actual duplicate rate vs. what Excel reports.
Catch the duplicates Excel calls "not found." Paste your data into our free duplicate remover — fuzzy matching, runs in your browser, nothing uploaded, review before merging.