How to find duplicates in Google Sheets
Learn how to find and highlight duplicate entries in Google Sheets using conditional formatting and COUNTIF formulas.
Quick answer
Select your data range, then open Format > Conditional formatting. Choose Custom formula is and enter =COUNTIF($A$2:$A$100,A2)>1, then pick a highlight color and click Done. Google Sheets will color every cell that appears more than once, letting you instantly spot duplicate entries across a column or dataset.
Steps at a glance
- Select the column or range you want to check
- Open Format > Conditional formatting
- Choose Custom formula is as the rule type
- Enter a COUNTIF formula to flag repeats
- Pick a highlight color and click Done
- Review highlighted cells to confirm duplicates
- Optionally add a helper column to count them
Summary
Google Sheets lets you spot duplicate entries using conditional formatting rules built on the COUNTIF formula, without installing any add-ons. This method highlights repeated values directly in your sheet so you can review, filter, or clean them up before they cause reporting errors.
Step-by-step guide
Step 1
Select the range you want to check
Click and drag to highlight the column or range where you suspect duplicate values, such as A2:A100. Exclude the header row so the formula only evaluates actual data entries.
Step 2
Open the conditional formatting menu
Click Format in the top menu, then choose Conditional formatting. A panel will open on the right side of the screen where you can set up formatting rules for the selected range.
Step 3
Set the rule to Custom formula is
In the Format cells if dropdown inside the panel, scroll down and select Custom formula is. This lets you write a formula that decides which cells get highlighted, similar to how you'd use conditional formatting elsewhere in the sheet.
Step 4
Enter the COUNTIF formula
Type =COUNTIF($A$2:$A$100,A2)>1 into the formula box, adjusting the range and starting cell to match your data. This checks how many times each value appears and flags it if the count is greater than one. For more formula variations, see how to /guides/google-sheets/use-countif.
Step 5
Choose a highlight color and save
Click the formatting style box to pick a fill color or text style for duplicate cells, then click Done. Every cell containing a repeated value will now display the chosen highlight automatically.
Step 6
Review and filter the flagged duplicates
Scroll through your sheet to see which entries are highlighted, or combine this with a filter to isolate only duplicate rows. You can learn more about isolating flagged data in the guide to /guides/google-sheets/filter-data.
Step 7
Decide whether to remove or keep duplicates
Once duplicates are visible, you can manually delete rows, or use Google Sheets' built-in duplicate removal tool for a faster cleanup. Check the separate guide on how to remove duplicates once you've confirmed which entries are true repeats.
Why this matters
You've imported a customer list, survey results, or inventory data and suspect the same entry got recorded twice. Finding duplicates before you analyze or share the sheet prevents skewed totals, double-counted contacts, and embarrassing errors in reports built on that data.
Frequently asked questions
Does highlighting duplicates delete any data?
No. Conditional formatting only changes the visual appearance of cells that match the formula condition. Your original data stays untouched until you manually delete or use a dedicated duplicate removal tool.
Can I find duplicates across multiple columns instead of one?
Yes. Create a helper column that combines the values from each row using a formula like =A2&B2, then run the COUNTIF conditional formatting rule against that combined column to catch duplicate rows.
How do I count how many duplicates exist instead of just highlighting them?
Use a formula like =COUNTIF(A2:A100,A2)-1 in a helper column to show how many extra copies of each value exist. Summing that column gives you a total count of duplicate entries.
Does this method work for both text and number values?
Yes. COUNTIF compares cell values regardless of whether they're text, numbers, or dates, as long as the formatting is consistent. Mismatched formats, like a number stored as text, can prevent matches from being detected.
Create interactive demos like this one — free, no coding required.
Start for free →