How to remove duplicate rows in Excel or a CSV

Excel and Google Sheets both have a built-in "Remove Duplicates" feature, and it works well for the one case it is built for: rows that match character for character. Almost every real duplicate is not that clean, which is why a list can come back from a deduplication pass looking tidy and still contain the same person four times.

What "Remove Duplicates" actually compares

The tool compares the value in each selected column across every row, and removes a row only when every checked column matches. The comparison is literal: it has no notion that two values might mean the same thing.

Whitespace defeats the comparison. Case does not.

Whitespace can prevent values from matching in tools that compare the text as stored. Normalise the fields you intend to compare, and check how your chosen tool treats casing and spaces.

Casing goes the other way, and catches people out for being more forgiving than expected rather than less. Both Excel and Google Sheets compare case-insensitively in their built-in duplicate removal, so "Priya@Example.com" and "priya@example.com" are treated as the same value and one of them is deleted. Neither tool offers a switch for this.

That is usually what you want on an email column and occasionally not what you want at all — on a case-sensitive product code or an externally-issued ID, where ab12 and AB12 are two different things, the built-in tool will quietly collapse them. Case-sensitive detection is a separate job, done with a formula rather than the menu, because EXACT() is one of the few comparisons in either program that respects case:

That flags a value with a genuine character-for-character twin somewhere in the column, leaving case variants alone.

Normalize before you deduplicate

The fix for the whitespace rows — the ones the built-in tool genuinely misses — is to clean the columns you are matching on before you compare them, so that formatting differences cannot hide a real match. A helper column does it:

LOWER() rather than PROPER() here, deliberately. You are not trying to make this column look right — you are making it comparable, and it gets deleted afterwards. Lowercasing is the safer choice because it has no opinions: PROPER() would turn "ABC Pty Ltd" into "Abc Pty Ltd" and "mcdonald" into "Mcdonald", which is fine for a throwaway matching key but a trap if you ever keep the column.

Run Remove Duplicates against the helper column, then delete it. If the values you are matching may have come from a web page or a PDF, wrap it once more to catch non-breaking spaces, which TRIM() alone leaves in place:

Decide which row to keep

When you remove duplicates using only selected columns, other fields may differ. Check those fields before deletion: a row with the same email may contain a different phone number or newer information. Comparing all columns retains rows that differ in any compared value.

Deduplicate as-is and row 2 is deleted — the newer record with the phone number.

So sort before you deduplicate, not after. Sort by the column that indicates recency or completeness, descending, so the row you want to keep is the one the tool encounters first. If completeness is spread across rows rather than concentrated in one — row 1 has the phone, row 2 has the address — no sort saves you, and the honest answer is that those rows need merging rather than deduplicating, which is a manual job or a scripted one.

Whole-row versus single-column duplicates

If you tick several columns in the dialog, a row is removed only when all of them match. This is the setting people get wrong most often, because the two readings produce very different results on the same file:

A signup date that differs by one day keeps both rows under whole-row matching.

Decide which of those two jobs you are actually doing before you open the dialog. "One record per person" almost always means matching on a single identifying column, and almost never means ticking every box.

What this approach cannot catch

Normalizing fixes duplicates that differ by formatting. It does nothing for duplicates that differ by content:

Four records, one person. Only the second is reachable by matching, and only if you match on email alone.

The second row is the argument for choosing your key deliberately, since matching on email catches it and matching on the whole row does not. The third and fourth are past the reach of any key: no column in either row matches the original character for character. They need a similarity comparison rather than an equality one — asking how close two values are, not whether they are identical. That is beyond what a spreadsheet formula does comfortably, and it is also a judgement call rather than a cleanup: two people can genuinely share a surname and a household address, and deleting one of them is worse than keeping a duplicate.

For that reason the right output is a flag, not a deletion. Anything automatic here should tell you which rows look related and leave the decision to you.

Common questions

Why does Remove Duplicates report zero removals when I can see repeats? Check what you asked it to compare before assuming the data is at fault. The most common cause is the column selection: with several columns ticked, a row is only removed when all of them match, so one differing signup date or record ID keeps a pair that is otherwise identical. Confirm the selected columns and that the range covers every row, then look for a trailing space in the key column — a space defeats the comparison, though casing will not.

Can I see the duplicates before deleting them? Yes, and it is worth doing. Conditional formatting → Highlight Cells Rules → Duplicate Values marks them without changing anything, so you can check the tool is matching what you think it is before committing.

Does deduplicating a CSV in Excel risk anything else? Yes — opening the file at all can strip leading zeros from phone numbers and postcodes before you deduplicate anything. Import via Data → From Text/CSV with those columns set to Text.