559 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Three different ways to find the differences between two Excel worksheets
Part three of a four-part series of blogs
There are a few ways to find the differences between two Excel worksheets. This blog considers 3 methods (using Excel formulae, using Query Editor and using the Inquire add-in).
This method uses Query Editor to load up the data for the two worksheets and then merge the two tables together.
To do this, choose the following option for each worksheet to be loaded:
This is the option for Excel 2016 (you can do this in Excel 2010 and 2013, but the ribbon option is different).
You should end up with two queries:
Double-click on either query to go into Query Editor.
In Query Editor, choose to merge the two tables of data:
This option on the HOME tab of the ribbon will allow you to compare the two sets of data.
Choose the first table, and select all of the columns you want to match by:
Use the CTRL key to choose columns which should be the same in both tables.
Now choose the second table, and choose the same columns in the same order:
The columns are numbered (Query Editor will match them in this order).
Finally, at the bottom choose the match options:
This option, for example, would show those rows which are in the second table but not in the first.
Here's what you should see for the Left Anti join option:
You could now repeat this to show rows in the second worksheet which aren't in the first.
Very clever! I prefer this way to the Excel formulae method (it's easier to use, for one thing). You can download the final file containing the two lists of differences here.
|Parts of this blog|
Some other pages relevant to the above blogs include:
25 Aytoun Street