How to Compare Two Columns in Excel: Step-by-Step
Comparing two columns in Excel is something you’d need to do very often. Sometimes to see if both the columns tally. And the other times, to see if both the columns are unique to each other 👀
In both cases, Excel fails to offer any in-built feature to help you do that. Yeah, very unfortunate – but that’s how it is.
However, there are still many hacks that you can use to compare two columns in Excel.
To learn those, continue reading the guide below 👇
Also, download our free sample workbook here to tag along with the guide.
Table of Contents
Compare two columns to find matches & differences
Comparing two columns to spot-matching data in Excel is all about putting an IF function in place 😎
For example, the image below has two lists.
Both lists contain names that apparently seem the same. But we still need to run a check to see if they are actually the same.
Oh no! Don’t start scanning the lists already. We have a hack to do that.
- In a neighboring column to the lists (Column C), write the IF function as below:
= IF (
- As the first argument, write in the logical test to be performed by the IF function.
= IF (A2=B2
We want to see if the first and the second list match. And so, we have set the logical test to be performed to Cell A2=B2. Excel would now check if Cell A2 is equal to B2 🚀
- As the second argument, type in the value that you’d want to be returned if the logical test turns true (value_if_true).
You can set any value as the value_in_true enclosed in double quotation marks. For now, we are setting it to “Names match”.
= IF (A2=B2, “Names match”
- For the value_if_false, type in the value that you’d want to be returned if the logical test turns false.
Let’s set it to “Names don’t match”.
= IF (A2=B2, “Names match”, ”Names don’t match”)
With this, we are all done writing the IF function 👍
- Hit “Enter” to get the results as follows:
The result is “Names match” for the first row of the list. And we can clearly see the same name “Charles” in both lists.
- Drag and drop the formula till the end (until the lists continue).
The whole list is now checked for the same data. And for some names (in Rows 3, 4, and 7), the IF function returns “Names don’t match” ❌
Pay attention to the dataset to note that for:
- Row 3: The spelling of “Tilbury” and “Tillbury” differs.
- Row 4: “Samia” and “Leon” are clearly two different names.
- Row 7: “Peter” and “Tim” are clearly two different names.
Kasper Langmann, Microsoft Office SpecialistThis is one way how you can compare columns in Excel to know if both of the columns match or not. Such a row-to-row comparison also helps identify the individual items that differ (like Row 3,4, and 7 above) 🚴♀️
Compare two columns for case-sensitive matches
Take a quick look at the image below (Row 5).
The text “Pesso” in the first list starts with a capital P. Whereas, the text “pesso” in the second list starts with a small “p” 👆
However, the IF function treats them the same and returns the value “Names Match”.
This means that the IF function performs a case-insensitive comparison. But what if you want to run a case-sensitive comparison of two lists? Not a problem – we can do that too. See below.
- Write the EXACT function as below:
= EXACT (A2,B2)
The EXACT function checks if two given cells (A2 and B2) have exactly the same value. It also checks for case sensitivity.
It returns TRUE if both the cells are equal, and FALSE if they are not exactly equal.
For the first row, the EXACT function returned TRUE. That’s because the name “Charles” in the first column is exactly the same as “Charles” in the second column.
- Nest the EXACT function above in the IF function as follows.
= IF ( EXACT (A2,B2)
This will act as the logical test. If the EXACT function returns TRUE, the IF function will return the value_if_true.
And if the EXACT function returns FALSE, the IF function will return the value_if_false 🎯
- Type in the value that you’d want to be returned if the EXACT function returns TRUE.
We are again setting it to “Names match”.
= IF ( EXACT (A2,B2), “Names match”
- Type in the value that you’d want to be returned if the EXACT function returns FALSE.
= IF ( (EXACT(A2, B2), “Names match”, ”Names don’t match”)
- Hit “Enter”.
- Drag and drop the formula to the whole list.
Note that this time, the results for the same Row 5 (Pesso) change 🤩
The IF function now tells us that the names do not match because they do not have the same case.
Highlight matches or differences between two columns
If you don’t want a whole separate list of formulas and results while you compare two columns – no worries, we hear you.
Another efficient way to compare columns is by running conditional formatting in Excel. You can use it to identify matching and differing columns, both 🎨
Let’s see how.
- Select the lists that you want to compare. If you don’t want both lists to be highlighted, you may select any one list only.
- Go to Home Tab > Conditional Formatting.
- From the drop-down list that appears, select Highlight Cell Rules option > More Rules.
This will take you to the “New Formatting Rule” dialog box as follows:
- Under Rule Type, select “Use a formula to determine which cells to format”.
- Under the Rule description, write the following formula:
=$A2=$B2
This tells Excel to format the cells where cell A2 is equal to cell B2.
We have turned the column reference into an absolute one so that the same formatting can be applied to the whole list 📝
Kasper Langmann, Microsoft Office Specialist- Click on the “Format” button to select any Format that you’d want to be applied to the cells of the list that match.
We have selected the Fill option in green color as below.
- All done – Click “Okay”.
Excel highlights all the rows that match 📌
Pro Tip!
Did you note Row 5 (Pesso) being highlighted in green? This means conditional formatting also carries out a case-insensitive comparison of columns.
Similarly, if you want to apply conditional formatting to those rows of the lists that do not match:
- Launch the “New Formatting Rule” dialog box.
- Under Rule Type, select “Use a formula to determine which cells to format”.
- Under the Rule description, write the following formula:
=$A2<>$B2
This tells Excel to format the cells where cell A2 is not equal to (<>) B2.
- Choose any Format that you’d want to be applied to the cells that meet the above criterion. Like we are selecting a red fill.
- Press “Enter” and you get the following results.
This time Excel has red-highlighted the cells that are different in the whole list ⭕
That’s it – Now what?
We hope you now know how to compare columns in Excel – whether to find matching items or unique items.
In the guide above, we’ve explored different functions that can help you do that. We also learned to use conditional formatting to highlight the similarities and differences between any two columns in Excel.
Excel functions and features are super versatile. Even if Excel misses out on a specific function, you can find a way to make that happen by tweaking some other Excel functions.
There are just so many functions in Excel to explore. And once you know them all – there’d be barely anything in Excel you couldn’t do 💪
Want to begin learning them? We suggest you start with the VLOOKUP function, SUMIF, and IF functions of Excel.
Other resources
Conditional formatting can help you do so much more in Excel. To learn more about it – click here.
And once you are done comparing columns in Excel, you might want to make your data more readable and easier to decipher. Read our blog on how this can be done through the grouping of rows and columns in Excel.