How to Find Duplicates in Google Sheets (4 Ways)
Find, highlight, and remove duplicates in Google Sheets with Conditional Formatting, COUNTIF, UNIQUE, and the built-in Remove duplicates tool — plus a free highlighter for exported CSVs.
Founder of ARCED Foundation, ARCED International, and Solutions of Things Lab (SoTLab). Built FolderManifest to help teams protect file integrity and stay audit-ready.
Quick Answer
To highlight duplicates, use Format → Conditional formatting → Custom formula is with =COUNTIF(A:A, A1) > 1. To delete them, use Data → Data cleanup → Remove duplicates. To check a sheet without formulas, download it as CSV and drop it into the free Spreadsheet Duplicate Finder.
Method 1: Highlight with Conditional Formatting
Google Sheets has no one-click "duplicate values" preset, so you use a custom formula:
- Select the range, e.g.
A2:A. - Open Format → Conditional formatting.
- Under Format cells if, choose Custom formula is.
- Enter
=COUNTIF(A:A, A1) > 1and pick a fill color. - Click Done.
Method 2: COUNTIF (Count the Duplicates)
To label and count duplicates in a helper column:
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")
=COUNTIF(A:A, A2) ' occurrences of this valueMethod 3: UNIQUE (List Unique Values)
=UNIQUE(A2:A) returns the list with duplicates removed, leaving your original column untouched. Pair it with =COUNTUNIQUE(A2:A) to see how many distinct values you have — a quick way to gauge how many duplicates exist without changing anything.
Method 4: Remove Duplicates
To delete duplicate rows directly:
- Select the range.
- Open Data → Data cleanup → Remove duplicates.
- Confirm whether the data has a header row and which columns to compare.
- Click Remove duplicates.
Make a copy first
Remove duplicates edits in place. Use File → Make a copy before running it on important data, and review the highlights first so you know what is being removed.
Method 5: Highlight Exported Files (No Formulas)
If you would rather not build a formula — or you need to check a sheet someone sent you — export it and use the free highlighter:
- In Google Sheets, choose File → Download → Comma-separated values (.csv).
- Open the free Spreadsheet Duplicate Finder.
- Drop the CSV in — duplicate values are highlighted per column instantly, in your browser.
Which Method Should You Use?
| Need | Best method |
|---|---|
| See duplicates highlighted | Conditional formatting (COUNTIF) |
| Count or list unique values | COUNTIF / UNIQUE |
| Delete duplicate rows | Data cleanup → Remove duplicates |
| No formulas / a file someone sent | Free online tool |
Frequently Asked Questions
How do I find duplicates in Google Sheets?
Use Conditional formatting with =COUNTIF(A:A, A1) > 1 to highlight repeats, or =COUNTIF(A:A, A1) to count them.
How do I remove duplicates in Google Sheets?
Select the range, then Data → Data cleanup → Remove duplicates.
How do I find duplicates across two sheets?
Use =COUNTIF(Sheet2!A:A, A1) > 0 in a helper column; TRUE means the value exists in both.
Can I check a Google Sheet for duplicates without formulas?
Yes — download it as CSV and drop it into the Spreadsheet Duplicate Finder.
Find Your Sheet Duplicates Now
Export to CSV and drop it into the free, private highlighter — instant per-column results.