How to Compare Two Excel Sheets and Highlight Differences (7 Ways)

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.

Download Workbook

7 Ways to Compare Two Excel Sheets and Highlight Differences

Here, we have two sheets containing the sales values of 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 versions according to your convenience.

Method-1: Using View Side by Side Option to Compare Two Excel Sheets from Different Files and Highlight 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 aligned horizontally and 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 which have different names and change their background color.

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

In the same way, select the cells of the Sales column which 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.

Read More: How to Highlight a Cell in Excel (5 Methods)

Method-2: Using 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:
➤ At first, go to the Start screen and 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 are showing 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 also which is indicating 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 Fill Color in Excel Cell Using Formula (5 Easy Ways)

Method-3: Using 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, at 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.

Read More: Excel Cell Color: Add, Edit, Use & Remove

Method-4: Using 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. Like 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.

Related Content: How to Highlight from Top to Bottom in Excel (5 Methods)

Similar Readings

• How to Highlight Every 5 Rows in Excel (4 Methods)
• How to Highlight Selected Text in Excel (8 Ways)
• Excel Formula Based on Cell Color (5 Examples)
• How to Highlight Selected Cells in Excel (5 Easy Ways)

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

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.

Read More: Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)

Method-6: Using 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

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 of the January and February months.

Read More: How to Highlight Cell Using the If Statement in Excel (7 Ways)

Method-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 FOR loop for all of the used cells of the January (4) sheet and then IF 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 into Green.

➤ Press F5.

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

Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)

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.

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.

Related Articles

• How to Highlight a Row in Excel (5 Quick Methods)
• Excel VBA to Highlight Cell Based on Value (5 Examples)
• How to Highlight a Column in Excel (3 Methods)
• Fill Cell with Color Based on Percentage in Excel (6 Methods)
• How to Change Cell Color Based on a Value in Excel (5 Ways)