How to mark duplicates with color

Answer

To mark duplicates with color in Excel, use Conditional Formatting, which highlights cells based on specific rules.

Steps to Mark Duplicates

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

   • Example: A1:A100 for a single column or A1:D100 for multiple columns.

2. Go to the Home tab on the ribbon.

3. Click Conditional Formatting in the Styles group.

4. Hover over Highlight Cells Rules and select Duplicate Values.

5. In the Duplicate Values dialog box:

   • Ensure Duplicate is selected in the first dropdown.

   • Choose a formatting color from the second dropdown or click Custom Format to select a specific color.

6. Click OK.

   • Excel immediately highlights all duplicate values in the selected range with the chosen color.

Notes

• Excel treats duplicate values case-insensitively by default. "apple" and "Apple" are considered duplicates.

• Duplicates can be highlighted within a single column or across multiple columns.

• For Macbook, the path is slightly different:

  • Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, then follow the same steps.

Example

If column A contains:

A1: Apple

A2: Banana

A3: Apple

A4: Orange

• Applying the duplicate formatting highlights A1 and A3 in the chosen color.

This method ensures quick visual identification of duplicates without changing the original data.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report