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.