Compare Two Columns using Conditional Formatting in Excel

puneet-gogia-excel-champs

- Written by Puneet

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.

  1. First, select both columns in the data.
    1-select-both-columns
  2. Afterward, go to the Home Tab > Conditional Formatting > New Rule.
    2-conditional-formatting-new-rule
  3. From there, click on “Use a formula to determine which cells to format”.
    3-select-use-a-formula-to-determine
  4. Next, in the formula bar, enter the formula =$A2<>$B2, then click “Format…” to specify the format.
    4-enter-formula-and-specify-format
  5. Once you specify the format, click OK to apply the conditional formatting and again OK to close the Rules Manager.
    5-close-the-rules-manager

And the moment you click OK, it highlights cells from both columns where values are the same.

highlighted-cells-with-same-values

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.

select-duplicate-values

In the duplicate values dialog box, select which values you want to highlight and the color you want to apply.

select-duplicate-to-highlight-duplicates

In the end, click OK to apply the conditional formatting.

duplicate-values-highlighted

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.

select-unique-to-highlight-unique-values

And when click OK, it will highlight where values are unique using comparison in both columns.

highlighted-unique-values

Get the Excel File

Download
Last Updated: May 25, 2024