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

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

Posted by Andy Brown on 01 June 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Loading a worksheet

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:

Two queries loaded

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:

Merging queries

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:

Matching by column

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:

Columns to merge

The columns are numbered (Query Editor will match them in this order).

Finally, at the bottom choose the match options:

Right anti join

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:

Left anti join results

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.

This blog has 0 threads Add post