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,

compare two excel sheets and highlight differences

and the other is for February sales records.

compare two excel sheets and highlight differences

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.

compare two excel sheets and highlight differences

compare two excel sheets and highlight differences

 

Steps:
➤ Open the two workbooks simultaneously.

view side by side for different files

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

view side by side for different files

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.

view side by side for different files

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

view side by side for different files

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

view side by side for different files

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

view side by side for different files

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

view side by side for different files

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

view side by side for different files

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

view side by side for different files

Finally, you will get the highlighted cells having different values in the two sheets.

view side by side for different files

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.

compare two excel sheets and highlight differences

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.

compare two excel sheets and highlight differences

compare two excel sheets and highlight differences

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

spreadsheet compare

 

Then a new window Spreadsheet Compare will open up.

spreadsheet compare
➤ Go to Home Tab >> Compare Files Option.

spreadsheet compare

 

 

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.

spreadsheet compare

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

spreadsheet compare

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

spreadsheet compare

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.

spreadsheet compare

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.

compare two excel sheets and highlight differences

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.

compare two excel sheets and highlight differences

compare two excel sheets and highlight differences

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.

compare and merge

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.

compare and merge

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.

compare and merge

➤ Select Options.

compare and merge

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.

compare two excel sheets and highlight differences

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

compare and merge

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

compare and merge

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

compare and merge

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

compare two excel sheets and highlight differences

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,

compare two excel sheets and highlight differences

and the other is for February sales records.

compare two excel sheets and highlight differences

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

view side by side option for the same file

 

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.

view side by side option for the same file

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

view side by side option for the same file

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

view side by side option for the same file

view side by side option for the same file

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


Similar Readings:


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.

compare two excel sheets and highlight differences

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.

conditional formatting

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.

conditional formatting

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

conditional formatting

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

conditional formatting

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

conditional formatting

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

compare two excel sheets and highlight differences

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

compare two excel sheets and highlight differences

compare two excel sheets and highlight differences

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

IF function

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

IF function

➤ Press ENTER and drag down the Fill Handle tool.

IF function

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

IF function

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,

compare two excel sheets and highlight differences

and the other one contains the sales records for February.

compare two excel sheets and highlight differences

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

VBA Code

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

VBA Code

After that, a Module will be created.

VBA Code

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.

VBA Code

➤ Press F5.

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

compare two excel sheets and highlight differences

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,

practice

and the other one is Practice1.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo