Remove Duplicates
The tick boxes are the whole decision here, and they read backwards. Leaving fewer columns ticked destroys more rows, not fewer, because what counts as a repeat is defined by the columns you kept rather than by the row as a whole. Whichever combination is chosen, the topmost occurrence survives in whatever order the sheet happens to be sorted at that moment and the rest are deleted in place, with no link back and nothing to undo once the file has been closed. So the real fork on this hub is whether you are deleting at all: flagging, colouring and counting all answer the same question without touching a row.
A worked example
A2:D13 holds twelve order rows — Order ID, Customer, Date, Amount. Order 1002 was exported three times and order 1001 twice, so three of the twelve rows are exact repeats across every column.
Click A2 > Data > Remove Duplicates > leave all four columns ticked > OK
Excel reports "3 duplicate values found and removed; 9 unique values remain" and the sheet closes up to nine rows. Untick everything except Order ID and it removes the same three rows here — but on a file where the same order appears with two different amounts, that narrower tick keeps whichever amount happened to be listed first and destroys the other without mentioning it.
Which one do I need?
| If you want to… | Use |
|---|---|
| Entire rows repeat and you want one of each left | Data > Remove Duplicates with every column ticked |
| One column is the identity — an order ID, an email — and the other columns differ | Tick that column alone; Excel keeps the topmost row and discards the rest, so sort the data the way you want it kept before running it |
| You want to see what repeats before anything disappears | A COUNTIF helper column flags every repeat and changes nothing |
| Colouring them is enough | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values |
| You need a de-duplicated list elsewhere with the source untouched | =UNIQUE(range) on 365/2021, or Data > Advanced Filter > Copy to another location with Unique records only ticked |
| How often each value occurred still matters | Count before you delete — the counts vanish with the rows |
| Rows that look identical survive the run | Trailing spaces, or a number stored as text sitting beside a real number; TRIM and convert first, then rerun |
| Only the rows currently visible in a filtered range should be touched | Copy the visible cells to a fresh sheet first — Remove Duplicates processes hidden and filtered-out rows as well |
Frequently asked questions
Does Remove Duplicates delete both copies or keep one?
It keeps one — the first occurrence in the current row order — and deletes the later ones. Because "first" means topmost as the sheet is sorted right now, sorting the data before you run it is how you control which row survives.
Can I undo Remove Duplicates?
Ctrl+Z restores the rows if you press it in the same session, before the workbook is closed. After a save and close the deleted rows are simply gone; there is no history to recover them from, which is why the safe habit is duplicating the sheet and running the tool on the copy.
Why did Excel leave rows that look exactly the same?
Something invisible differs. Leading or trailing spaces, non-breaking spaces pasted in from a web page, a number stored as text against a real number, or a date that is text on one row and a date serial on another all read as different values. Run TRIM and fix the column types first, then repeat the removal.
Does it skip rows hidden by a filter?
No. Remove Duplicates works across the whole range it detects, hidden rows and filtered-out rows included, so narrowing the view first does not protect anything. To limit it to what you can see, select the range, press Alt+; to grab visible cells only, and copy those to a new sheet before deduplicating.
New guides and tools, once a month
DE + EN · double opt-in · no spam