3 Ways to Highlight Duplicates in Excel (No VBA Needed!)

Duplicate entries can ruin your analysis and cause confusion. Luckily, Excel offers quick ways to find and highlight them—no VBA or advanced coding needed!

🔍 1. Highlight Duplicates with Conditional Formatting

1. Select your data range (e.g., A1:A100).
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Choose a color and click OK. Done!

Tip: This also works for entire rows or multiple columns!

📊 2. Use COUNTIF to Flag Duplicates

Create a new column and use this formula:

=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")

This helps when you want to filter or sort based on duplicates.

🔁 3. Remove Duplicates Completely

1. Select your data.
2. Go to Data → Remove Duplicates.
3. Select columns to check and confirm.

🚀 Bonus Tip: Use Excel Tables

Converting your data to a table (Ctrl + T) makes formatting and filtering even easier when working with duplicates.

💾 Want a ready-to-use template to find duplicates with visual cues? Grab it from our Gumroad store.