How to Find Duplicates in Google Sheets
Finding duplicates is essential for data cleaning. Google Sheets offers several methods to identify and handle duplicate entries.
Use Conditional Formatting
Select your range, go to Format > Conditional formatting, choose "Custom formula is", and enter =COUNTIF(A:A, A1)>1. Set a highlight color and Done.
Use the UNIQUE Formula
Use =UNIQUE(A1:A100) to create a list of unique values from the range. Compare this to your original data to identify duplicates.
💡 UNIQUE does not modify your original data.
Use COUNTIF to Flag Duplicates
Use =COUNTIF(A:A, A1) in a helper column. Results greater than 1 indicate duplicates. Filter to see only duplicates.
Use Data Cleanup Tool
Go to Data > Data cleanup > Remove duplicates for a one-click solution. Review the summary to see how many duplicates were removed.
Try These Templates
Frequently Asked Questions
What is the fastest way to find duplicates?
Use conditional formatting with COUNTIF for visual identification, or Data cleanup for automatic removal.
Can I find duplicates across multiple columns?
Yes. Use =COUNTIFS(A:A, A1, B:B, B1)>1 to find duplicates based on multiple column values.