Highlight Duplicate Emails in Google Sheets Without Deleting Rows
Color every repeated email without deleting any customer orders.
Download practice data · CSVStart the exerciseA published video is not available for this version yet. The complete written exercise and data are below.
Try it yourself
Import the CSV at D1, not A1. Apply conditional formatting to D2:E8 using the custom formula below. Four rows are highlighted. Change D5 to karel@example.com: only the two Noah rows remain highlighted. No orders are deleted.
=COUNTIF($D$2:$D$8,$D2)>1Detailed steps
- 1. Select the rows to format
We are working on the copy in columns D and E. Column D contains emails and column E contains order IDs. Select D two through E eight, leaving out the header.
- 2. Choose a custom formula
Open Format, then Conditional formatting. Choose Custom formula is. This lets the color depend on whether the email on the current row appears more than once.
- 3. Count matches for each email
Use COUNTIF with D two through D eight as the fixed comparison range. Add dollar signs to keep that range fixed. Compare it with dollar D two, and test whether the count is greater than one.
- 4. Choose the highlight color
Choose a fill color and click Done. Both rows for Emma and both rows for Noah are highlighted. The other email addresses appear only once and stay uncolored.
- Repeated email does not mean bad data
The dollar sign before D keeps the email column fixed while the row number changes. Repeated emails can be legitimate repeat customers, so review them instead of automatically deleting orders. Which field would you flag next: emails, order IDs, or product codes?
Preview the input data
| Order | |
|---|---|
| emma@example.com | A101 |
| noah@example.com | A102 |
| mia@example.com | A103 |
| emma@example.com | A104 |
| liam@example.com | A105 |
| noah@example.com | A106 |
| ava@example.com | A107 |
How to open the CSV
In a blank Google Sheet choose File → Import → Upload and select the downloaded CSV. Use comma as the separator. Start at A1 unless the exercise specifies another location. CSV contains input data; add formulas and controls using the steps above. Files are hosted directly here and require no shared personal Drive document.
Fictional demo data. Formulas use English function names and commas between arguments. Other spreadsheet locales may require semicolons. Check date recognition after importing.