Table of Contents
In order to highlight duplicates in two columns using Excel, follow these steps:
1. Select the two columns that you want to compare.
2. Go to the “Home” tab and click on the “Conditional Formatting” option.
3. From the drop-down menu, select “Highlight Cells Rules” and then “Duplicate Values”.
4. A new window will appear with different formatting options. Choose the formatting style that you want for the duplicates.
5. Make sure to select “Both” from the “Format all” drop-down menu.
6. Click on “OK” and all the duplicate values in the two columns will be highlighted.
This feature in Excel allows for easy identification of duplicate values in two columns, making it a useful tool for data analysis and organization.
Excel: 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:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: