How to Highlight Duplicate Values in Excel (Using Conditional Formatting)

How to Highlight Duplicate Values in Excel (Using Conditional Formatting)

Identifying duplicate values in Excel is simple and effective with Conditional Formatting. It visually flags repeating entries in your dataset, saving time during data cleaning or validation.

📘 Steps:

1. Select the range where you want to find duplicates (e.g., A2:A100).
2. Go to the Home tab → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Choose the format color and click OK.

🎯 Use Cases:

• Checking for repeated names or IDs
• Finding duplicate transactions or entries
• Cleaning messy datasets before analysis

💡 Bonus Tip:

You can use formulas in Conditional Formatting for more control. For example, use this formula in a new rule to highlight duplicates in column A:

=COUNTIF(A:A, A2)>1

📊 Visual Insight:

Once applied, duplicate cells will be auto-highlighted with your chosen color, helping you take quick action like deleting or merging records.

For smarter Excel data cleaning hacks like this, follow ScriptDataInsights.