Home Â» How to Compare Two Excel Sheets for Differences

# How to Compare Two Excel Sheets for Differences

Occasionally you may want to compare two different Excel sheets to identify the differences between them.

Fortunately this is fairly easy to do and this tutorial explains how.

### How to Identify Differences Between Two Excel Sheets

Suppose we have the following two sheets in Excel with some information about basketball players:

Â

Â

Â

Â

Â

Â

Â

To compare the differences between the two sheets, we can create a third sheet and use the following formula in cellÂ A2:

```=IF(Sheet1!A1  Sheet2!A1, "Sheet1:"&Sheet1!A1&", Sheet2:"&Sheet2!A1, "")
```

We can then copy this formula to each cell, which results in the following:

If the corresponding cells in Sheet1 and Sheet2 are identical, then the cell in Sheet3 will be blank. However, if the cells are different between the two sheets then the differences will be shown in Sheet3.

For example, cell A9 in the first sheet has a value ofÂ GÂ while cell A9 in the second sheet has a value ofÂ X:

### How to Highlight Differences Between Two Excel Sheets

In addition to identifying the differences between the two sheets, you can also highlight the differences using conditional formatting.

For example, suppose we want to highlight each cell in Sheet2 that has a different value from the corresponding cell in Sheet1. To do this, we can use the following steps:

Step 1: Select the range of cells.

First, select the entire range of cells that weâ€™re interested in applying conditional formatting to:

Step 2: Choose conditional formatting.

Next, on theÂ HomeÂ tab within theÂ StylesÂ group, clickÂ Conditional FormattingÂ and then clickÂ New Rule.

Step 3: Choose conditional formatting.

Choose the option titledÂ Use a formula to determine which cells to format. Then type in the following formula:

`=A1Sheet1!A1`

Then clickÂ FormatÂ and choose a color youâ€™d like to use to highlight the cells that are different. Then clickÂ OK.

Once you clickÂ OK, the cells in Sheet2 that have different values than the corresponding cells in Sheet1 will be highlighted:

You can find more Excel tutorials here.