In this tutorial I will show you how to compare two columns in Excel so that the values that appear in both columns become highlighted and you can do something with those duplicate values.
Find duplicate values in two columns
1. Click on the
Column Header to highlight Column A.
2. Select the
Conditional Formatting button on the
3. Select the
New Rule option from the
Conditional Formatting button drop-down list.
4. Select ‘
Use a formula to determine which cells to format‘ as a rule in the
New Formatting Rule window.
5. Proceed by entering the following formula in the ‘
Format values where this formula is true‘ box:
6. Click on the
Format button and specify the format you want to set.
It is possible to set a different cell color for duplicate values by clicking the
Fill tab and selecting a background color.
7. After having specified the format you want click
You will be redirected back to the
New Formatting Rule window and will be given a preview of the formatting you have selected. If you are happy with the formatting you have specified click
The conditional formatting rule then will be applied to all values of the column and should look like this.
Note how the values of the highlighted cells in Column A are repeated in Column B. I have manually highlighted the cells with identical values in Column B green for demonstrative purposes.
So this is how you can easily see which values are duplicate in different columns in Excel.
Let me know in the comments if I can answer any of your questions!