Excel · ChatGPT
How to Highlight Duplicates in Excel
Highlight every duplicated value, only the second and later occurrences, or entire rows that share a complete multi-column key. These tested Conditional Formatting rules preserve every source value and row.
Choose your duplicate rule — copy the prompt:
I have worksheet Invoices with invoice IDs in A2:A10: A2 INV-101 A3 INV-102 A4 INV-101 A5 [blank] A6 INV-103 A7 INV-104 A8 INV-104 A9 INV-105 A10 INV-101 Show me how to select A2:A10 and use Excel's built-in Duplicate Values Conditional Formatting rule. It must highlight every occurrence, including the first, of each duplicated invoice ID; evaluate only this column; leave unique IDs and the single blank unhighlighted; and change formatting only. Expected highlighted cells: A2, A4, A10 (INV-101) and A7, A8 (INV-104). Expected unhighlighted: A3, A5, A6, A9. Applies to must be =$A$2:$A$10. Explain that the built-in rule highlights all occurrences. This test has only one blank; multiple blanks would generally be treated as duplicates by the built-in rule. Include current Windows/Mac menu differences. Do not use Remove Duplicates, Power Query, VBA, Office Scripts, or a formula rule.
Use the built-in rule when every occurrence—including the first—should be highlighted.
I have worksheet Contacts with emails in A2:A11: A2 alex@example.com A3 beth@example.com A4 [blank] A5 alex@example.com A6 chris@example.com A7 [blank] A8 beth@example.com A9 alex@example.com A10 dana@example.com A11 [blank] Create a formula-based Conditional Formatting rule that highlights only the second and later occurrences in A2:A11 and ignores every blank. Expected highlighted cells: A5, A8, A9. Expected unhighlighted: A2, A3, A4, A6, A7, A10, A11. The first alex in A2 and first beth in A3 must remain unhighlighted. Use exactly =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) with Applies to =$A$2:$A$11. Explain that $A$2 fixes the range start, the ending A2 expands by row, and the final A2 tests the current row. Include current Windows/Mac rule-menu differences and localized separator caveats. Preserve all values and row order. Do not use the built-in Duplicate Values rule, Remove Duplicates, Power Query, VBA, or Office Scripts.
Use an expanding COUNTIF when the first occurrence must remain unhighlighted.
I have worksheet Orders with A2:D11. A duplicate means the complete Customer ID + Order Date combination repeats. Row 2 C-100 | 2026-08-01 | Keyboard | 80 Row 3 C-100 | 2026-08-02 | Mouse | 25 Row 4 C-200 | 2026-08-01 | Monitor | 240 Row 5 C-100 | 2026-08-01 | Dock | 120 Row 6 C-300 | 2026-08-03 | Webcam | 70 Row 7 C-200 | 2026-08-01 | Cable | 15 Row 8 C-100 | [blank] | Headset | 95 Row 9 C-100 | [blank] | Stand | 45 Row 10 [blank] | 2026-08-04 | Adapter | 30 Row 11 [blank] | 2026-08-04 | Charger | 55 Create one formula-based rule that highlights the entire A:D row for every occurrence of a duplicated complete key. Complete means both A and B are nonblank. Expected highlighted ranges: A2:D2, A4:D4, A5:D5, A7:D7. Expected unhighlighted: A3:D3, A6:D6, A8:D11. Incomplete repeated pairs must remain unhighlighted. Use exactly =AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1) with Applies to =$A$2:$D$11. Explain fixed lookup ranges, column-locked/current-row references, why both first and later occurrences highlight, and that genuine Excel dates compare by underlying value. Include Windows/Mac and localized separator notes. Do not add helpers, concatenate keys, delete/sort data, or use Power Query, VBA, or Office Scripts.
Use COUNTIFS across complete key fields when the whole duplicate row should highlight.
Before — duplicates are not visually marked
| Cell | Invoice ID |
|---|---|
| A2 | INV-101 |
| A3 | INV-102 |
| A4 | INV-101 |
| A5 | [blank] |
| A6 | INV-103 |
| A7 | INV-104 |
| A8 | INV-104 |
| A9 | INV-105 |
| A10 | INV-101 |
→
After — every duplicated value highlighted
| Cell | Invoice ID |
|---|---|
| A2 | INV-101 |
| A3 | INV-102 |
| A4 | INV-101 |
| A5 | [blank] |
| A6 | INV-103 |
| A7 | INV-104 |
| A8 | INV-104 |
| A9 | INV-105 |
| A10 | INV-101 |
Built-in rule: Duplicate Values · Applies to:
=$A$2:$A$10Before — repeats and blanks look alike
| Cell | |
|---|---|
| A2 | alex@example.com |
| A3 | beth@example.com |
| A4 | [blank] |
| A5 | alex@example.com |
| A6 | chris@example.com |
| A7 | [blank] |
| A8 | beth@example.com |
| A9 | alex@example.com |
| A10 | dana@example.com |
| A11 | [blank] |
→
After — only second and later occurrences
| Cell | |
|---|---|
| A2 | alex@example.com |
| A3 | beth@example.com |
| A4 | [blank] |
| A5 | alex@example.com |
| A6 | chris@example.com |
| A7 | [blank] |
| A8 | beth@example.com |
| A9 | alex@example.com |
| A10 | dana@example.com |
| A11 | [blank] |
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1) · Applies to: =$A$2:$A$11Before — complete and incomplete repeated keys
| Row | Customer | Date | Product | Amount |
|---|---|---|---|---|
| 2 | C-100 | 2026-08-01 | Keyboard | 80 |
| 3 | C-100 | 2026-08-02 | Mouse | 25 |
| 4 | C-200 | 2026-08-01 | Monitor | 240 |
| 5 | C-100 | 2026-08-01 | Dock | 120 |
| 6 | C-300 | 2026-08-03 | Webcam | 70 |
| 7 | C-200 | 2026-08-01 | Cable | 15 |
| 8 | C-100 | [blank] | Headset | 95 |
| 9 | C-100 | [blank] | Stand | 45 |
| 10 | [blank] | 2026-08-04 | Adapter | 30 |
| 11 | [blank] | 2026-08-04 | Charger | 55 |
→
After — entire duplicate-key rows highlighted
| Row | Customer | Date | Product | Amount |
|---|---|---|---|---|
| 2 | C-100 | 2026-08-01 | Keyboard | 80 |
| 3 | C-100 | 2026-08-02 | Mouse | 25 |
| 4 | C-200 | 2026-08-01 | Monitor | 240 |
| 5 | C-100 | 2026-08-01 | Dock | 120 |
| 6 | C-300 | 2026-08-03 | Webcam | 70 |
| 7 | C-200 | 2026-08-01 | Cable | 15 |
| 8 | C-100 | [blank] | Headset | 95 |
| 9 | C-100 | [blank] | Stand | 45 |
| 10 | [blank] | 2026-08-04 | Adapter | 30 |
| 11 | [blank] | 2026-08-04 | Charger | 55 |
=AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$11,$A2,$B$2:$B$11,$B2)>1) · Applies to: =$A$2:$D$11Choose the duplicate-highlighting behavior
All occurrences
Every repeated value, including its first appearance
Built-in rule
After the first
Only occurrence 2, 3, and later; blanks excluded
COUNTIF
Composite rows
Complete Customer ID + Order Date pairs across A:D
COUNTIFS
How to highlight duplicates safely
A practical five-step workflow
1
Define the duplicate key
Decide whether one cell or a combination of columns defines a duplicate.
2
Choose first-occurrence behavior
Use Duplicate Values for every occurrence or an expanding COUNTIF for only the second and later occurrences.
3
Select the exact Applies to range
Select only the value column for cell highlighting, or the full table width when entire rows should highlight.
4
Anchor references correctly
Fix lookup ranges; lock key columns while leaving the current row relative.
5
Verify known duplicates and blanks
Confirm first occurrences, later repeats, unique values, and incomplete keys match the intended rule.
Highlighting is not deletion. Conditional Formatting changes appearance only. Review highlighted records before using a separate removal workflow.
Frequently asked questions
How do I highlight duplicates in Excel?
Select the target range, then use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. The built-in rule highlights every occurrence of each duplicated value, including the first.
How do I highlight only duplicates after the first occurrence?
Use a formula-based Conditional Formatting rule with an expanding COUNTIF range, such as
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1). This leaves the first occurrence and blanks unhighlighted.How do I highlight duplicate rows based on two columns?
Apply a COUNTIFS-based formula across the full row range. Lock the key columns, keep the row relative, and exclude rows where either key field is blank.
Does Excel highlight the first duplicate value?
The built-in Duplicate Values rule highlights every occurrence, including the first. An expanding COUNTIF formula can instead highlight only the second and later occurrences.
Does highlighting duplicates delete or change data?
No. Conditional Formatting changes appearance only. It does not delete, sort, move, or replace source values.