Excel How To Compare Two Columns (3 ways)
Using Microsoft Excel to compare two columns is a great way to look for discrepancies or similarities between two different sets of data. This post provides 3 different ways to accomplish this so that you can use the one that is most appropriate for your data and workbook.
Video
Step-by-step guide
Let’s work through each option, starting with a relatively simple case.
Conditional Formatting
Let’s start by looking at two single column lists on the same worksheet. In this situation (when the lists are relatively short and stored on the same worksheet), the fastest way to compare the two columns is to use Conditional Formatting.
For example, if both lists are stored on one sheet, like this:
You may want to identify items found on both lists, or items found only on one of the lists. Conditional Formatting can help you either way.
To identify the items found multiple times (for example, on both lists), (1) select the range that includes all items and then (2) use the Conditional Formatting > Highlight Cell Rules > Duplicate Values command. In the resulting dialog, you can stick with the defaults (or pick other formatting options) and bam:
Note: technically, this identifies items found multiple times, even if multiple times in the same list. So, just be aware of this depending on your data.
If, on the other hand, you’re looking for items that appear only once (for example, are not found on both lists), you (1) select the entire range (2) Home > Conditional Formatting > Duplicate Values … and in the resulting dialog select Unique:
And bam:
Although Conditional Formatting is an easy way when the lists are relatively short and on the same worksheet, we can use formulas when the lists are longer or stored on separate worksheets. Let’s get to it.
COUNTIFS
We can use a formula with the COUNTIFS function when we have larger lists that may include multiple columns. This function counts the number of rows that match the condition (one column) or conditions (multiple columns) you specify. Since you can specify multiple conditions, you can compare more than one column at a time.
For example, we have a table that contains a list of products that includes ProdID, Option, and Cost columns:
We would like to see which of the products in Table 1 appear in Table2 (partial table shown below):
So, in Table1 we can write the following formula into the OnList2 column:
=COUNTIFS(Table2[ProdID],[@ProdID],Table2[Option],[@Option],Table2[Cost],[@Cost])
This formula essentially counts the number of rows in Table 2 where the ProdID, Option, and Cost values agree to those in Table 1. The results are shown here:
The results in the OnList2 column show that some products have 1 matching row in Table 1, and some have 0 matches.
So, the COUNTIFS function returns the count of the number of rows that match all three conditions.
But, what if instead of returning the count of the matching rows, we wanted to compare the cost values found in the two tables? For that, we could use the SUMIFS function instead of the COUNTIFS function. Let’s check it out.
SUMIFS
If you’re looking to retrieve an amount (numeric value) of an item (rather than just counting the number of matching rows) you can use the SUMIFS function. It will return the sum of numeric column values for all matching rows.
For example, if we wanted to return the cost from second table, we could use this:
=SUMIFS(Table2[Cost],Table2[ProdID],[@ProdID],Table2[Option],[@Option])
And when this formula is placed in the List2Cost column, the results are shown below:
The Diff column is a simple subscription formula.
This makes it relatively easy to compare the Cost values found in both tables.
Note: the SUMIFS function returns the sum of all matching rows. So, if there are duplicate rows with matching ProdID and Option values then the formula will return the sum of all matches.
Conclusion
These are just a few of the ways you can compare two columns in Excel. Knowing how to compare two columns in Excel can be incredibly useful for identify matches and highlighting exceptions.
If you have any alternative methods or questions, please share by posting a comment below … thanks!
Sample file
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.