How to remove duplicates in Excel without deleting the wrong rows
Select any cell in the table, open the Data tab and click Remove Duplicates, then tick the columns that define a duplicate and press OK. Excel keeps the first copy of each row and permanently deletes the rest, comparing text without regard to upper or lower case.
Remove Duplicates is one of the few buttons in Excel that destroys data on purpose, and it does it without a preview. It deletes, tells you how many rows went, and leaves you to trust it.
That is fine once you know what it compares, which is the part most explanations skip. Nearly every bad outcome with this button comes from a mismatch between what you meant by duplicate and what Excel meant by it.
What Excel counts as a duplicate
Two rows are duplicates when every ticked column matches. Untick a column and Excel stops caring about it: with only Account ticked, two rows sharing an account number are duplicates even if the names differ, and the second name is deleted along with its row.
- Case is ignored. ADA SHAH, Ada Shah and ada shah are the same value to Remove Duplicates.
- Formatting is ignored. A cell displaying 1,021 and one displaying 1021 hold the same number and match.
- Spaces are not ignored. "AC-1021" and "AC-1021 " are different, and this is the single most common reason duplicates survive the button.
- Formulas are compared on their result, not their text. Two cells showing 40 match even if one is =B2*C2 and the other was typed.
Count them before you delete anything
COUNTIF tells you how many times each value appears, which turns an irreversible deletion into something you can check first. Put this beside the list and read the column:
=COUNTIF($A$2:$A$9,A2)The range is locked with dollar signs so it stays put as the formula is copied down; A2 is relative so each row tests its own value.
| A | B | C | |
|---|---|---|---|
| 1 | Account | Name | Times in list |
| 2 | AC-1021 | Ada Shah | 2 |
| 3 | AC-1044 | Ben Ortiz | 2 |
| 4 | AC-1021 | Ada Shah | 2 |
| 5 | AC-1078 | Cleo Nkemi | 2 |
| 6 | AC-1044 | Ben Ortiz | 2 |
| 7 | AC-1095 | Dev Rao | 1 |
| 8 | AC-1102 | Eli Fontes | 1 |
| 9 | AC-1078 | Cleo Nkemi | 2 |
| 10 |
=COUNTIF($A$2:$A$9,A2)Anything above 1 appears more than once. Here three accounts show a 2, so three rows will go and five will remain, and now that is a number you can check against what Excel reports rather than a surprise.
To flag them instead of counting them, wrap it in IF and read the words: =IF(COUNTIF($A$2:$A$9,A2)>1,"Duplicate",""). Filter on that column and you can look at every repeat before deciding anything.
The exercise built on this does it on a real account list, and the checker reads your formula rather than the number it produced.
Audit duplicate account numbers
Use COUNTIF with a locked range to find repeated account IDs in an employee list. · 5 min · Free account
Removing duplicates based on one column
This is the case people usually mean: one customer per account number, regardless of what else is on the row. In the Remove Duplicates dialog, untick everything except the column that defines uniqueness, and Excel keeps the first row for each value and deletes the rest.
Be clear about what that costs. The rows it deletes may hold information the surviving row does not, a different phone number, a later order date. Excel keeps whichever came first in the sheet, not whichever is best. If the newest record should win, sort by date descending before you deduplicate, so the one you want is the one at the top.
Practise it here
A short list with repeats in it, a real Remove duplicates panel, and a checker that looks at how many rows you actually removed.
A mailing list with two addresses in it twice. Find them without reading all eight rows. · 4 min · No account needed
Worth doing both this and the COUNTIF audit above, in that order. Counting first and deleting second is the habit; the button on its own is the thing that goes wrong.
What goes wrong
These are the four mistakes that come up again and again. Each one is reasonable. That is why people make it.
Selecting one column, then clicking Remove Duplicates.
WhyIt looks like telling Excel which column to check. Excel offers to continue with the current selection, and if you accept, it deletes cells from that column only.
InsteadClick a single cell inside the table and let Excel find the extent. Then untick columns in the dialog to say which ones define a duplicate. Choosing the comparison is a tickbox, never a selection.
Treating duplicates that survive the button as a bug.
WhyThe two rows look identical on screen, so the button appears to have missed them.
InsteadAlmost always a trailing space or a non-breaking space from a web paste. TRIM the column, or use the COUNTIF check. It will report 1 for each, which tells you the values genuinely differ.
Deduplicating before deciding which copy should survive.
WhyExcel keeps the first row it meets and says nothing about what was in the others, so the loss is invisible.
InsteadSort first so the row you want to keep is the one above. If the newest should win, sort by date descending; if the most complete should win, you need to merge rather than deduplicate.
Running it on the only copy of the data.
WhyThere is no preview, no confirmation, and once the file is saved and closed the undo history is gone with it.
InsteadWork on a copy of the sheet, or at minimum count the duplicates first so you know what the button should report. A number you predicted is a number you can check.
Common questions
How do I remove duplicates in Excel?
Click a cell inside the table, open the Data tab, click Remove Duplicates, tick the columns that define a duplicate and press OK. Excel keeps the first copy of each row, deletes the rest, and tells you how many it removed. Alt then A then M is the keyboard route.
Does Remove Duplicates keep the first or the last row?
The first, in sheet order, and there is no setting for it. If the most recent record should survive, sort the table by date descending before deduplicating so the row you want is the one Excel meets first.
Why is Excel not removing my duplicates?
Nearly always invisible whitespace. A trailing space, or a non-breaking space pasted in from a web page, makes two identical-looking values different. Run TRIM over the column first, or check with COUNTIF, if it reports 1 for rows you think are repeats, the values really are not equal.
How do I find duplicates without deleting them?
Use COUNTIF across the column, as =COUNTIF($A$2:$A$9,A2) copied down, and anything above 1 appears more than once. Wrap it in IF to print the word Duplicate instead of a count. Conditional formatting can highlight them in Excel too, under Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
Can I undo Remove Duplicates?
Yes, with Ctrl+Z, for as long as the workbook stays open. Once it has been saved and closed the undo history goes with it and the rows are gone, which is why counting first is worth the thirty seconds.
Is Remove Duplicates case sensitive?
No. ADA SHAH and ada shah are treated as the same value. If case genuinely distinguishes two records, Remove Duplicates cannot tell them apart and you need EXACT in a helper column instead.