Excel + Google Sheets · Find duplicates
Spot duplicates instantly
A rule that colors any value that appears more than once.
The formula
=COUNTIF($A$2:$A$7, $A2)>1What each part means
COUNTIF($A$2:$A$7, $A2)How many times it appears>1More than once = duplicate
Example
Home → Conditional formatting → New rule → Use a formula
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | ana@acme.com | Ana |
| 3 | tom@bolt.io | Tom |
| 4 | ana@acme.com | Ana G. |
| 5 | leo@cora.es | Leo |
| 6 | tom@bolt.io | Tom B. |
| 7 | mia@dyno.co | Mia |
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | ana@acme.com | Ana |
| 3 | tom@bolt.io | Tom |
| 4 | ana@acme.com | Ana G. |
| 5 | leo@cora.es | Leo |
| 6 | tom@bolt.io | Tom B. |
| 7 | mia@dyno.co | Mia |
Step by step
- Select the data, from row 2 down.
- Go to Home → Conditional formatting → New rule → Use a formula.
- Paste the formula:
=COUNTIF($A$2:$A$7, $A2)>1 - Click Format, pick a fill colour and press OK.
In Google Sheets: Format → Conditional formatting → Custom formula is.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English Find duplicates
- Español Encuentra duplicados
- Português Encontre duplicados