How to Compare Two Excel Sheets and Highlight Differences

If you are looking for some of the easiest ways to compare two Excel Sheets and highlight differences, then you will find this article useful. By following this article, you will be able to compare and highlight the differences between the two Excel sheets having large datasets easily. So, let’s start with the main article.

Here, we have two sheets containing the sales values for January and February month. To compare the differences between these two sheets we have used the following demonstrated methods. The first sheet is for January sales records,

and the other is for February sales records.

For creating the article, we have used Microsoft Excel 365 version, you can use any other version according to your convenience.

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

Here, we have two different sheets in two different Excel workbooks named January and February. We will compare the differences of these two sheets by placing these two sheets side by side and then highlight the differences.

Steps:
➤ Open the two workbooks simultaneously.

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

After that, 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.

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

In this way, you will be able to monitor the two sheets at a time like below.

Now, select the cells of the SalesPerson column that have different names and change their background color.

Then, you will get the highlighted cells which show the different names of the salespersons.

In the same way, select the cells of the Sales column that have different values and change their background color.

Finally, you will 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.

2. Utilizing Spreadsheet Compare Tool for Comparing Two Excel Sheets

In this section, we will use the Spreadsheet Compare option for comparing the two different sheets of the two workbooks named January and February.

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

Then a new window Spreadsheet Compare will open up.

➤ Go to Home Tab >> Compare Files Option.

After that, 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.

So, here we have browsed the location of our January workbook.

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

After that, select the options (on the basis of which you want to compare the values of the workbooks) from the left pane. Here, we have selected the Entered Values and Names option.

In this way, you will get the highlighted values which show the differences between the two sheets, and here you can see the two sheets side by side which you are comparing. Besides this, you are getting the description which 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.

Read More: How to Highlight Text in Excel

3. Using the Compare and Merge Workbook Option

Suppose, you have a workbook January where you have the sales record of the January month and then you have made some alterations in this workbook and entered the sales record of the February month and saved it as February. Now, you can compare these workbooks and merge them into a sheet.

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:
Now, we will add the Compare and Merge Workbooks option to the Quick Access Toolbar.
To do this, first, go to the File Tab.

➤ Select Options.

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

After that, the Compare and Merge Workbooks option will be added to the toolbar, and then press OK.

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

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

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

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

In this section, we will highlight the differences between the two sheets from the same workbook by using the View Side by Side option. Here, the first sheet is for January sales records,

and the other is for February sales records.

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

After that, 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. Here we can see that the name of the opened workbook became Compare Sheets-1 and the name of the new workbook is Compare Sheets-2.

Now, just 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.

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

In this section, we will use the Conditional Formatting option to compare the two sheets of the same workbook.

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

Then, 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

Here, 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.

After that, the Format Cells dialog box will open up.
➤ Select Fill Option.
➤ Choose any Background Color and click on OK.

After that, the Preview Option will be shown as below, and press OK.

Then, you will get the highlighted cells in the SalesPerson column of the January (2) sheet.

In the same way, highlight the different values of the Sales column.

6. Inserting a Formula to Combine the Differences in Another Sheet

Here, we will use the IF function to combine all of the differences in the sales values between the two sheets January (3) and February (3).

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,"")

Here, ‘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.

After that, you will get the different sales values for the January and February months.

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

In this section, we will use a VBA code to compare the sheets and highlight the different values. Here, the first sheet is for January sales records,

and the other one contains the sales records for February.

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

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

After that, 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

Here, we have declared Cell as Range and used the VBA FOR Next loop for all of the used cells of the January (4) sheet, and then 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.

Finally, you will get the cells containing different values highlighted.

Practice Section

For doing practice by yourself we have provided two Practice sections like below in the sheets named Practice,

and the other one is Practice1.

Download Practice Workbook

Compare Sheets.xlsm

Conclusion

In this article, we tried to cover some of the ways to compare two Excel Sheets and highlight differences. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

<< 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