Highlight duplicates
Duplicate order IDs, repeated emails, double-booked slots — every spreadsheet gets them. Walk the real Excel / Google Sheets menus above a live spreadsheet engine, then copy the exact menu path or formula.
Highlight duplicates in Excel
Select the range → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and pick a fill. Excel colors every copy — the first occurrence included. To light up only the extras, use a COUNTIF helper column with a filter instead.
Highlight duplicates in Google Sheets
Select the range → Format → Conditional formatting → Custom formula is, and enter =COUNTIF($A$2:$A$9,A2)>1 (adjust the range; the $ keeps it fixed), then pick a color. The formula reads: this value appears more than once in the range.
Three gotchas you must know
Highlighting is not removing
Conditional formatting only changes looks — not one cell of data moves. To actually delete copies, use Data → Remove Duplicates (Excel) or Data → Data cleanup → Remove duplicates (Sheets). It keeps the first occurrence, drops the rest, and cannot be undone — back up first.
Every copy lights up
Excel's Duplicate Values rule marks the original occurrence too — a classic source of "everything is a duplicate!" panic. Reading it is simple: if a value appears N times, N cells glow, and one of them is the original.
COUNTIF works everywhere
The helper-column pattern =COUNTIF($A$2:$A$9,A2)>1 works in Excel, Sheets, sorting, filtering, and advanced rules alike. And when you need fine rules like "first occurrence in each group isn't a duplicate", formulas are the only way.
FAQ
How do I highlight duplicates in Google Sheets?
Select the range, then Format → Conditional formatting. Under Format rules choose Custom formula is, enter =COUNTIF($A$2:$A$9,A2)>1 (adjust the range to yours), and pick a fill color. Every value that appears more than once gets the color.
How do I highlight duplicates in Excel?
Select the range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and pick the red fill. Excel marks every copy — the original occurrence included.
Does highlighting duplicates delete anything?
No. Conditional formatting only changes how cells look — the data stays untouched. To actually delete extra copies, use Data → Remove Duplicates (Excel) or Data → Data cleanup → Remove duplicates (Google Sheets), which keeps the first occurrence and drops the rest.
How do I count duplicates instead of highlighting them?
Add a helper column with =COUNTIF($A$2:$A$9,A2) and fill down — values of 2 or more are duplicates. Filter on that column to review or delete them row by row.