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.
Get the Excel File
DownloadMore on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- How to Add Data Bars in Excel
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel