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.
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 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 fruit’s 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 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, 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 and 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 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.
Hope you find this article helpful. Browse our website for finding more useful articles.
- Excel Conditional Formatting Formula
- Excel Compare Text in Two Columns (7 Fruitful Ways)
- Excel Compare Two Cells Text (9 Examples)
- Excel formula to compare two columns and return a value (5 examples)
- VLOOKUP Formula to Compare Two Columns in Different Sheets!
- How to Compare Two Columns or Lists in Excel