In Excel, conditional formatting is the easiest way to compare two columns. With this, you can compare two columns in multiple ways. In this tutorial, we will learn multiple methods to use conditional formatting for column comparison.
Compare Two Column Row Wise
With this method, you can compare both cells from each row of the columns and highlight the cells with the same value.
- First, select both columns in the data.
- Afterward, go to the Home Tab > Conditional Formatting > New Rule.
- From there, click on “Use a formula to determine which cells to format”.
- Next, in the formula bar, enter the formula =$A2<>$B2, then click “Format…” to specify the format.
- Once you specify the format, click OK to apply the conditional formatting and again OK to close the Rules Manager.
And the moment you click OK, it highlights cells from both columns where values are the same.
Compare All the Values from All the Columns
Unlike the first method, you can highlight values that are in duplicates in both columns. And for this, select both columns and go to the Home Tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the duplicate values dialog box, select which values you want to highlight and the color you want to apply.
In the end, click OK to apply the conditional formatting.
The moment you click OK, all the cells where values are duplicate in both columns will be highlighted with the format you have selected. But if you want to highlight the cells which are unique in both columns, you need to select the “Unique” from the drop down.
And when click OK, it will highlight where values are unique using comparison in both columns.
