Highlight Duplicates in Excel Without Deleting Rows
Highlighting is a review step: your rows remain in place. First decide whether “duplicate” means one repeated ID or a repeat of every selected field.
Choose the matching policy
| Rule | Formula for first data row | Expected rows |
|---|---|---|
| Repeated ID in column A | =COUNTIF($A$4:$A$9,A4)>1 | A-01: 4, 6, 7; A-02: 5, 9 |
| Identical A:C row | =COUNTIFS($A$4:$A$9,$A4,$B$4:$B$9,$B4,$C$4:$C$9,$C4)>1 | Rows 4 and 6; rows 5 and 9 |
| Only later occurrence | =COUNTIFS($A$4:$A4,$A4,$B$4:$B4,$B4,$C$4:$C4,$C4)>1 | Rows 6 and 9 |
Row 7 shares ID A-01 but has a different Region, so it is an ID duplicate and not an identical full row. The downloaded sheet exposes the three tests in D:F; change a source value in A:C and compare the booleans.
Apply a rule to your own range
- Make a copy of the sheet. Select only the range to color: A4:A9 for IDs or A4:C9 for full rows.
- Open Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format. Enter the corresponding formula above, choose a fill, and save.
- In Manage Rules, check Applies to matches your data range. The formula must refer to the top-left selected data row. Extend both its fixed comparison range and Applies to when new rows arrive.
The workbook has three rules: repeated IDs are yellow in A4:A9, identical rows are green in A4:C9, and later occurrences are purple in the separate F4:F9 helper range. If formatting looks surprising, inspect rule order and the boolean helper columns. A blank ID can match another blank. If blanks should never light up, prepend AND($A4<>"", ...) to the rule.
Version and limits
Microsoft documents formula-based conditional formatting in current Excel and shows the COUNTIF duplicate pattern. Ribbon labels vary between Windows, Mac, and web; confirm the rule editor in your version. These examples compare values as Excel compares them, not visually similar text containing hidden spaces. For cleanup first, use the hidden-spaces diagnostic below.