Remove duplicates in Excel without losing the row you wanted to keep

Excel's Remove Duplicates silently keeps the first row it sees. How to decide which duplicate survives, dedupe on one or two columns, and stop near-matches slipping through.

The dangerous part of deduplication is not removing rows. It is deciding which row survives, and Excel makes that decision for you without saying so.

Data → Remove Duplicates keeps the first occurrence in sheet order. If the file was sorted by date ascending, you keep the oldest record. If it was exported newest-first, you keep the newest. Same data, opposite outcome, and nothing in the dialog tells you which one happened.

Why "remove duplicates from column" usually fails

People select a single column and hit Remove Duplicates. Excel then compares only that column and deletes the other rows — including columns you needed. If you select the whole table, it compares every column, so two rows for the same customer with different phone numbers are not duplicates at all and both survive.

So there are two separate questions and the dialog conflates them:

1. What makes two rows the same? (the key — usually one or two columns)
2. Which of them do I keep? (first, last, or "the most complete one")

Doing it deliberately in pandas

import pandas as pd

df = pd.read_excel("customers.xlsx", dtype=str)   # dtype=str: do not let 007 become 7

# 1. normalize BEFORE comparing, or near-duplicates survive
for c in ("name", "city", "email"):
    if c in df:
        df[c] = (df[c].fillna("")
                      .str.replace(r"\s+", " ", regex=True)
                      .str.strip()
                      .str.lower())

# 2. choose the survivor: keep the row with the fewest empty cells
df["_filled"] = df.notna().sum(axis=1)
df = df.sort_values("_filled", ascending=False)

# 3. dedupe on the key only
df = df.drop_duplicates(subset=["email"], keep="first").drop(columns="_filled")

df.to_excel("customers_deduped.xlsx", index=False)

The sort_values line is the whole trick. Sort so the row you want is first, then keep="first" is honest. Sorting by a completeness score beats sorting by date, because the newest record is often the most incomplete one — entered in a hurry at a trade show.

Four kinds of near-duplicate that both tools miss

1. Whitespace and case. "Li Wei " and "li wei" are different strings. Normalize first, always. Excel's TRIM only removes leading and trailing spaces, not the double space in the middle.
2. Number stored as text. "13800138000" as text and as number compare unequal in some paths and equal in others. Read everything as string, then decide.
3. Full-width vs half-width. Full-width digits and ASCII digits look identical on screen. NFKC-normalize before comparing if the data came from a Chinese form.
4. Two columns that mean one thing. phone and mobile both filled for some rows and only one for others. Neither column alone is a key.

Checking the result instead of trusting it

After deduplication, verify three numbers before you use the file:

• row count before and after — if you removed 40% of a customer list, you probably picked the wrong key
• count of distinct keys in the output — should equal the output row count
• a spot check of five removed rows against the original — were they really the same entity?

The tool

CSV Doctor is free and reports duplicate rows before you delete anything, along with mixed date formats, numbers stored as text, and columns padded with junk. It exits non-zero when it finds problems, so you can run it in a script before the dedupe step rather than discovering the mess afterwards. No account, no email.