Duplicate rows creep in when lists are merged or exports overlap. There are three quick ways to deal with them, and they behave differently.
In this article
1. Data → Remove Duplicates (permanent)
- Click inside the data → Data → Remove Duplicates.
- Tick the columns that define a duplicate — e.g. only Invoice, or Name + Date together.
- OK — Excel reports how many rows it removed.
It deletes rows in place, so work on a copy. It keeps the first occurrence of each duplicate.
2. UNIQUE function (live, Microsoft 365 / 2021+)
=UNIQUE(A2:C200) ' unique rows
=UNIQUE(B2:B200) ' unique values from one column
=SORT(UNIQUE(B2:B200)) ' sorted unique list
=UNIQUE(B2:B200, , TRUE) ' values that appear exactly onceThe source stays untouched and the result updates when the data changes — ideal for dropdown lists and summaries.
3. Advanced Filter (every version)
Data → Advanced → Copy to another location → tick Unique records only. It copies a de-duplicated list elsewhere without deleting anything.
Which one?
| Need | Use |
|---|---|
| Clean a list once, for good | Remove Duplicates |
| A list that stays up to date | UNIQUE |
| Older Excel, keep the original | Advanced Filter |
| Just see duplicates first | Conditional formatting: =COUNTIF($A$2:$A$200,$A2)>1 |
💡 Spaces make “Asha” and “Asha ” different values. Run TRIM (or Flash Fill) on the column first.