Top 3 Ways to Highlight Duplicate Values in Excel (With and Without Formulas)

Top 3 Ways to Highlight Duplicate Values in Excel (With and Without Formulas)

Cleaning data in Excel? Finding and highlighting duplicates is essential. Here are the top 3 methods to do it easily:

🔹 1. Using Conditional Formatting (Built-in)

• Select your data range (e.g., A2:A100)
• Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
• Choose a color and click OK

Best for: Quick duplicate checks without formulas

🔹 2. Using COUNTIF Formula

Apply this formula to a helper column:

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

Best for: When you want to tag duplicates as text

🔹 3. Advanced Conditional Formatting Formula

• Select your data
• Use this formula in Conditional Formatting:

=COUNTIF($A$2:$A$100, A2) > 1

Then choose formatting style and click OK

Best for: Flexible and dynamic highlighting

✅ Final Tips:

• Sort your data to group duplicates visually
• Remove duplicates with Data → Remove Duplicates (if needed)

🎯 Duplicate management in Excel saves time and improves accuracy.

💡 For more Excel hacks, tips, and guides — follow ScriptDataInsights!