Home » How to Compare Two Excel Sheets and Find Differences

# How to Compare Two Excel Sheets and Find Differences

In this video, I demonstrate how to compare two Excel worksheets in order to find the differences between them. I look at two scenarios: one where the worksheets are in the same workbook and then where the worksheets are in separate workbooks. Download the featured files here:

30+ hrs - includes certification.

All purchases help to support this blog - thanks!

### Compare Data in the Same Workbook

If the worksheets are in the same workbook, the first thing to do is to display both worksheets on the screen at the same time. To achieve this:

1. Click on the Ribbon’s View tab and then click on the New Window button in the Window group.
2. Still on the Ribbon’s View tab, click on the Arrange All button.
3. In the Arrange Windows dialog box, select Vertical and then click OK.
4. Select the worksheets you want to appear in each window.

#### Method 1: Conditional Formatting

To use conditional formatting to compare the two worksheets, do the following:

1. Select all the data in the first worksheet.
2. On the Ribbon’s Home tab click on Conditional Formatting in the Styles group.
3. Select New Rule… in the menu.
4. Under Select a Rule Type:, select Use a formula to determine which cells to format.
5. In the Format values where this formula is true: box, write the following formula: =A1<>’Ver2′!A1, where Ver2 is the name of worksheet you are comparing with.  The formula returns TRUE if the cells contain values that are different.
6. Click on the Format button to specify a format to apply if the rule is met – for example a red fill and white font.  Click on OK to confirm the format.
7. Click on OK to confirm the rule.

#### Method 2: A Formula

The second method for comparing worksheets and showing differences uses a formula.  To use this method:

1. Create a new worksheet within the same workbook that contains the worksheets you want to compare.
2. The formula to use is =IF(‘Ver1′!A1=’Ver2′!A1,’Ver2′!A1,”Ver1:”&’Ver1′!A1&” | Ver2:”&’Ver2’!A1)
3. Copy the formula across and down the worksheet.