Comparing data by analyzing and identifying similarities and differences is a task that is frequently encountered in various contexts. This task can be performed manually by visually looking at data one by one but it can be both time-consuming and prone to errors. Fortunately, Excel provides a solution for this issue!

There are several techniques that allow us to easily compare two columns and streamline your data in Excel. Here are a few of those approaches

## Watch our free training video on Comparing 2 Columns:

Letâ€™s look at these methods thoroughly!

#### Download the Excel Workbook below to follow along and understand how to compare two columns in Excel – DOWNLOAD EXCEL WORKBOOK

Method 1: Equal Operator

This is a simple yet effective approach that allows users to compare two columns and check if the cell values are equal or not. To use this operator in Excel, simply add the equal sign (=) in between the two values that you want to compare.

For example, if you want to compare the value mentioned in cells A2 and B2, you can enter the following formula –

=A2=B2

If the values in the specified cells are equal then the function returns TRUE otherwise it returns FALSE.

In this example, we are trying to individually compare two columns using the equal operator –

STEP 1: Create a new column.

STEP 2: Input the two cells that you are trying to compare separated by an equal sign operator.

=A2=B2

STEP 3: Copy the formula below to populate the formula for the remaining cells in the column.

You can see that the rows with matching values will return the value as TRUE otherwise it will return the value as FALSE.

Method 2: EXACT Function

The Equal operator is used to compare cell values but it does not consider case sensitivity. The EXACT function is used specifically to compare text keeping in mind theÂ distinction between uppercase and lowercase letters. It will return TRUE only if the values are identical in a case-sensitive manner as well.

Let’s compare two columns in this example using the EXACT function –

STEP 1: Enter the EXACT function.

=EXACT(

STEP 2: Enter the first argument i.e. the first text that you need to compare. Here, it is in cell A2.

=EXACT(A2,

STEP 3: Enter the second argument i.e. the second text that you need to compare. Here, it is in cell B2.

=EXACT(A2,B2)

STEP 4: Copy the formula below to populate the formula for the remaining cells in the column.

You can see when the values in columns A and B are exactly the same in case sensitive manner, the function is returning the value as TRUE or else FALSE.

Method 3: IF Function

You can use the IF function in Excel to compare two columns and specify the desired output if the comparison is TRUE or FALSE. As compared to the equal operator, it helps you in providing more flexibility as opposed to an equal operator where you can only display boolean results – TRUE or FALSE.

Here’s an example –

STEP 1: Enter the IF function.

=IF(

STEP 2: Enter the first argument i.e. the logical test.

=IF(A2=B2,

STEP 3: Enter the second argument i.e. the value if the condition is true.

=IF(A2=B2,”Match”,

STEP 4: Enter the third argument i.e. the value if the condition is false.

=IF(A2=B2,”Match”,”No Match”)

STEP 5: Copy the formula below to populate the formula for the remaining cells in the column.

You can see when the values in columns A and B are exactly the same, the function returns the value as TRUE or else FALSE.

Method 4: Conditional Formatting

To use conditional formatting to compare two columns in Excel, follow the steps below –

STEP 1:Â Select the cell range where you want the formatting to be shown.

STEP 2: Go toÂ HomeÂ >Â Condition FormattingÂ >Â New Rule.

STEP 3: In the New Formatting Rule dialog box, select Use a Formula to determine which cells to format.

STEP 4: Type the formula stated below â€“

=\$A2<>\$B2

STEP 5: Click on theÂ FormatÂ button.

STEP 6: Under the Fill tab, select the light red color. Click OK.

STEP 7: Double-check the result under Preview and then click OK.

And Voila, you will see that the desired result has been achieved. The cell values in column A that do not match Column B will be highlighted in light red fill.

Method 5: MATCH Function

The MATCH function is typically used to return the position of an item in a range. But, you can also use it to compare two columns and check if a specific item in List1Â exists inÂ List2. The function willÂ return the row position of that item inÂ List2Â hence confirming that it exists.Â  If you get aÂ #N/A it means that the cellÂ´s item does not existÂ inÂ List2.

Let’s understand it better with an example –

STEP 1: Enter the MATCH function.

=MATCH(

STEP 2: Enter the first argument – lookup_value. Here, it is mentioned in cell A2.

=MATCH(A2,

STEP 3: Enter the second argument – lookup_array. Here, it is the range B2:B30.

=MATCH(A2,\$B\$2:\$B\$27,

STEP 4: Enter the third argument – match_type. Here, it is 0 for an exact match.

=MATCH(A2,\$B\$2:\$B\$27,0)

STEP 5: Copy the formula below to populate the formula for the remaining cells in the column.

You can see when the values in columns A and B are the same, the function returns the value as a row numberÂ or else #N/A.

Method 6: Highlight Row Difference

You can easilyÂ highlight differences in value in each row using an in-built feature in Excel. It will provide you with an idea of how many lines in the columns differ in values.

In the data below, you have two lists in Column A and Column B respectively.

Follow theÂ steps below to compare two columsn and highlight row difference :

STEP 1: Select both columns.

STEP 2:Â Go toÂ HomeÂ >Â Find & SelectÂ >Â Go To SpecialÂ or simply press keysÂ Ctrl + GÂ and SelectÂ SpecialÂ to open the Go To Special dialog box.

STEP 3:Â SelectÂ Row DifferenceÂ and ClickÂ OK.

And, Voila!

All the values in List 2 that do not match the corresponding value in List 1 have been highlighted.

STEP 4:Â You can mark these cells with color as well. Go toÂ HomeÂ >Â Font ColorÂ > SelectÂ Red.

This will permanentlyÂ highlight the cells in red font colorÂ for future reference.

Click here to access these FREE Excel courses!

If you like this Excel tip, please share it

email

Pinterest