How to highlight if duplicate

Answer

To highlight duplicates in Excel, use Conditional Formatting, which automatically identifies repeated values in a selected range.

Steps to Highlight Duplicates

1. Select the range of cells where you want to check for duplicates.

2. Go to the Home tab on the ribbon.

3. Click Conditional Formatting.

4. Hover over Highlight Cells Rules, then select Duplicate Values.

5. In the pop-up window, Excel shows the default formatting (usually light red fill with dark red text).

6. Choose a different formatting style if desired, then click OK.

Additional Tips

• Highlighting works for numbers, text, and dates.

• Excel considers uppercase and lowercase letters as identical when identifying duplicates.

• For multiple columns, select all the columns together to highlight duplicates across them.

Highlighting Unique Values Only

1. Follow the same steps but choose Unique instead of Duplicate Values in the Conditional Formatting pop-up.

Using Formulas for More Control

Conditional Formatting with a formula allows highlighting duplicates based on specific criteria. Example for column A:

1. Select the range, e.g., A2:A100.

2. Go to Conditional Formatting → New Rule → Use a formula to determine which cells to format.

3. Enter the formula: 

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

4. Set the formatting and click OK.

This formula counts how many times each value occurs. Cells with counts greater than 1 are highlighted.

Differences Between Windows and Mac

• Windows: Conditional Formatting options appear under Home → Conditional Formatting.

• Mac: Conditional Formatting is under Home → Conditional Formatting → New Rule, and the menu style may differ slightly.

Example Scenario

If a sales team tracks customer IDs in column A and some IDs appear twice, using Conditional Formatting highlights the duplicates, helping the team spot repeated entries quickly.

This method ensures quick identification of duplicates without manual checking, improving data accuracy.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report