In this tutorial, you will learn how to compare two rows in Excel and Google Sheets.
Compare Two Rows
Excel allows you to compare rows and highlight the similarities and differences using conditional formatting. In the following example, there are names in Rows 2 and 3.
Say you want to compare the two rows above and highlight cells where the rows don’t match (Columns D, G, and H) in red.
- Select the data range you want to compare, and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the pop-up window, (1) select Unique and (2) click OK, leaving the default format (Light Red Fill with Dark Red Text).
You could also have selected Duplicate and in that case, the same values would be highlighted.
As a result, cells containing different values in Rows 2 and 3 are highlighted in red.
Compare Two Rows in Google Sheets
You can also compare two rows using conditional formatting in Google Sheets.
- Select the data range you want to compare (here, C2:I3), and in the Menu, go to Format > Conditional formatting.
- 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.
The formula has a dollar sign to fix rows, and only change columns. This means that the formatting rule will go column-by-column and compare cells in Rows 2 and 3.
As a result, cells with different values have a red fill color.