How to Compare two Excel Sheets or Workbooks for Differences

Excel only displays one worksheet at a time in a given window. Therefore, comparing two workbooks or worksheets becomes a challenge. Luckily, there are multiple ways that can be used to solve this challenge. From the use of formulas, to Pivot Tables or Even third-party Addins, you can easily navigate this challenge

In this tutorial, I will guide you step by step on how you can compare two Excel sheets for differences. 

Use VBA Code

VBA helps users to automate Excel Tasks programmatically. Comparing two Excel sheets for differences is not an Exception. 

1. Open VBA editor by using the shortcut ALT+F11 for windows and FN + OPT +F11 on Mac

2. Click on insert>module

3. Copy and paste the following VBA code

Sub CompareSheets()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim cell1 As Range
    Dim cell2 As Range
    Dim diffCount As Long
    
    ' Set references to the sheets you want to compare
    Set ws1 = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your first sheet name
    Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Change "Sheet2" to your second sheet name
    
    ' Initialize counter for differences
    diffCount = 0
    
    ' Loop through each cell in the first sheet
    For Each cell1 In ws1.UsedRange
        ' Set the corresponding cell in the second sheet
        Set cell2 = ws2.Cells(cell1.Row, cell1.Column)
        
        ' Compare cell values
        If cell1.Value <> cell2.Value Then
            ' Highlight the cells with differences
            cell1.Interior.Color = RGB(255, 0, 0) ' Red color
            cell2.Interior.Color = RGB(255, 0, 0) ' Red color
            
            ' Increment the difference count
            diffCount = diffCount + 1
        End If
    Next cell1
    
    ' Display a message with the number of differences found
    If diffCount > 0 Then
        MsgBox diffCount & " differences found!", vbExclamation
    Else
        MsgBox "Sheets are identical.", vbInformation
    End If
End Sub

4. You can then run the CompareSheets macro from the “Run Macro”  

As you can see from the image below, the macro highlights all the differences.

Compare side-by-side

The easiest way to compare two Excel Sheets or Workbooks for differences is by using the compare side-by-side feature in Excel. Let’s look at how you can compare side by side in different scenarios.

1. How to compare two sheets side by side (in the same workbook)

If the Excel Sheets you want to compare are in the same workbook, then this is very easy. 

1. Open the Excel file 

2. Click on the View Tab then Navigate to the Window group and select New Window

This action will launch the same Excel file in a different window

3. Click on the view side-by-side option on the ribbon to enable this mode

4. Select one sheet to display in the first window and another to display in the second window

5. Arrange All to view the sheets side by side vertically

To make the comparison easier, you can make use of synchronous scrolling. You can find this option in the view tab.

Finally, remember that both windows show the same workbook. Thus when you make any changes it will be reflected in both. Once you close any instance the file name will revert to as it was.

2. How to worksheets side-by-side in different workbooks

Note that this method only allows you to compare two Excel files. In case you have more than two open then Excel will prompt you with a dialogue box so as you choose two files. 

Also, note the Files that need to be compared have to be opened. If they are not open then Excel will not detect them in this feature. 

 Here are the steps.

1. Launch the two workbooks you wish to compare

2. On the first workbook click on the view tab then select view side by side under the Window group

Note that in default Excel displays the two workbooks side by side in a horizontal position. 

3. You can set it in a vertical position by clicking on Arrange All, and then selecting the vertical option

Finally, you can compare row by row simultaneously by enabling the Synchronous Scrolling option.  

How to arrange more than two workbooks side by side

You can arrange more than two Excel files side by side by following these steps. 

1. Open all the Workbooks that you wish to compare

2. Click on the view tab then select Side by Side option

This will launch a dialogue box containing all the open Excel files. 

3. If you want to view all at the same time click on the Arrange All option from the ribbon. Now choose your preferred mode to view your workbooks side by side. You have the following options

• Tiled
• Horizontal
• Vertical
• Cascade

Use formula to compare two sheets for  differences

You can compare two Excel sheets for differences by using a formula. This will indicate the cells that have different values in the sheets in question. Here are the steps:

1. First open a blank sheet beside the ones you wish to compare. 

2. Copy and paste the following formula in cell A1 of the sheet you have just created. 

=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

Note that the above formula assumes that the Excel sheets you want to compare are named Sheet1 and Sheet2 respectively. If they have different names make sure to replace them in the formula.

3. Copy it down and to the right by using the fill handle. Make sure the formula covers the range equivalent to the other sheets that you wish to compare to have accurate results

Since this formula uses relative cell reference, the formula will adjust depending on the cell. Therefore, the formula in A1 looks through A1 in shet1 and A1 in sheet 2. Also, the formula in B1 looks through B1 in the sheets that you wish to compare. 

4. In case there is a difference in the two sheets the results will be displayed in this new sheet. 

However, it is important to note that the differences in dates using this method are presented as serial numbers. Thus, this may not be the best method to compare two sheets with dates for differences 

Highlight differences using Conditional Formatting

What better way to compare differences in two Excel sheets than something that is visual? Conditional formatting makes it easy for users to spot differences. Here are the steps

1. Select all the cells that you want to check differences by using the mouse or the shortcut Ctrl+Shift+End

2. Navigate to the Home tab then click on conditional formatting under the styles group. Then click on the new rule copy and paste the formula below

=A1<>Sheet2!A1

Note that Sheet2 refers to the name of the second file that you want to check for differences.

3. Lastly select the color that you wish to highlight the different values then click on okay. 

Although this method is good for finding differences in values between two sheets, it has some limitations. 

• It only finds the difference in values hence, it cannot find the difference in cell formatting or compare formulas.
• It only works on sheet level hence it cannot detect any structural differences.
• The conditional formatting method 

Use an Excel Addin

Another way to compare two Excel sheets or files for differences is by using add-ins. You can use third-party add-ins such as:

• Kutools
• Abblebits
• Synkronizer Excel compare
• XLcompare

Addins help simplify Excel tasks with custom-made functions and features integrated directly into the Excel software. To compare Excel files using the above faddins you need to download them and install them first. Once you do click on the respective add-in tab then look for the compare feature. Finally, select the sheets you want to compare.

Note that some add-ins require a subscription. Others are free and some offer limited free features that you can take advantage of.