How to compare data between two columns for differences in an Excel spreadsheet

This tutorial will show you how to compare data in two different columns in an Excel spreadsheet and find the differences. The data doesn’t have to be in any particular order, and it can be any type of data, whether it’s names, numbers, a mix of the two, etc. Also, each column can have a different amount of rows than the other and this will still work the same way. This example was done in Excel 2010.

I’ve used this to compare over 2,000 rows of data, but in this example I will keep it simple and compare two columns that contain 10 rows of data each.

Highlight the data in column A. Then on the Home tab, click Conditional Formatting and then New Rule.

In that window, click Use a formula to determine which cells to format and enter the following code into the formula bar.

=countif($B:$B, $A1)

Click on Format and select a new font, color, fill, or something else that you’d like to use.

Do the same exact steps for column B, but choose a different format and enter this code in the formula bar instead:

=countif($A:$A, $B1)

Now that you have your conditional rules set up for columns A and B, select the first row in column C and paste this code:

=if(countif($A:$A, $B1)<>0, "-", "Not in A")

Once you’ve done that, drag that row down until your data stops. Do this by selecting the row, move your cursor to the bottom right of that row, and then drag down until there is no more data (in my case, line 11).

Now paste this code in the first row of column D and then drag that row down as well:

=if(countif($B:$B, $A1)<>0, "-", "Not in B")

You’ll end up with something like what you see below, and be able to tell what data is missing from each column.

excelcompare4