How to Compare Two Columns in Excel: 4 Methods

7.5 min read|Last Updated: December 16th, 2023|Categories: excel|
table of content

Working in Excel is enjoyable and easy, especially if you know the tricks and techniques to work with different options. While having a table full of data, sometimes, you may need to make some comparisons. Whether it’s a comparison of a whole table or row by row, here are four methods of comparing two columns in Excel that can help you

1- Comparing two columns in Excel row by row 

TRUE/FALSE method 

In this method, you can find out if the data in one row in a column is equal to the same row in the next column. For example, you may need to know if C3 is the same as B3. Check out the table below:

Comparing two columns in Excel row by row
Figure 1- How to compare two columns in Excel

Let’s compare the data in columns B and C, row by row. First, select a column to see the results. We have chosen column D as the result column. This comparison is so easy. You only need to write a simple formula in the formula bar: (=B3=C3). This will check the data in both cells and give two results: TRUE and FALSE. Remember, you can drag the formula cell to the end of the table, so the formula is applied on all rows.

Comparing two columns in Excel with TRUE/FALSE result
Figure 2- Comparing two columns in Excel with TRUE/FALSE result

Using IF formula method to match two columns in excel

Sometimes, you may need to have an exact string after comparing two columns in Excel, or you may need to have a result with a particular text. For example, you may need to see ‘Match’ if the cells have the same data or ‘NO’ to say the data are not equal. Or even a word in a language other than English. Therefore, the first method is not for you because it has a predefined text result. In this case, you can write a simple IF formula.

=IF(B3=C3,”Vrai”,” Faux”)

Note: You can write whatever you want as a result instead of “Vrai” and “Faux.”

See the result below:

Comparing two columns in Excel with the IF method.
Figure 3- Comparing two columns in Excel with the IF method.

Please note that this formula doesn’t make a case-sensitive comparison. Thus, if any character in upper-case is important for your comparison, you can use this formula:

=IF(EXACT(B4,C4),”Vrai”,”Faux”)

Case-Sensitive comparison
Figure 4- Case-Sensitive comparison.

As you see, in this formula, “Blue Jeans” and “Blue jeans” are not the same. 

Highlighting Matched Data in One Row

Comparing two columns in Excel can also be done by highlighting the matched data. We can highlight them in the following steps: 

  1. Select the table or the columns you want to compare. 
  2. Click on the “Home” tab and click on the “Conditional Formatting” option, in the Styles group. Then, select “New Rule.”
    Click on the “Home” tab and click on the “Conditional Formatting” option, in the Styles group. Then, select “New Rule.”
    Figure 5- Highlighting matched values.
  3. You will see a dialogue box with different options. Select “Use a formula to determine which cells to format.”
  4. In the formula field, write the following formula: =$B3=$C3. Note that the formula may change based on the columns you have. In our example, we wanted to check these two columns (B and C) and our data starts from row 3. 
  5. In the next step, you need to choose a format for highlighting. Therefore, click on the “Format” button. The Format Cells dialog box will open. Now you can change font color, border, or just choose a Fill color.