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

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.

**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:****S****elect 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 List1exists inList2**. The function will

**return the row position of that item in**. If you get a

*List2*hence confirming that it exists**#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.

If you like this Excel tip, please share it

XFacebookLinkedInCopyEmailPrintReddit

##### John Michaloudis

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 MyExcelOnline Academy Online Course.