Excel's Remove Duplicates tool deletes repeated rows in one click. That is also why people lose data: it keeps the first row and silently drops the rest. Before you delete anything, decide whether you need to find the duplicates, highlight them, or build a unique list and leave the original alone.
Worked example used below — a short order list:
| A (Order ID) | B (Customer) | C (Amount) |
| 1001 | Lee | 40 |
| 1002 | Patel | 75 |
| 1001 | Lee | 40 |
| 1003 | Chen | 20 |
| 1002 | Ortiz | 55 |
Method 1: Remove Duplicates tool (Data tab)
Use this when you trust the data and just need a clean table.
- Click any cell inside the range
- Data → Remove Duplicates
- Tick the columns that define a duplicate
- Click OK
This is destructive. Work on a copy, or undo immediately with Ctrl+Z if the count looks wrong.
Method 2: Find duplicates first with COUNTIF
This is the method worth practicing. It does not delete anything. You see every repeat, then you decide.
In D2, next to the first order:
=COUNTIF($A$2:$A$6,A2)
Copy down. Results:
- 1 — that Order ID appears once
- 2 or more — it is a duplicate
To flag only the second and later occurrences (leave the first unmarked):
=COUNTIF($A$2:A2,A2)>1
The range grows as you copy down. The first 1001 returns FALSE. The second 1001 returns TRUE.
To write a label instead of TRUE/FALSE:
=IF(COUNTIF($A$2:$A$6,A2)>1,"Duplicate","Unique")
Method 3: Highlight duplicates (no deleting)
- Select A2:A6
- Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
- Pick a fill colour → OK
=COUNTIF($A$2:A2,A2)>1
applied to A2:A6.
Method 4: UNIQUE — a clean list, original left intact
In Microsoft 365 or Excel 2021, spill a distinct list of Order IDs without touching the source:
=UNIQUE(A2:A6)
For unique rows across several columns:
=UNIQUE(A2:C6)
UNIQUE is the right tool when you need a lookup list or a dropdown source and you do not want to delete history.
Which method to use
| You need | Use |
| A cleaned table, and you trust the first row | Remove Duplicates tool |
| To review before anything is deleted | COUNTIF helper column |
| To show the problem on a shared sheet | Conditional formatting |
| A unique list, original data kept | UNIQUE |
| Case-sensitive matching | COUNTIF cannot do this; use SUMPRODUCT((EXACT($A$2:$A$6,A2))*1) |
Why "0 duplicates found" is usually a formatting problem
The values look the same and are not.
- Trailing spaces — "Lee " vs "Lee". Fix with =TRIM(A2) in a helper column, then remove duplicates on that column.
- Numbers stored as text vs real numbers. Check alignment, or Data → Text to Columns to convert.
- Invisible characters. Wrap with CLEAN.
Frequently Asked Questions
How do I delete duplicates in Excel and keep the first row?
Data → Remove Duplicates. Excel always keeps the first occurrence of each unique key. There is no setting to keep the last row instead — sort first if you need a different survivor.
How do I remove duplicate rows based on one column only?
In the Remove Duplicates dialog, tick only that column. Other columns are ignored when deciding what is a duplicate, but the entire row is still deleted.
Does Remove Duplicates care about letter case?
No. "Apple", "apple", and "APPLE" are treated as the same value.
Can I remove duplicates in one column without deleting the rest of the row?
No. The built-in tool always deletes full rows. To get a unique list of one column, copy that column to a new sheet and run Remove Duplicates there, or use =UNIQUE(A2:A6).
What is the difference between Remove Duplicates and UNIQUE?
Remove Duplicates edits the source in place. UNIQUE is a formula that spills a new list and leaves the source alone. Prefer UNIQUE unless you are deliberately cleaning a working table.