Highlight duplicates with conditional formatting, label them with COUNTIF, or list them with FILTER in Excel 365. You can also mark only the second and later copies, which is what you need before deleting.
Table of Contents
1. Highlight with conditional formatting
Select the cells → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values → OK. Choose Unique in the dialog to highlight values that appear once instead.
2. Label each duplicate
=IF(COUNTIF($B$3:$B$15,B3)>1,"Duplicate","")
3. Mark only the extra copies
=IF(COUNTIF($B$3:B3,B3)>1,"Repeat","")The range grows as the formula is copied down ($B$3:B3, $B$3:B4…), so the first appearance stays blank and only later copies are marked. Filter on Repeat to review or delete them.
4. List duplicates (Excel 365)
=UNIQUE(FILTER(B3:B15,COUNTIF(B3:B15,B3:B15)>1))FILTER keeps values whose count is more than 1, and UNIQUE lists each once. Leave out UNIQUE to see every copy.
5. Duplicate rows (several columns)
=COUNTIFS($B$3:$B$15,B3,$C$3:$C$15,C3)>1TRUE when the same name and city appear together more than once. Use it as a conditional formatting rule to highlight whole rows.
Things to know
- COUNTIF ignores case: Ajay and AJAY count as duplicates.
- Extra spaces make values different: use TRIM first, or the list cleaner.
- To delete duplicates: Data → Remove Duplicates, after making a copy.