How to Highlight Duplicates in Excel
Highlight duplicate values or whole duplicate rows in Excel with built-in conditional formatting or a COUNTIF formula rule.
Method 1: use the Duplicate Values rule
Select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and confirm. Excel highlights every occurrence of a repeated value, including the first one, and ignores letter case. No rows are deleted.
Apply the rule to the key column rather than the whole table when one field defines duplication. If you select several unrelated columns, each cell is judged against the whole selection, which is not the same as finding duplicate records. Use Conditional Formatting > Manage Rules to inspect or change the Applies to range.
Method 2: highlight with a COUNTIF formula
A formula rule offers control. Select A2:A100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =COUNTIF($A$2:$A$100,A2)>1. The fixed range is counted for every selected cell, while A2 changes with each row.
To highlight only the second and later occurrences, use the expanding range =COUNTIF($A$2:A2,A2)>1. It counts from the first data row down to the current row, so the first copy stays unmarked. Sorting changes which record is considered first.
Two variants are common. =COUNTIF($A$2:A2,A2)=1 marks first occurrences only, which helps when you want to see the records that would survive a cleanup. COUNTIF ignores case, so when “ABC” and “abc” must count as different keys, use =SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1 instead.
Highlight whole duplicate rows or multi-column keys
To shade complete rows based on an ID in column A, select the full data range, for example A2:D100, and use =COUNTIF($A$2:$A$100,$A2)>1. Lock the key column but leave the row relative, and confirm that the top-left cell of Applies to corresponds to the row used in the formula.
For a composite key, use COUNTIFS: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1 highlights rows where both fields repeat. A helper column that concatenates cleaned key parts can also work, but choose a separator that cannot appear in the source.
Blanks, spaces, and maintenance
Truly empty cells are not highlighted by Duplicate Values or by the COUNTIF rule. Cells that hold a formula result of "" or only spaces can look blank yet be treated as repeats. Guard the rule with =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1).
Open Manage Rules and check the formula, the Applies to range, and the rule order. Expand the range when new records are added, or convert the source to an Excel Table so formatting can extend with new rows. Conditional formatting is visual only: calculate a helper flag when another formula, export, or audit needs the duplicate status, because filtering by color is only a convenience.
Your turn
In B4, count how many times the ID in A4 appears from A2 down to A4 (the expanding range that marks later copies only).
| A | B | |
|---|---|---|
| 1 | Invoice ID | Occurrence |
| 2 | INV-7 | |
| 3 | INV-8 | |
| 4 | INV-7 | |
| 5 | INV-9 | |
| 6 | INV-7 |
Answer: =COUNTIF($A$2:A4,A4)
Worked example
Names in A2:A7; “ana” is the same as “Ana” because the comparison ignores case, and A7 is empty. The expanding range marks only the later copies.
| A | |
|---|---|
| 1 | Name |
| 2 | Ana |
| 3 | Bo |
| 4 | Ana |
| 5 | Cy |
| 6 | ana |
| 7 |
| Formula | Result |
|---|---|
=COUNTIF($A$2:$A$7,A2)>1 | TRUE |
=COUNTIF($A$2:A2,A2)>1 | FALSE |
=COUNTIF($A$2:A4,A4)>1 | TRUE |
=COUNTIF($A$2:A6,A6) | 3 |
=COUNTIF($A$2:$A$7,A3)>1 | FALSE |
=AND(A7<>"",COUNTIF($A$2:$A$7,A7)>1) | FALSE |
Quick mistake checklist
- Selecting the whole table when only one key column defines duplicates.
- Using the wrong relative row in a formula rule.
- Assuming highlighted rows have been removed or filtered.
Frequently asked questions
Does highlighting duplicates delete anything?
No. Conditional formatting only changes the appearance.
How do I highlight only later copies?
Use an expanding COUNTIF rule such as =COUNTIF($A$2:A2,A2)>1.
How do I highlight an entire row?
Apply a formula rule to the row range and lock the key column, for example $A2.
Why are some blank-looking cells highlighted?
They may hold spaces or formulas returning "". Add a nonblank condition or narrow the applied range.
Practice the skill
Continue with a checked FormulaCamp lesson: Conditional Formatting: Text and Duplicates Free
Open the free lesson