Three different ways to find the differences between two Excel worksheets
Part one 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) (this blog)
  2. Comparing two worksheets using Excel formulae
  3. Comparing two worksheets using Query Editor
  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.

How to compare two Excel worksheets (three methods)

Following a question from a course, I thought I'd give 3 possible ways in which you can find all the differences between two worksheets.  The example I'll use is to see if someone's been tampering with my shopping list.  Here's the original:

First shopping list

The original shopping list.

 And here's the second list:

Second shopping list

The revised shopping list (in each case I've helpfully coloured in the cells which are different).

If you want to try any of the methods given in this blog, you can download the two worksheets to compare here.  The three methods are:

  1. Use standard Excel formulae.
  2. Use the Get & Transform facility of Excel (Query Editor).
  3. Use the Inquire add-in, or the (I think identical) Spreadsheet Compare standalone program (as shown here).

I've used Excel 2016 for this blog, and included notes when you'll need to do different things for different versions.

So ... let's start with a vanilla Excel method, combining various functions. 

This blog has 0 threads Add post