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

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

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

Posted by Andy Brown on 01 June 2017

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:

The original shopping list.

And here's the second 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:

Use standard Excel formulae.
Use the Get & Transform facility of Excel (Query Editor).
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.

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