Compare Two Columns and Highlight Differences in Excel & Google Sheets

In this article, you will learn how to compare two columns and highlight differences in Excel and Google Sheets.

 

compare two columns final data

 

Compare Two Columns and Highlight the Differences

In Excel, you can compare two columns and highlight their differences using conditional formatting. Say you have the following data with two lists of names in Columns B and C.

 

compare two columns initial data

 

To highlight all differences (Rows 3, 6, 7, and 9) in red, follow these steps:

1. Select data in the columns you want to compare and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

 

compare two columns conditional formatting

 

2. In the pop-up window, (1) select Unique, and (2) click OK. You can leave the default format (Light Red Fill with Dark Red Text).
You could also have selected Duplicate; in that case, the same values would be highlighted.

 

compare two columns conditional formatting 2

 

As a result, cells containing different values in Columns B and C are highlighted in red.

 

compare two columns final data

 

Compare Two Columns and Highlight the Differences in Google Sheets

You can also compare two columns using conditional formatting in Google Sheets.

1. Select a data range you want to compare (B2:C9), and in the Menu, go to Format > Conditional formatting.

 

google sheets compare two columns conditional formatting

 

2. In the window on the right side, (1) select Custom formula is under Format rules and (2) enter the formula:

Then (3) click on the fill color icon, (4) choose red, and (5) click Done.

 

google sheets compare two columns conditional formatting 2

 

The formula has a dollar sign to fix columns, only changing rows. This means that the formatting rule will go row-by-row and compare cells in Columns B and C.

As a result, cells with different values have a red fill color.

 

google sheets final data