Recipes / Recipe 16

Highlight duplicates in Google Sheets or Excel with conditional formatting

Custom formulas that color duplicate values, whole duplicate rows, or only the second and later copies, so you can review before deleting anything.

FormulasCleanup Setup time: 3 minutes

You'll need: Google Sheets or Excel. Examples assume the values to check are in column B with a header in row 1.

Google Sheets steps

Select the range to color, e.g. B2:B (or A2:Z to color whole rows).
Format → Conditional formatting. Under Format cells if, choose Custom formula is.
Paste one of the formulas below, pick a fill color, and click Done.

Every copy of a duplicate value (apply to B2:B)Formula

=COUNTIF($B$2:$B, $B2)>1

Whole row, based on column B (apply to A2:Z)Formula

=COUNTIF($B$2:$B, $B2)>1

Only the 2nd, 3rd... copies, so the first stays unhighlightedFormula

=COUNTIF($B$2:$B2, $B2)>1

Duplicates across two columns together (first name in A + last name in B)Formula

=COUNTIFS($A$2:$A, $A2, $B$2:$B, $B2)>1

Ignore blank cellsFormula

=AND($B2<>"", COUNTIF($B$2:$B, $B2)>1)

Excel steps

For simple cases use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. For whole rows or “2nd copy only”, choose New Rule → Use a formula to determine which cells to format, select e.g. A2:Z1000 first, and use the same formulas with a fixed end row:

Excel: whole row, based on column BFormula

=COUNTIF($B$2:$B$1000, $B2)>1

Good to know

The dollar signs matter: $B2 locks the column but lets the row move, so each row checks its own value.
COUNTIF ignores upper/lower case, so ANN@X.COM and ann@x.com are flagged as duplicates. Trailing spaces are not ignored; clean them first.
Ready to delete them? See remove duplicate rows or keep the latest entry.

Do it yourself

Recipe Club: $12/month

Prefer to set things up yourself? Join the Recipe Club and get access to a private GitHub repository of ready-made, tested automations for Sheets, Apps Script, Python and Node. Each one comes with setup notes and sample data.

A new recipe added every week
Everything in the free library, plus the longer, more complete versions
Use them in your own business as much as you like
Cancel anytime by email (or the Stripe customer portal once available)

Recipe Club opens soon: join the waitlist

You'll need a free GitHub account. After checkout, reply with your GitHub username and we'll send the repo invite, usually within one business day.

Or buy a one-time recipe pack ($19–$29)

Clean Data Pack $19
Dedupe, cleaning, merging and normalizing scripts and formulas for messy exports, with a sample sheet.
Coming soon
Forms & Inbox Pack $19
Form auto-replies, request routing, a lead log and follow-up nudges, ready to paste into Apps Script.
Coming soon
Money Ops Pack $29
Invoice reminders, a monthly revenue dashboard with QUERY, a receipts log and nightly backups.
Coming soon

Written with AI assistance. Script logic is unit-tested; formulas follow documented Google Sheets behavior. Always try new automations on a copy of your sheet first.