This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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). How to compare two Excel worksheets (three methods) Comparing two worksheets using Excel formulae Comparing two worksheets using Query Editor (this blog) Compare worksheets using Inquire or Spreadsheet Compare

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).

How to compare two Excel worksheets (three methods)
Comparing two worksheets using Excel formulae
Comparing two worksheets using Query Editor (this blog)
Compare worksheets using Inquire or Spreadsheet Compare

Posted by Andy Brown on 01 June 2017

In this blog

Loading the data
Merging the two queries

Comparing two worksheets using Get & Transform

This method uses Query Editor to load up the data for the two worksheets and then merge the two tables together.

Loading the data

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.

Merging the two queries

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
How to compare two Excel worksheets (three methods) Comparing two worksheets using Excel formulae Comparing two worksheets using Query Editor (this blog) Compare worksheets using Inquire or Spreadsheet Compare