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:

https://www.bluepecantraining.com/wp-content/uploads/2021/01/Compare-Two-Sheets.xlsx https://www.bluepecantraining.com/wp-content/uploads/2021/01/Ver1.xlsx https://www.bluepecantraining.com/wp-content/uploads/2021/01/Ver2.xlsx

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.

Compare Data in Different Workbooks

If the worksheets are in two different workbooks, follow these steps:

  1. Arrange the workbooks on your screen so they can both be seen.  On the Ribbon’s View tab, click the Arrange All button in the Windows group.
  2. In the Arrange Windows dialog box, select Vertical and then click OK.
  3. Conditional formatting can’t be used across workbooks so use the formula method described above. In one of the workbooks, create a new worksheet and use the same formula to compare the first cell in workbook 1 with the first cell in workbook 2.  The only thing to watch out for is that when you refer to a cell in another workbook (by clicking on it), the cell address is by default locked with dollars.  Delete the dollars to make the cell reference relative.  If you are not sure about how to create this formula, please watch the video.