How to Compare Two Excel Sheets and Highlight Differences: 7 Methods

Method 1 – Using the View Side by Side Option to Compare Two Excel Sheets from Different Files and Highlight the Differences

Steps:
➤ Open the two workbooks simultaneously.

➤ Go to View Tab >> Window Dropdown >> View Side by Side Option

You will be able to view the two sheets at a time but they are aligned horizontally so we will change their alignment now.

➤ Go to View Tab >> Window Group >> Arrange All Option.

The Arrange Windows will open up.
➤ Select the Vertical option and press OK.

You can monitor the two sheets simultaneously, as shown below.

Select the cells of the SalesPerson column that have different names and change their background color.

Get the highlighted cells that show the different names of the salespersons.

Select the cells of the Sales column that have different values and change their background color.

Get the highlighted cells having different values in the two sheets.

One thing to mention is that if you have a large dataset and you want to scroll through the values of these sheets simultaneously then you have to follow this way.
➤ Go to View Tab >> Window Group >> Synchronous Scrolling Option.

Method 2 – Utilizing Spreadsheet Compare Tool for Comparing Two Excel Sheets

Steps:
➤ Go to the Start screen, search for the Spreadsheet Compare app, and open it.

A new window Spreadsheet Compare will open up.

➤ Go to Home Tab >> Compare Files Option.

The Compare Files wizard will open up, and here select the indicated sign beside the Compare box to browse the location of the January file.

We have browsed the location of our January workbook.

Choose the path of the file February workbook in the To box and press OK.

Select the options (based on which you want to compare the values of the workbooks) from the left pane. We selected the Entered Values and Names option.

Get the highlighted values that show the differences between the two sheets, and here you can see the two sheets side by side that you are comparing. You are getting the description that indicates the different cells and values.

Note:
The Spreadsheet Compare option works for only Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps.

Method 3 – Using the Compare and Merge Workbook Option

Step-01:
To enable the Compare and Merge Workbooks option, you have to follow this extra step.
➤ Go to Review Tab >> Share Workbook (Legacy) Option.

Then, the Share Workbook wizard will open up.
➤ Select the option “Use the old shared workbooks feature instead of the new co-authoring experience” and press OK.

Step-02:
Add the Compare and Merge Workbooks option to the Quick Access Toolbar.
Go to the File Tab.

➤ Select Options.

The Excel Options wizard will open up, and here select the Quick Access Toolbar option.
➤ Select the options serially, All Commands → Compare and Merge Workbooks → Add.

The Compare and Merge Workbooks option will be added to the toolbar, and press OK.

Step-03:
➤ Go to your main workbook January and select the Compare and Merge Workbooks option from the toolbar.

From the dialog box select the copy of your January file, which is February, and press OK.

The changes we have made in the February workbook will appear in this workbook and those cells will be highlighted.

Method 4 – Applying the View Side by Side Option to Compare Two Excel Sheets and Highlight Differences from the Same File

Steps:
➤ Go to View Tab >> Window Group >> New Window option.

A new workbook will open up which is basically the same workbook that is opened right now and a slight change of name will be done automatically. See that the name of the opened workbook became Compare Sheets-1 and the name of the new workbook is Compare Sheets-2.

Follow the procedures of Method-1, and then you will be able to highlight the differences.

When you close any one of the workbooks, then the changes will appear in the two sheets of the main workbook like below.

Method 5 – Using Conditional Formatting to Compare Two Excel Sheets and Highlight Differences

Steps:
➤Select the data range on which you want to apply the Conditional Formatting
➤Go to Home Tab >> Conditional Formatting Dropdown >> New Rule Option.

The New Formatting Rule wizard will appear.
➤ Select Use a formula to determine which cells to format option and write the following formula in the Format values where this formula is true: box

=C5<>'February (2)'!C5

C5 is the first cell of the SalesPerson column, February (2) is the sheet name to which we want to compare and <> represents the Not Equal to operator.

➤ Click the Format option.

The Format Cells dialog box will open up.
➤ Select Fill Option.
➤ Choose any Background Color and click OK.

The Preview Option will be shown as below, and press OK.

Get the highlighted cells in the SalesPerson column of the January (2) sheet.

Highlight the different values of the Sales column.

Method 6 – Inserting a Formula to Combine the Differences in Another Sheet

Steps:
➤ Create a sheet named Difference and select a cell in this sheet.

➤ Write the following formula in the selected cell

=IF('January (3)'!D6<>'February (3)'!D6,"January Sales: "&'January (3)'!D6&CHAR(10)&" February Sales: "&'February (3)'!D6,"")

‘January (3)’! and ‘February (3)’! are the names of the sheets and when the values of the cells D6 will be not equal in these sheets then the corresponding values will be combined here otherwise IF will return a blank.

➤ Press ENTER and drag down the Fill Handle tool.

Get the different sales values for the January and February months.

Method 7 – Using a VBA Code to Compare Two Excel Sheets and Highlight Differences

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

The Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

A Module will be created.

Step-02:
➤Write the following code

Sub highlightdifferences()

Dim Cell As Range

For Each Cell In Worksheets("January (4)").UsedRange
     If Not Cell = Worksheets("February (4)").Cells(Cell.Row, Cell.Column) Then
         Cell.Interior.Color = vbGreen
     End If
Next Cell

End Sub

We declared Cell as Range and used the VBA FOR Next loop for all of the used cells of the January (4) sheet, and the VBA IF statement will check the values of these cells with the values of the February (4). When the values are unequal, the color of those cells of the January (4) sheet will be changed to Green.

➤ Press F5.

Get the cells containing different values highlighted.

Download Practice Workbook

Compare Sheets.xlsm

<< Go Back to Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Alejandro Oct 15, 2023 at 7:15 PM

Hello,

Thank you very much for this code.

Could you please help in comparing two tables in the same worksheet? For example, the ame second table starting at F4 for the example above.

Many thanks
- Reply
  Sourav Kundu Oct 16, 2023 at 11:32 AM
  Hello, ALEJANDRO
  Thank you for your comment. We have got an effective solution for your problem. We have taken 2 tables in the same worksheet as you can see from the following image:
  
  To compare between these 2 tables, you can use the following VBA code:
```
Sub HighLight_Diff_in_same_Ws()
    Dim ws As Worksheet
    Dim cell_1 As Range, cell_2 As Range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    For Each cell_1 In ws.Range("B5:D12")
        Set cell_2 = ws.Cells(cell_1.Row, cell_1.Column + 4)
        If Not (cell_1.Value = cell_2.Value) Then
            cell_1.Interior.Color = vbGreen
            cell_2.Interior.Color = vbGreen
        End If
    Next cell_1
End Sub
```
  Note: You should modify the Worksheet Name, Range and Cells references according to your data table.
  
  When you run the VBA code, it will highlight the differences between the 2 tables in green color.
  
  I hope, this is the solution you were looking for. If you have further queries let us know in the comment section. We will solve them as soon as possible.
  
  Regards,
  Sourav Kundu.
  Exceldemy