What Most People Miss About Removing Duplicates in Excel

Why does Excel keep the first row and toss the second? Why do you lose Sarah Chen’s $45,200 entry but keep her duplicate at $38,750? Why does it sometimes feel like Excel is flipping a coin?

The answer isn’t random — it’s deterministic, predictable, and quietly strict. Excel never removes 'both' duplicates. It always keeps the first occurrence and deletes every subsequent match — even if the later one has better data. That’s the core truth most users don’t realize until they’ve already overwritten their source sheet.

The Problem

You copy-paste client leads from three regional sales reps into one master list. Two entries for 'TechNova Solutions' appear — same name, same email, but different phone numbers and last contact dates. You hit Data > Remove Duplicates, click OK, and later discover the retained record shows an outdated number from March 2023 — while the newer, verified contact (from May 2024) got deleted.

This isn’t a bug. It’s Excel’s design: it preserves row order and treats duplicates as identical *only by selected columns*. If you select only 'Company Name', then 'TechNova Solutions' in row 7 and row 14 are treated as identical — and row 7 stays, row 14 vanishes. No judgment. No preference. Just position.

Row	Company Name	Contact Name	Phone	Last Contact
1	Acme Corp	James Liu	[phone]	2024-02-11
2	BetaSoft Inc	Maya Patel	[phone]	2024-01-29
3	TechNova Solutions	Sarah Chen	[phone]	2023-03-15
4	Veridian Dynamics	David Kim	[phone]	2024-04-02
5	TechNova Solutions	Sarah Chen	[phone]	2024-05-10
6	Orion Labs	Aisha Johnson	[phone]	2024-03-22
7	Acme Corp	James Liu	[phone]	2024-02-11

Rows 1 and 7 are identical across all five columns — so Excel will keep row 1 and delete row 7. Rows 3 and 5 match on 'Company Name' and 'Contact Name' only — but if you select just those two columns in the Remove Duplicates dialog, row 3 stays, row 5 goes. Always.

The Solution

We fix this by controlling what Excel compares — and ensuring the *best* version appears first. Here’s how to guarantee the right row survives:

1. Sort your data strategically before removing duplicates. In our example, sort by 'Last Contact' (descending) so the newest TechNova entry lands above the older one (i.e., row 3 becomes row 5, row 5 becomes row 3). Then run Remove Duplicates on 'Company Name' + 'Contact Name' — and the freshest record stays.
2. Select columns deliberately. Don’t check every column unless you mean it. If 'Phone' differs but you only care about unique companies, uncheck 'Phone' and 'Last Contact'. Excel then ignores those fields entirely during comparison.
3. Always preview. Click 'OK' in the Remove Duplicates dialog — Excel shows a pop-up: 'Excel found 2 duplicate values and removed 2 rows.' That number tells you how many were deleted, not kept. The original row count minus that number equals what remains.

After sorting A2:E7 by Column E (Last Contact, largest to smallest), then applying Remove Duplicates on Columns B and C only, here’s what remains:

Row	Company Name	Contact Name	Phone	Last Contact
1	Acme Corp	James Liu	[phone]	2024-02-11
2	BetaSoft Inc	Maya Patel	[phone]	2024-01-29
3	TechNova Solutions	Sarah Chen	[phone]	2024-05-10
4	Veridian Dynamics	David Kim	[phone]	2024-04-02
5	Orion Labs	Aisha Johnson	[phone]	2024-03-22

Notice: Row 5 from the original table is now row 3 — and it’s the one that survived. That’s 100% intentional, not luck.

Going Further

You can’t make Excel ‘choose’ based on value quality — but you can simulate it. Try this combo: use =COUNTIFS(B$2:B2,B2,C$2:C2,C2) in column F to flag first occurrences (returns 1), then filter for F=1 and copy those rows elsewhere. Or use Power Query: Group By 'Company Name' and 'Contact Name', then aggregate with Table.Max on 'Last Contact' — which actually picks the newest row, not just the first.

Another trick: if you want to see duplicates without deleting, use Conditional Formatting. Select B2:C7 → Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values. Excel highlights both — but doesn’t delete either. Great for auditing.

(Trust me, I learned this the hard way when I wiped out six months of lead scoring data trying to ‘clean up’ before a board meeting.)

When NOT to Use This

Don’t run Remove Duplicates on raw transaction logs where every row matters — even repeats. A customer placing two identical $19.99 orders on the same day? Those are two real events. Deleting one loses revenue context.

Avoid it on datasets with merged cells (Excel blocks the command entirely — you’ll get a warning). Also skip it on tables with formulas referencing adjacent rows (like =A2*1.08 in B2); removing row 5 shifts references, breaking calculations downstream.

And never use it on unprotected worksheets linked to live dashboards. One accidental click on a shared file can cascade errors through 12 pivot tables — we’ve seen it happen twice this month alone.

Keyboard Shortcuts

Action	Shortcut	Notes
Open Remove Duplicates dialog	Alt + A + M	From Data tab — fastest path, no mouse needed
Select entire data range	Ctrl + A (twice)	First Ctrl+A selects current region; second expands to full sheet
Sort descending (e.g., Last Contact)	Alt + A + S + O	Then press Down Arrow → Enter
Toggle Conditional Formatting for duplicates	Alt + H + L + D	Highlights both copies — safe for review