How to Highlight Duplicates in Excel (Without Deleting Them)
You need to see which rows repeat — not lose them to Remove Duplicates — but conditional formatting only paints cells and a COUNTIF helper is fiddly. Here's why, and the one-click way that writes a real column.
Last updated: August 2026
| Customer | Flag | |
|---|---|---|
| Acme | → | (first seen) |
| Acme | → | Duplicate |
| Globex | → | (first seen) |
Why conditional formatting falls short
The usual routes are Home ▸ Conditional Formatting ▸ Highlight Duplicate Values, or a helper column with =COUNTIF($A$2:$A2,A2)>1. The colour a rule applies isn't a value you can filter, sort, or export — it vanishes when you copy the data elsewhere — and the rule silently breaks when rows are inserted or moved. The COUNTIF approach works but needs an absolute range typed by hand, one that drifts the moment your table changes shape.
The 1-click way — Tellsheet's Flag Duplicates
Select the range and run Flag Duplicates. Choose to judge rows by the whole row or by one key column, then pick whether repeats are marked ("Duplicate") or numbered (1, 2, 3). It adds a real column you can sort, filter, and export, and it never removes a row — the "see them" companion to Remove Duplicates. Everything runs on your device. Pairs with Remove Duplicates and Merge Duplicates.
Frequently asked questions
Can I highlight duplicates without deleting them?
Yes — Flag Duplicates marks or numbers repeats and leaves every row in place, so you can review before deciding what to keep.
Why not just use conditional formatting?
Its colour isn't a value you can sort, filter, or export, and the rules break when rows move. Flag Duplicates writes a real column instead.
Can I check by one column instead of the whole row?
Yes — pick a key column to match on that alone, or the whole row to match on every column at once.
Does it change my data?
No — it only adds one flag or numbering column. Your rows and their order stay exactly as they were.
Related Excel guides
Flag duplicates in one click
Flag Duplicates marks or numbers repeated rows in a real column you can filter and export — offline, with nothing deleted.
Get Tellsheet free See pricing