Three quick ways to compare data sets in Excel

Sometimes you need to compare large data sets.

You may have two workbooks with thousands of rows, and want to know what’s changed between them.

There are a lot of ways to do this in Excel, but some are harder and more time-consuming than others.

I received a question about the best/fastest way to do this.

I answer in this video:

Note: You will need to be familiar with filters to follow this. You may want to review my video Filters: Excel’s “Easy” button before watching.

Video Summary

First, let’s talk about finding records that have been added or removed from spreadsheets.

If each record has a unique identifier, such as a username or ID number, the fastest way to do this is using COUNTIF.

COUNTIF is normally used to count things. But it’s also a quick way to see if something exists at all. If it’s there, COUNTIF will count it one time. If it’s not, it will count it zero times. That gives you your answer.

If you don’t have a unique identifier, find two columns you can combine together so that each record will have a unique combination. Most often, this would be first and last name.

Then use COUNTIFS, which is like COUNTIF, but for multiple criteria.

What if instead of trying to find records that have been added or removed, you’re looking for records that have been changed?

Then you can use TEXTJOIN to combine all of your columns into one monster column. This column will look hideous and be incomprehensible to human eyes, but easy to read for Excel.

Then use COUNTIF on that column.

Bonus Video

Sometimes people have the same first and last name. (I should know. I receive email meant for other people with my name all the time.)

This method still works even if there’s a few repeat names, as long as there aren’t too many.

I explain here: