How to Compare Two Columns in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

When you want to see how two columns differ, what do you do?

Do you scan through and keep an eye out for differences?

Run repeated searches?

If you’re doing anything like that, you’re wasting a lot of time!

There are much better options 🙂

And we’ll show you how to use them.

You’ll get perfect results, and it won’t take nearly as long.

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file here!

If you don’t have columns you want to compare at hand, you can use our example workbook.

Download it for free and follow along with the rest of the article!

Download the FREE Exercise File

Download exercise file

Highlighting differences between columns

The first method we’ll use simply highlights the differences between columns. It’s only a temporary highlighting, but it’ll give you an idea of how many lines in your columns differ.

Note: this method only looks at rows that differ between columns.

Even if a value is present elsewhere in the column, it will be highlighted if it doesn’t match the value next to it.

Kasper Langmann, Co-founder of Spreadsheeto

That sounds a little tricky, but open up the first worksheet in the example workbook and you’ll see what we mean.

On this sheet, you’ll see two lists of names. The first is in a column called “Scheduled,” and the other in a column called “Registered.” We’re going to highlight the names in the second column that don’t match those in the first.

To get started, highlight both columns.

highlight-columns

Now, in the Home tab of the Ribbon, click Find & Select. In the dropdown menu, select Go to Special…

go-to-special

In the resulting menu, select Row differences, and click OK:

row-differences

After clicking OK, you’ll see that there are several entries in the Registered column that are highlighted:

highlighted-row-differences

Looking closely, you’ll see that these entries are the ones that don’t match the corresponding entries in the Scheduled column.

For example, the name “Yolanda” is highlighted because the corresponding entry is spelled “Yolande.”

Even if there’s another “Yolanda” in the Scheduled list, this entry will be highlighted because Excel only looks at the corresponding row.

Kasper Langmann, Co-founder of Spreadsheeto

What good is a temporary highlighting?

One of the most useful things you can do is use the Fill button to highlight these cells permanently so you can go back and look at them later:

filled-row-differences

It can also give you a rough idea of how many differences there are between the columns.

Making a list of differences

If you’re looking for a more permanent highlighting of differences, you can do that too. It just requires a clever formula.

Click over to the second sheet in the workbook, where there are two lists of animals. We’re going to pull a list of the animals from column 1 that aren’t listed anywhere in column 2, and vice versa.

In C2, type the following formula:

=IF(ISERROR(MATCH(A2, $B$2:$B$50, 0)), A2, “”)

That’s a bit confusing, so we’ll break it down. Let’s start in the middle.

Kasper Langmann, Co-founder of Spreadsheeto

The matching function here is as follows: MATCH(A2, $B$2:$B$50, 0).

This returns TRUE if the value in A2 is found anywhere in B2:B50. The zero at the end tells Excel to only look for an exact match. If there’s no match, Excel returns FALSE.

Outside of that is the ISERROR function. When MATCH doesn’t find a match, it gives us an #N/A error. The presence of that error triggers the IF function.

The IF statement tells Excel two things: if the MATCH function produces an error, display the value in A2. If there is a match somewhere in the column, don’t display anything at all (signaled by two quotation marks with no space between them).

Type the function in C2 and hit Enter.

Don’t forget the dollar signs—this needs to be an absolute reference to work.
Kasper Langmann, Co-founder of Spreadsheeto
if-match-error

When you run the function, it will look like it didn’t work—because Excel won’t display anything. But that’s because it worked perfectly.

Excel finds a match for A2 in B2:B50. It’s easy to see, because it’s right there in B2.

And because of that, it displays a blank cell.

Click the fill handle and drag the formula down to the bottom of the column.

if-match-column-absolute

Every animal listed in column A that also isn’t listed anywhere in column B is now listed in column C.

Just like that.

Now, to pull out the values that are listed in B, but not A, you just need to make a couple changes and put the formula in column D.

Try making that tweak yourself, and see if the result looks like the following screenshot. If you’re having trouble, check out the correct formula in the conclusion below!

Kasper Langmann, Co-founder of Spreadsheeto
if-match-two-column

Compare columns quickly

Comparing two columns manually is easy — if they’re very short. But when you have more than a few values, you need to bring in something more powerful.

And now that you know how to access Row differences and use the IF, ISERROR, and MATCH functions together, you’ll always be able to find those differences in a flash.

Remember these strategies next time you want to compare columns, and you’ll save yourself a ton of time!

If you’re here looking for the solution to the question posed above, here it is:

=IF(ISERROR(MATCH(B2, $A$2:$A$50, 0)), B2, “”)

Kasper Langmann, Co-founder of Spreadsheeto