How to Highlight Duplicates in Excel
Highlight duplicates in Excel with conditional formatting, formulas, and cleaner review rules. Learn how to spot repeated IDs, emails, invoices, and names.
Highlighting duplicates in Excel makes repeated values visible before they create bad reports, duplicate invoices, double-counted leads, or messy lookup results. The built-in conditional formatting rule is the fastest option for a single column, while formulas are better when the definition of duplicate is more specific.
Start by deciding what counts as a duplicate: a repeated email, a repeated invoice number, or an entire repeated row.
Use the built-in duplicate rule
Step 1. Select the range to check, such as A2:A500.
Step 2. Go to Home -> Conditional Formatting.
Step 3. Choose Highlight Cells Rules.
Step 4. Choose Duplicate Values.
Step 5. Pick a highlight style and apply the rule.
This works well for customer emails, invoice numbers, vendor IDs, employee names, and other single-column checks.
Use COUNTIF for a custom duplicate flag
When you need a helper column, use COUNTIF.
=COUNTIF($A$2:$A$500,A2)>1If the formula returns TRUE, the value in A2 appears more than once in the checked range.
For a cleaner review column, wrap it in IF:
=IF(COUNTIF($A$2:$A$500,A2)>1,"Duplicate","")This is useful when you want to filter duplicate rows instead of only coloring them.
Practical example: check invoice numbers
In an invoice tracker or expense tracker, duplicate invoice numbers can lead to double payment, reconciliation confusion, or mismatched records.
Add a helper column next to the invoice number field, use COUNTIF, then filter the rows marked Duplicate. Review the actual vendor, date, and amount before deleting anything. Some duplicates are real errors; others are repeated identifiers from a vendor system that need a more specific key.
TIP
Common duplicate checks
| Sheet type | Duplicate field to check |
|---|---|
| CRM lead tracker | Email or company domain |
| Sales pipeline | Deal ID or account plus opportunity name |
| Expense tracker | Receipt number or vendor plus amount |
| Employee schedule | Employee plus shift date |
| Project tracker | Task ID or milestone name |
The Griddy way
Duplicate rules depend on the workflow. A duplicate email in a lead tracker means something different from a duplicate shift in a schedule.
"Find duplicate leads by email, flag likely duplicate companies, and keep the best row for each account"
Griddy can create the duplicate checks, mark the suspect rows, and help clean the sheet without deleting useful context.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.