# How to Compare Two Columns in Excel For Finding Differences

This is an interesting situation that often comes up. Namely, sometimes one needs to differentiate data into two different columns. There are so many processes in which Excel compare two lists and return differences. In this article, we will see the ways on how to compare two columns in Excel for differences.

So, letâ€™s get started with a simple example, to illustrate how to accomplish this.

## Introduction

Here we have two lists where some fruits name is placed. We will compare the two lists for finding the differences. The two lists containing the fruits name is given below.

We will see 4 different processes of finding the differences between the two columns. In every process, we will use the same table.

## Compare Two Columns in Excel For Differences using Conditional Formatting

We can use conditional formatting to highlight the unique values of two columns. The procedure is simple and given below.

• First, select the ranges where you want to apply the conditional formatting. In this example, the range is A2:B8

• Now, in the Home Tab click on the Conditional Formatting and Under Highlight Cells Rules click on to Duplicate Values.

• In the Duplicate values Dialogue Box if you select Duplicate you will see the duplicate values of the two cells.

• If you select Unique in the Duplicate values Dialogue Box you will see the unique values of the two cells.

• Press OK to confirm the conditional formatting.

Read More:Â How to Compare Two Columns or Lists in Excel

## Compare Two Columns in Excel Using VLOOKUP

We can use the VLOOKUP function in Excel to find the differences between two lists or columns. The procedure is given below.

• In Cell C2 use the formulaÂ
 Source code
`=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$8,1,0)),"NO","YES")`
and press Enter.

• After pressing Enter, you will see the statement NO in cell C2. That is because the fruit name Apple from List-1 is not available in List-2.

• Now drag down the formulated cell downwards from C2 to C8 to see the differences between List-1 and List-2.

Read More:Â How to Compare Two Columns in Excel Using VLOOKUP

## Compare Two Columns in Excel & Returns the Difference

Here we will be using IF, ISERROR, and MATCH functions to compare two columns. We will compare List-1 with List-2. The formula will calculate the two lists and will return the fruitâ€™s name which is only in List-1. The procedure is given below.

• In Cell C2, write the formulaÂ
 Source code
`=IF((ISERROR(MATCH(A2,\$B\$2:\$B\$14,0))),A2,"")`
and press enter.

• After pressing Enter you will see the name of the fruit Apple which is only placed in List-1.

• Now drag down this formulated cell to see the fruitâ€™s name which is only in List-1.

• In this same way, you can find the fruitâ€™s name which is only in List-2. In that case, the formula will be,
 Source code
`=IF((ISERROR(MATCH(B2,\$A\$2:\$A\$15,0))),B2,"")`
which will be written in cell D2 and should be dragged down to find the comparison between the two lists.

## How to Compare Two Columns in Excel For Differences Using Formula

In this procedure, if List-1 contains any fruit name which is not placed in List-2, the formula that we will be using will say that the fruit name from List-1 is not found in List-2. Let`s start the comparison.

• In cell C2 write the formula
 Source code
`=IF(COUNTIF(\$B\$2:\$B\$8, \$A2)=0, "Not Found in List-2", "")`
Â and press enter.

• After pressing Enter you will see the statement, Not Found in List-2 has appeared. It is because the fruit name Apple from List-1 is not in List-2.

• Now, drag down the formulated cell C2 downwards to copy this formula for the rest of the cells of column C. By doing this, you will see the differences between the two columns.

## Conclusion

So, we can see the different processes of finding the differences between the two columns. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed using conditional formatting is the best way to compare between two columns. Because in conditional formatting you can compare between multiple columns, the procedure is simple and fast and you can find both matches and differences.

#### Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

Guillermo J Wainselboim Jul 17, 2017 at 8:12 PM

Good day

My apologies for the inconvenience

Thank you and have a nice day