Pinterest Pixel

6 Different Ways to Compare Two Columns in Excel – A Detailed Tutorial

Comparing data by analyzing and identifying similarities and differences is a task that is frequently encountered in... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial | MyExcelOnline 6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial | MyExcelOnline

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

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 workbookCompare-Two-Columns-in-Excel.xlsx

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

=A2=B2

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

See also  Create Custom Symbols in Excel based on Numbers

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

 

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(

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

=EXACT(A2,

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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)

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

 

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.

See also  INDEX and MATCH Made Simple

Here’s an example –

STEP 1: Enter the IF function.

=IF(

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

=IF(A2=B2,

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

=IF(A2=B2,”Match”,

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

 

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

STEP 4: Type the formula stated below –

=$A2<>$B2

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

STEP 5: Click on the Format button.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

See also  Compare Two Columns in Excel for Matches & Differences: 3 Useful Methods

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

 

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(

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

=MATCH(A2,

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

 

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.

See also  Excel Tricks: How to Find Unique Values in Excel

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

STEP 1: Select both columns.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

STEP 3: Select Row Difference and Click OK.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

And, Voila!

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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.

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

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

6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial

If you like this Excel tip, please share it
6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial | MyExcelOnline 6 Different Ways to Compare Two Columns in Excel - A Detailed Tutorial | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...