Last updated on May 13th, 2018
This is an interesting situation that often comes up. Namely, sometimes one needs to differentiate data in 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.
Table of Contents
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 two columns. In every process, we will use the same table.
Compare Two Columns in Excel For Differences using Conditional Formatting
We can use the 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 to 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 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.
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 fruits name which is only in List-1. The procedure is given below.
- In Cell C2, write the formula 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 fruits name which is only in List-1.
- In this same way, you can find the fruits name which is only in List-2. In that case, the formula will be,which will be written in cell D2 and should be dragged down to find the comparison between two lists.
Read More: How to Highlight Every Other Row in Excel
How to Compare Two Columns in Excel For Differences Using Formula
In this procedure, if List-1 contains any fruits 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 formulaand press enter.
=IF(COUNTIF($B$2:$B$8, $A2)=0, "Not Found in List-2", "")
- 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.
Download Working Files
So, we can see the different processes of finding the differences between two columns. The comparison between two columns can be obtained for matches also. Out of the 4 procedures we discussed using the 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.
Hope you find this article helpful. Browse our website for finding more useful articles.