Introduction
If you work with data, I’m sure you’ve needed to compare two columns in Excel at some point. Whether you’re a data scientist or just someone who spends a lot of time with spreadsheets, knowing how to efficiently compare columns can save you a lot of time and effort. In this guide, we will explore various methods to compare two columns in Excel, through working examples.
Learn More: Microsoft Excel for Data Analysis
Overview
- Learn to use conditional formatting to visually compare two columns in Excel.
- Learn to find matching values between two columns using the IF function.
- Learn to make complex and precise comparisons between two columns in Excel using the VLOOKUP, EXACT, and COUNTIF functions.
Table of Contents
Ways to Compare Two Columns in Excel
There are 5 different ways in which you can compare the contents of two columns in Excel.
1. Using Conditional Formatting
Let’s say you have two columns, A and B, filled with names, and you want to highlight names in Column B that do not appear in Column A.
Steps:
- Select the range of cells in Column B.
- Go to the ‘Home’ tab, click on ‘Conditional Formatting’, then ‘New Rule’.
- Choose ‘Use a formula to determine which cells to format’.
- Enter the formula:
=ISERROR(MATCH(B1, $A$1:$A$10, 0)). - Click ‘Format’, choose a color to highlight the differences, and click ‘OK’.
Example:
| Column A | Column B |
| Alice | Bob |
| Bob | Alice |
| Charlie | Dave |
| Dave | Charlie |
| Eve | Frank |
After applying conditional formatting, “Frank” in Column B would be highlighted, indicating it does not appear in Column A.
2. Using the IF Function
Here’s how to find which values in Column A match those in Column B.
Steps:
- In a new column (Column C), enter the formula:
=IF(A1=B1, "Match", "No Match"). - Drag the fill handle down to apply the formula to other cells in the column.
Example:
| Column A | Column B | Column C |
| Alice | Bob | No Match |
| Bob | Alice | No Match |
| Charlie | Charlie | Match |
| Dave | Dave | Match |
| Eve | Frank | No Match |
3. Using the VLOOKUP Function
Suppose you want to see if values in Column A exist in Column B and retrieve related data.
Steps:
- In a new column, enter the formula:
=IF(ISNA(VLOOKUP(A1, $B$1:$B$10, 1, FALSE)), "Not Found", "Found"). - Drag the fill handle down to apply the formula to other cells.
Example:
| Column A | Column B | Column C |
| Alice | Bob | Found |
| Bob | Alice | Found |
| Charlie | Dave | Found |
| Dave | Charlie | Found |
| Eve | Frank | Not Found |
4. Using the EXACT Function
If you want to compare text values in Columns A and B, considering case sensitivity, here’s how you can do it.
Steps:
- In a new column, enter the formula:
=EXACT(A1, B1). - Drag the fill handle down to apply the formula to other cells.
Example:
| Column A | Column B | Column C |
| Alice | alice | FALSE |
| Bob | Bob | TRUE |
| Charlie | charlie | FALSE |
| Dave | Dave | TRUE |
| Eve | Eve | TRUE |
5. Using the COUNTIF Function
Here’s how you can count how many times each value in Column A appears in Column B.
Steps:
- In a new column, enter the formula:
=COUNTIF($B$1:$B$10, A1). - Drag the fill handle down to apply the formula to other cells.
Example:
| Column A | Column B | Column C |
| Alice | Bob | 1 |
| Bob | Alice | 1 |
| Charlie | Dave | 1 |
| Dave | Charlie | 1 |
| Eve | Frank | 0 |
Conclusion
As you have seen in this article, you can compare columns in Excel in many different ways. Each method has its unique advantages and the one you choose depends on your use case. If all you need is a quick visual check, conditional formatting is the best method. For more precise comparisons you can use functions and formulas like COUNTIF, EXACT, VLOOKUP, or IF. With some practice, you can find out which ones best suit your needs and use them to streamline your workflow.
Master Excel functions in 2 hours: Microsoft Excel Tutorial for Beginners
Frequently Asked Questions
Q1. How do I compare two columns for differences in Excel?
A. You can use conditional formatting to find the differences between two columns in Excel. Here’s the formula for it: =ISERROR(MATCH(B1, $A$1:$A$10, 0))
Q2. Can I compare two columns in Excel for duplicates?
A. Yes, you can use the COUNTIF function or conditional formatting to find duplicates between two columns.
Q3. What is the best formula to compare two columns in Excel?
A. The most commonly used method to compare two columns in Excel is the IF function. It is written as =IF(A1=B1, "Match", "No Match"). However, for more complex comparisons, you can use the VLOOKUP or EXACT function.
Q4. How do I compare two columns in Excel using VLOOKUP?
A. You can use VLOOKUP along with IF and ISNA to find matches between two columns in Excel: =IF(ISNA(VLOOKUP(A1, $B$1:$B$10, 1, FALSE)), "Not Found", "Found").
Q5. Can I compare two columns in Excel without using formulas?
A. Yes, you can use Excel’s built-in tools like conditional formatting to visually compare two columns without writing formulas.
Beginnerconditional formattingCountifcountif formuladata scientistexact formulaExcelfunctionsgoogle sheetsGuidespreadsheetvlookup formula
Sabreena Basheer is an architect-turned-writer who's passionate about documenting anything that interests her. She's currently exploring the world of AI and Data Science as a Content Manager at Analytics Vidhya.
