5 Ways To Compare Two Columns In Excel

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.

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:

1. Select the range of cells in Column B.
2. Go to the ‘Home’ tab, click on ‘Conditional Formatting’, then ‘New Rule’.
3. Choose ‘Use a formula to determine which cells to format’.
4. Enter the formula: =ISERROR(MATCH(B1, $A$1:$A$10, 0)).
5. Click ‘Format’, choose a color to highlight the differences, and click ‘OK’.

Example:

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:

1. In a new column (Column C), enter the formula: =IF(A1=B1, "Match", "No Match").
2. Drag the fill handle down to apply the formula to other cells in the column.

Example:

3. Using the VLOOKUP Function

Suppose you want to see if values in Column A exist in Column B and retrieve related data.

Steps:

1. In a new column, enter the formula: =IF(ISNA(VLOOKUP(A1, $B$1:$B$10, 1, FALSE)), "Not Found", "Found").
2. Drag the fill handle down to apply the formula to other cells.

Example:

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:

1. In a new column, enter the formula: =EXACT(A1, B1).
2. Drag the fill handle down to apply the formula to other cells.

Example:

5. Using the COUNTIF Function

Here’s how you can count how many times each value in Column A appears in Column B.

Steps:

1. In a new column, enter the formula: =COUNTIF($B$1:$B$10, A1).
2. Drag the fill handle down to apply the formula to other cells.

Example:

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

K.C. Sabreena Basheer

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.