How to Find and Remove Duplicates in Excel — Safely

Data → Remove Duplicates is one click, and that's the problem. It deletes rows immediately, keeps the first occurrence of each duplicate without asking, and tells you only how many rows it removed — not which ones. On a customer list or an orders export, that's a gamble.

The safe workflow is: count → review → decide which copy wins → then remove. Here's each step.

Step 1: Count duplicates before touching anything

For a single key column (say, email in column B):

=COUNTIF($B$2:$B$5000, B2)

Any value above 1 marks a duplicate group. For duplicates defined by a combination of columns (same customer AND same date AND same amount):

=COUNTIFS($A$2:$A$5000,A2, $C$2:$C$5000,C2, $D$2:$D$5000,D2)

Add this as a helper column named Copies, then filter to >1 to see every affected row at once.

To highlight instead: select the range → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Note this checks single-column duplicates only.

Step 2: Decide what "duplicate" actually means

The subtle bug in most dedup jobs isn't the removal — it's the definition:

• Case and whitespace. ACME Corp vs acme corp are different strings to COUNTIF unless you normalize first with TRIM/LOWER. Clean before you count (full cleanup checklist).
• Same key, different data. Two rows with the same email but different phone numbers aren't a duplicate — they're a conflict that needs merging, not deleting.
• Legitimate repeats. In a transactions sheet, the same customer appearing twice is normal. Dedup keys must include enough columns to identify a true double-entry.

Step 3: Choose which copy survives

Excel's built-in tool always keeps the first occurrence — the top-most row. If you'd rather keep the most recent record, sort by your date column descending before removing duplicates, so the newest is first in each group.

To mark survivors with formulas instead (keep first occurrence):

=IF(COUNTIF($B$2:B2, B2)=1, "keep", "remove")

The expanding range $B$2:B2 counts only occurrences up to the current row, so the first copy gets "keep" and later ones get "remove". Filter and delete the "remove" rows — after review.

Step 4: Remove, and verify the count

However you delete, verify: rows before − rows after should equal the number of "remove" flags. If it doesn't, stop and check.

The one-instruction version

With AI for Excel, the whole workflow — normalize, count, review, choose survivors, delete, verify — is a described task:

"Find duplicate rows by email, ignoring case and extra spaces. Where duplicates have conflicting phone numbers, flag them instead of deleting. Otherwise keep the most recent row by the Updated column. Show me a summary before finalizing."

Because the add-in snapshots the workbook before the write and reports exactly which rows were removed, the dedup is reviewable and reversible — the two properties the built-in button lacks. See how the safety model works, or start with the free download.

FAQ

How do I remove duplicates but keep the latest entry?

Sort by your date column descending first — Remove Duplicates keeps the first row of each group, which after sorting is the newest. Or use a helper formula to flag all but the latest, review, then delete.

Why does Excel find duplicates that look different?

Usually the opposite happens — near-identical values aren't matched because of trailing spaces, case, or numbers stored as text. Normalize with TRIM and consistent types before counting duplicates.

Can Excel show which rows were removed as duplicates?

Not with the built-in tool — it only reports a count. Use the helper-column method, or an assistant that logs each removed row, if you need an audit trail.