How to Highlight Duplicates in Google Sheets
Highlighting duplicates helps you clean data, identify errors, and analyze patterns. Google Sheets offers several ways to find duplicate values.
1
Select Your Data Range
Highlight the column or range where you want to find duplicates.
2
Open Conditional Formatting
Go to Format > Conditional formatting. This opens the conditional format rules panel on the right.
3
Set the Duplicate Rule
Under "Format cells if", choose "Custom formula is". Enter =COUNTIF(A:A, A1)>1 if your data is in column A.
💡 Adjust the range references to match your selected data.
4
Choose a Highlight Color
Select a formatting style with a bright fill color (like light red) so duplicates are easy to spot. Click Done.
Try These Templates
Frequently Asked Questions
Can I highlight duplicates across multiple columns?
Yes. Use a COUNTIFS formula or concatenate columns in your condition.
How do I remove duplicates in Google Sheets?
Select your data range, go to Data > Data cleanup > Remove duplicates.