Excel: How to Highlight Duplicates in Two Columns

Often you may want to highlight duplicate values across two columns in Excel.

Fortunately this is easy to do using the Highlight Cells Rules feature within the Conditional Formatting options in Excel:

The following example shows how to use this feature in practice.

Example: How to Highlight Duplicates in Two Columns in Excel

Suppose we have the following lists of basketball players on two different teams:

Suppose we would like to highlight duplicate values that occur in both lists.

To do so, we can highlight the cell range A2:B13 and then click the Conditional Formatting icon on the Home tab along the top ribbon, then click Highlight Cells Rules, then click Duplicate Values… from the dropdown menu:

A new window will appear where you can choose how you’d like to format the duplicate values:

We will use the default option of Light Red Fill with Dark Red Text and then click OK.

Once we click OK, all duplicate values that occur in both team lists will be highlighted:

Excel highlight duplicates in two columns

We can see that the following names occur in both lists:

  • Andy
  • Chad
  • Frank
  • Henry
  • John

Each of these names have dark red text and a light red fill to indicate that they occur in both columns.

Note: In this example, each range that we used had the same number of cells but the conditional formatting rule will work with ranges that do not have equal sizes as well.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Highlight Max Value in Each Row
Excel: How to Highlight Cell if Value Exists in List
Excel: How to Highlight Entire Row Based on Cell Value

Leave a Reply

Your email address will not be published. Required fields are marked *