Quick Answer & Formula
Excel
Sheets
Beginner
=COUNTIF($A$2:$A$100, A2) > 1 To highlight duplicates across a range, select the data, open Conditional Formatting > New Rule > 'Use a formula to determine which cells to format', and enter =COUNTIF($A$2:$A$100, A2) > 1.
How to Highlight Duplicates in Excel & Google Sheets (Conditional Formatting)
Visualizing duplicate records before deleting them ensures you don’t accidentally remove legitimate transactions.
1. Single Column Duplicate Rule
- Select range
A2:A100. - Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Formula:
=COUNTIF($A$2:$A$100, A2) > 1 - Set Fill color to Light Red / Soft Yellow.
2. Highlighting Entire Duplicate Rows (Multi-Column)
To highlight the entire row when both Email (Col A) and Order ID (Col B) are duplicated:
=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1(Notice the $ locking only the column $A2 and $B2)
?
Frequently Asked Questions
How do I highlight duplicate full rows (e.g. same First Name AND Last Name)?
Use COUNTIFS in your conditional formatting rule: =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1.