Ever looked at a long list of names or numbers in Google Sheets and thought, “Wait, did I already enter that?” You’re not alone! Spotting duplicates manually can be time-consuming and error-prone. Fortunately, Google Sheets has a built-in feature to highlight duplicates instantly — and the best part? No complex formulas needed. This guide walks you through everything step by step, using a simple example anyone can follow.
What Does “Highlight Duplicates” Mean?
Highlighting duplicates means automatically changing the background color of any cell that appears more than once in a range. This visual cue helps you catch mistakes, clean up data, or find repeated entries at a glance.
Real-Life Example: Cleaning Up a Guest List
Let’s say you’re planning an event and you’ve collected RSVPs in a spreadsheet. Some people may have submitted their names twice. Instead of scanning the entire list manually, you can use conditional formatting to quickly highlight those duplicate names.
Sample Table: Event Guest List
| Guest Name |
|---|
| Emily Brown |
| John Smith |
| Emma Wilson |
| Emily Brown |
| Michael Lee |
| Emma Wilson |
In the example above, “Emily Brown” and “Emma Wilson” appear twice. Let’s highlight them automatically!
Step-by-Step Instructions: How to Highlight Duplicates
- Select the range of cells where you want to find duplicates (e.g.,
A2:A100). - Click Format in the top menu.
- Choose Conditional formatting.
- Under the “Format cells if” drop-down, select Custom formula is.
- Enter this formula:
=COUNTIF(A:A, A2) > 1 - Choose a fill color to highlight duplicates (e.g., light red).
- Click Done.
And just like that, Google Sheets will highlight any name that appears more than once!
Key Benefits of Using This Feature
- No coding needed: It works with just a single formula.
- Instant results: As soon as you type or paste data, duplicates are highlighted automatically.
- Better accuracy: Easily avoid mistakes in mailing lists, sales records, sign-up sheets, etc.
- Works for text and numbers: Whether you’re checking product codes or phone numbers, it works the same way.
Pro Tips
- Always double-check the column range — if your data starts in
B2, adjust the formula to=COUNTIF(B:B, B2) > 1. - If you want to highlight only the second instance onward (but not the first), try this:
=COUNTIF($A$2:A2, A2) > 1 - You can use the same method to highlight duplicates across multiple columns — just select a wider range like
A2:C100.
Bonus: Highlight Duplicates Across Rows and Columns
If you have data spread out across rows and columns, simply select the entire data range before applying conditional formatting.
Example Table: Product Codes
| Product A | Product B | Product C |
|---|---|---|
| SKU001 | SKU002 | SKU003 |
| SKU004 | SKU002 | SKU005 |
| SKU006 | SKU003 | SKU007 |
To highlight duplicates across the whole table, select A2:C4 and apply the formula: =COUNTIF($A$2:$C$4, A2)>1
Quick-Reference Cheat Sheet
| Use Case | Formula to Use | Notes |
|---|---|---|
| Highlight all duplicates in column A | =COUNTIF(A:A, A2) > 1 |
Highlights all repeated values |
| Highlight duplicates only after the first | =COUNTIF($A$2:A2, A2) > 1 |
Leaves the first occurrence unhighlighted |
| Highlight duplicates across a table | =COUNTIF($A$2:$C$4, A2) > 1 |
Applies to multiple rows and columns |
Highlighting duplicates in Google Sheets is one of the easiest and most effective ways to clean your data — and you don’t need to be a spreadsheet expert to do it. Just follow the steps above, and you’ll catch repeated entries in seconds. Whether you’re managing contact lists, product SKUs, or homework submissions, this feature keeps your data clean and organized with just a few clicks.