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.