BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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 (this blog)
- Comparing two worksheets using Query Editor
- 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 Excel formulae

The first way to compare two worksheets is to create a unique key for each of
them, by combining the values for all of the columns using the **TEXTJOIN**
function, then use **MATCH **to find the values in one sheet which
don't exist in the other.

Note that the **TEXTJOIN** function was only introduced in Excel 2016 - for earlier versions of
Excel you'll have to concatenate the values instead.

## Joining all of the values together

Here's what the formula will look like for the first worksheet:

The
**TEXTJOIN** functions joins all of the values in cells
**A2** to **H2** together, using # as a delimiter.

Here's the formula used:

**=TEXTJOIN("#",TRUE,A2:H2)**

The three arguments are as follows:

Argument | Use |
---|---|

1 | The delimiter to use (so we'll stick a # sign between each pair of values) |

2 | Whether to include blank cells or not (there aren't any for this example, so it's a bit irrelevant) |

3 | The cells whose values we want to join together |

You can then repeat this for the second worksheet, to end up with two columns of key values.

## Creating range names

To make the formulae easier to understand, I've created a range name on each sheet as follows:

Select the cells shown, type
**FirstKey** in the range name box shown at top left and press Enter to create this (or any other) range name.

## Finding the differences

Having created formulae and range names for each of the two sheets to be compared as shown above, you can now create a function to see if the key for each row in one sheet exists in the other:

This function checks whether the key in one worksheet exists in the set of keys from the other.

Here's the formula used above (and copied down):

**=IF(ISNA(MATCH(I2,SecondKey,0)),"No match","")**

That is:

- Use the
**MATCH**function to return the position of this key in the set of all keys in the other worksheet. - If this returns an error, it means the key doesn't exist,
and we have found a difference (in which case display
**No match**).

The main drawback to this approach is that while you can easily see which rows are different, you can't see why they're different.

You can download a copy of the formulae used here.

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