Table of Contents
The ability to quickly identify and highlight duplicate values in two separate columns in Microsoft Excel can be incredibly helpful in a variety of scenarios. From quickly identifying and removing duplicate emails from a mailing list, to finding and deleting duplicate data entries in a large spreadsheet, the ability to easily highlight duplicate values can save time and provide actionable insights. In this article, we’ll discuss the various ways to identify and highlight duplicates in two columns in Microsoft Excel.
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:
We can see that the following names occur in both lists:
- Andy
- Chad
- Frank
- Henry
- John
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.