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.

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:

=A1<>Sheet1!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.