How to Highlight Duplicates in Google Sheets (2026 Guide)
Google Sheets has no one-click “highlight duplicates” button, but a single conditional formatting rule with COUNTIF does the job and keeps working as you add data. Select your range, add a custom formula rule, and pick a color. Here is how to flag duplicates in one column, across whole rows, and how to remove them once you find them.
1. Highlight Duplicates in One Column
- Select the range to check, for example A2:A100 (skip the header row).
- Go to Format > Conditional formatting.
- In the panel on the right, under Format rules, open the dropdown and choose Custom formula is.
- Enter:
=COUNTIF(A:A, A2)>1 - Under Formatting style, pick a fill color (a soft red or yellow reads well).
- Click Done.
Every value that appears more than once in column A is now highlighted. The rule is live, so if you type a value that already exists, it lights up immediately.
How the formula works: COUNTIF(A:A, A2) counts how many times the current cell’s value appears in the whole column. If that count is greater than 1, the value is a duplicate, so the rule applies.
2. Highlight Only the Repeats (Keep the First One Plain)
The rule above highlights every copy, including the original. To highlight only the second and later occurrences, leaving the first instance unmarked:
- Select A2:A100.
- Add a Custom formula is rule with:
=COUNTIF($A$2:A2, A2)>1 - Choose a color and click Done.
The locked start ($A$2) and relative end (A2) make the range grow row by row, so only repeats after the first get flagged. This is useful when you want to keep the first entry and visually mark the extras for deletion.
3. Highlight Entire Duplicate Rows
To highlight the whole row when a key column repeats (for example, a duplicate email address):
- Select the full data range across all columns, for example A2:D100.
- Go to Format > Conditional formatting > Custom formula is.
- Enter a formula that locks the column but not the row:
=COUNTIF($A$2:$A$100, $A2)>1 - Pick a color and click Done.
Locking the column with $A while leaving the row relative ($A2) means the rule evaluates each row against column A and shades the entire row when that email repeats. Change $A to whichever column holds the value you are de-duplicating on.
4. Find Duplicates Across Two Columns
To flag rows where the combination of two columns repeats (same name AND same date), add a helper column:
- In an empty column, say E2, combine the keys:
Fill it down the range.=A2&"|"&B2 - Apply a duplicate-highlight rule to that helper column:
=COUNTIF($E$2:$E$100, E2)>1
Now only rows where both values match are flagged.
5. Remove the Duplicates You Found
Once duplicates are highlighted and you have confirmed them, clean them up:
- Select your data range, including the header.
- Go to Data > Data cleanup > Remove duplicates.
- Tick Data has header row if it does.
- Choose which columns to compare (select only the key column to dedupe on that, or all columns for exact-row matches).
- Click Remove duplicates.
Sheets reports how many duplicate rows it removed and how many unique rows remain. This deletes data, so duplicate the sheet first if you want a backup.
To clear the highlighting afterward, go to Format > Conditional formatting, hover the rule, and click the trash icon.
Quick Reference
| Goal | Custom formula |
|---|---|
| Flag every duplicate in a column | =COUNTIF(A:A, A2)>1 |
| Flag only the repeats | =COUNTIF($A$2:A2, A2)>1 |
| Highlight whole duplicate rows | =COUNTIF($A$2:$A$100, $A2)>1 |
| Two-column match | Helper `=A2&" |
| Delete duplicates | Data > Data cleanup > Remove duplicates |
Let Carly Keep Lists Clean as Data Arrives
Spotting duplicates after the fact is fine, but the cleaner habit is preventing pile-ups in the first place. Carly is an AI assistant that connects to 200+ apps including Google Sheets and handles the repetitive follow-through, logging new records into the right sheet, and pulling a deduplicated, up-to-date list into a recurring report or email so you are not scrubbing the same spreadsheet by hand each week. Carly starts at $35/month.
More on Google Sheets: How to remove duplicates in Google Sheets · How to use conditional formatting in Google Sheets · How to use VLOOKUP in Google Sheets · Google Sheets integration
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
See what people say
"Before Carly, I relied on a Calendly link, but the whole process felt impersonal and not very professional. Carly changed that by handling all the back-and-forth, so I'm no longer stuck in endless email threads trying to line up schedules.
Now Carly reaches out to candidates, shares my real-time availability, lets them pick a slot, then sends a Zoom link and drops it straight into my calendar. She sends reminders to both of us before each call, which has significantly reduced no-shows and last-minute confusion.
On top of scheduling, Carly acts like a full executive assistant, sending me my schedule the night before so I can prepare for each call. It reminds me of the old x.ai assistant, but Carly is noticeably smarter, faster, and better suited to my healthcare recruitment business."
