How To Compare Rows From Two Sets Of Data In Excel

In this tutorial we are going to compare two data ranges that were supposed to be exact, but have some minor differences. We’ll use the Go To Special command to help us highlight those differences.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have two sets of data that appear to be the same:

3216-1

But when I scroll down to the bottom I can see that the totals are slightly different:

3216-2

I can painstakingly go through each line to try to find the differences, or I can solicit Excel’s help through the “Go To Special” command. Now, with this process you can only compare two columns of data at a time, but once you’ve compared the first set of columns, the rest is quite easy.

To start, we’ll highlight the two description columns, which are columns B & K:

3216-3

Then I’ll use my keyboard to enter the following keystrokes:

F5 > Alt + S > Alt + W

This series will do the following:

F5 – opens up the Go To dialog box
Alt + S – selects the Special option
Alt + W – selects the Row differences

3216-4

Now when I click on OK, it will highlight the cells in column B that are different than those in column K:

3216-5

In this case it highlighted cell B7 where “moulding” was spelled incorrectly, and B9 where the description was totally different.

Now, to check the rows for all the other columns, I can hold down my Ctrl key and highlight the next pair of columns and hit the F4 key and it will continue to highlight the cells for those that are different. In this example, I’ve only done three sets of columns:

3216-6

Once I’ve completed all the columns I want to compare, I only need to select a fill color and Excel will highlight all the cells from the first data set that are different than the second:

3216-7

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply