How to Compare Sheets Side by Side in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you how to use Excel to compare sheets side by side. Comparing various datasets at the same time is a very common scenario in our lives. We need to compare sheets side by side to get a clear idea of a topic. The method of comparing sheets side by side varies and entirely depends on the user. The user might want to view the sheets side by side to compare them with their own eyes. Or they can implement various formulas to spot different areas instantly.

Moreover, they have a VBA macro to compare the side of the sheet by side in Excel by highlighting the other values. Below, we showed how, in Microsoft Excel, users can compare the sides of the worksheet side by side by following various methods.

Below, we showed methods where we compared the sheet side by side and highlighted the cell values that were different from each other.

excel compare sheets side by side


Compare Sheets Side by Side in Excel: 5 Useful Examples

Here we are giving five separate examples to compare sheets side by side. To avoid any kind of compatibility issue, users are advised to use the Microsoft 365 edition.


1. Using View Tab to Compare Sheets Side by Side in Excel

The most common and most accessible way a person can view the worksheets is by using the View Side by Side option from the View tab.

  • First, go to the View tab> Window View Side by Side command.

Activate the side-by-side option from the ribbon menu

  • Then, in the Compare Side by Side dialog box, select the other worksheet in another open workbook.
  • Press OK after this.

choose which slide to show side by side from the Compare slide by slide

  • After selecting the Sales Value in 2022, we can see that both of the worksheets are now open simultaneously and scroll synchronously.

Worksheets are compared side by side

Note: Both of the worksheets need to be open at the same time before clicking on the Side by Side.

Read More: How to Compare Two Excel Sheets for Differences in Values


2. Using Formula to Find Differences in Values and Compare Sheets Side by Side in Excel

We can use formulas that consist of the IF function to compare values side by side in Excel.

  • For this, we need to enter the following formula into the C5 cell. In the video shown below.

=IF('[Sales Value in 2021.xlsm]2021 Sales Value'!C5='[Sales Value in 2022.xlsm]2022 Sales Value'!C5, B5& " Sales Unit for both year is same", B5& " Sales Unit for both years do not match")

  • After pressing the Enter button, we can see that the comparison made between the worksheet and the verdict made based on them is presented in cell C5.
  • Then drag the fill handle to cell C16. 
  • After dragging cell C16, we will see that the range of cells C5:C16 will be filled in with the verdict based on the worksheet values.

Final verdict over whether the compared sheets are the same in value or not.


3. Use of Compare and Merge Workbooks Command to Compare Sheets Side by Side in Excel

In this method, we can use the compare and merge method to compare multiple workbooks and then merge them.

  • But first, you need to add the Compare and Merge tool to the Quick Access Toolbar.
  • First, click on the Options button. Then in the Options window, click on the quick access toolbar.
  • Then select All Commands from the Choose Commands form.
  • Then select Compare and Merge Workbooks (Legacy) and then click on the Add button.

Adding Compare and Merge tool from the quick access toolbar

  • After clicking the Add button, we can see that the tool is on the right side of the window.
  • Press OK after this.

Addition of Quick Access Toolbar in the ribbon

  • After that, we need to add another tool named Shared Workbook(Legacy) in the quick access toolbar.
  • Select that tool Shared Workbook(Legacy) and then press Add to add the command on the right side of the workbook.
  • Click OK after this.

Addition of the Share Workbook (Legacy) command

  • We can see the added tools in the Quick Access Toolbar.

Share workbook and Compare and Merge option no in the Quick Access Toolbar

  • Then, click on the Share Workbook(Legacy) command.

Click on the share workbook option

  • After clicking on the Share Workbook tool on the Quick Access Toolbar, users can see that there is a window in which they have to select the user of the computer right now.
  • Click OK after this.

Choose user to share with

  • Repeat the same process for the second file also.
  • Then, in the Quick Access Toolbar, click on the Compare and Merge tool.

Click on the Compare and Merge tool

  • Then a file manager window opens, and from there, choose the second file with which you want to compare the first file.
  • Click Open.

Choosing the file to be compared from the file manager

  • After clicking Open, we can see that the values in the first file that are not matched with the second file are now highlighted and merged.

Final Output showing the differences between the Sale value in 2021 and 2022


4. Using Excel Inquire Add-ins to Compare Sheets Side by Side in Excel

Add-ins are an important part of the Excel ecosystem. With the help of Add-ins, we can carry out complicated operations and tasks. We are going to add the COM Add-ins in this case.

  • First, go to Files Options.

Option button from the files option

  • Then from the options window, go to Add-ins > COM Add-ins > Go to.

Enabling an add-in from the Excel options menu

  • You will see the Inquire check box in the COM Add-ins, check that box and then click on the OK button.

Checking the Inquiry box in the COM-Add-ins box

  • Then in the ribbon menu, click on the Inquire tab, and then click on Compare Files command.

Compare files from the Inquiry tab

  • After that, there will be another box asking which files are going to be compared with which files.
  • In the First row, we already see the parent workbook name in which we are doing this process.
  • Then we will select the second file in the To drop-down box.

Choose which files to compare to in the inquiry menu

  • Then in a spreadsheet window, we can see that both of the spreadsheet’s values are compared side by side, and different values are now highlighted in green
  • In the below sub-window, we can also see the verdict made on them.

Window showing the differed values in both of the sheet side by side

Note: Files to be compared must be kept open while running the Inquire tool.

  • We can also export the comparison in Excel format by clicking on the Export Results

Export the output results in a new sheet

  • There will be a file manager window that will appear, and the user needs to select the desired location of the file.
  • Click Save after this.

Selecting the output location of the export results

  • After selecting Save, we can see that the file is now saved in the desired location.
  • Then, after opening the file, we can see a new worksheet showing the difference in values highlighted in red.

Cells highlighting the difference in the new exported sheet


5. Using VBA Code to Compare Sheets Side by Side in Excel

In addition to the other methods described above, we can get the differences between the values using the VBA methods.

  • For this, you need to open the VBA editor following the article given here.
  • Then in the code editor, paste the code given below,
Sub Comparison_of_Worksheet()
    Dim wrkbk1 As Workbook, wrkbk2 As Workbook, wb3 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim lstRw As Long, i As Long
    Dim cell1 As Range, cell2 As Range
    Set wrkbk1 = Workbooks.Open("E:\Onedrive - Softeko\OneDrive\Softeko\62-0128_Rubayed Razib_excel compare sheets side by side\Sales Value in 2021")
    Set wrkbk2 = Workbooks.Open("E:\Onedrive - Softeko\OneDrive\Softeko\62-0128_Rubayed Razib_excel compare sheets side by side\Sales Value in 2022")
    Set wb3 = ThisWorkbook
    Set ws1 = wrkbk1.Sheets("2021 Sales Value")
    Set ws2 = wrkbk2.Sheets("2022 Sales Value")
    Set ws3 = wb3.Sheets("Comparison")
    lstRw = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    For i = 5 To lstRw
        Set cell1 = ws1.Cells(i, "C")
        Set cell2 = ws2.Cells(i, "C")
        Debug.Print ws1.Cells(i, "C")
        Debug.Print ws2.Cells(i, "C")
        If cell1.Value = cell2.Value Then
            ws3.Cells(i, "C").Value = "Value matched"
        Else
            ws3.Cells(i, "C").Value = "Not matched"
        End If
    Next i
    wrkbk1.Close False
    wrkbk2.Close False
    MsgBox "Comparison completed. Results are listed in the third workbook."
End Sub

VBA code to make a comparison between two worksheets

VBA Code Breakdown

Sub Comparison_of_Worksheet()
  • This line defines the start of the VBA subroutine (macro) named “Comparison_of_Worksheet”.
    Dim wrkbk1 As Workbook, wrkbk2 As Workbook, wb3 As Workbook
  • This line declares three variables: wrkbk1, wrkbk2, and wb3, all of the Workbook types. These variables will represent the workbooks that will be opened and used for comparison.
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
  • This line declares three variables: ws1, ws2, and ws3, all of the Worksheet types. These variables will represent the worksheets within the workbooks that will be used for comparison.
    Dim lstRw As Long, i As Long
  • This line declares two variables: lstRw and i, both of the Long data types. lstRw will store the last row number in the worksheet, and I will be used as a loop counter.
    Dim cell1 As Range, cell2 As Range
  • This line declares two variables: cell1 and cell2, both of the Range data type. These variables will represent specific cells in the worksheets for comparison.
    Set wrkbk1 = Workbooks.Open("E:\Onedrive - Softeko\OneDrive\Softeko\62-0128_Rubayed Razib_excel compare sheets side by side\Sales Value in 2021")
    Set wrkbk2 = Workbooks.Open("E:\Onedrive - Softeko\OneDrive\Softeko\62-0128_Rubayed Razib_excel compare sheets side by side\Sales Value in 2022")
  • These two lines open two workbooks (“Sales Value in 2021” and “Sales Value in 2022”) and assign them to the wrkbk1 and wrkbk2 variables, respectively
    Set wb3 = ThisWorkbook
  • This line assigns the workbook containing the VBA code (the workbook where the macro is stored) to the wb3 variable.
    Set ws1 = wrkbk1.Sheets("2021 Sales Value")
    Set ws2 = wrkbk2.Sheets("2022 Sales Value")
    Set ws3 = wb3.Sheets("Comparison")
  • These three lines assign specific worksheets within the workbooks to the ws1, ws2, and ws3 variables, respectively. These worksheets will be used for comparison and result display.
    lstRw = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
  • This line determines the last row in the ws1 worksheet by finding the last non-empty cell in column B.
    For i = 5 To lstRw
  • This line starts a loop that will iterate from row 5 to the last row in the ws1 worksheet.
        Set cell1 = ws1.Cells(i, "C")
        Set cell2 = ws2.Cells(i, "C")
  • These two lines assign the cells in column C for the current row (i) in ws1 and ws2 to the cell1 and cell2 variables, respectively.
        Debug.Print ws1.Cells(i, "C")
        Debug.Print ws2.Cells(i, "C")
  • These two lines print the values of cell1 and cell2 (for debugging purposes).
        If cell1.Value = cell2.Value Then
            ws3.Cells(i, "C").Value = "Value matched"
        Else
            ws3.Cells(i, "C").Value = "Not matched"
        End If
  • This block of code compares the values of cell1 and cell2. If they are equal, the corresponding cell in the ws3 worksheet is set to “Value matched”. Otherwise, it is set to “Not matched”.
    Next i
  • This line indicates the end of the loop and moves to the next iteration.
    wrkbk1.Close False
    wrkbk2.Close False
  • These two lines close the wrkbk1 and wrkbk2 workbooks without saving any changes.
    MsgBox "Comparison completed. Results are listed in the third workbook."
  • This line displays a message box with the specified message, indicating that the comparison is completed and the results can be found in the third workbook.
End Sub
  • This line marks the end of the VBA subroutine.
  • After pressing the Run command, we can see that the comparison between the worksheet from different workbooks has been made, and the verdict is made in the range of cells C5:C16. 

VBA code to make comparison between two worksheet

Note:  You need to close the file before running the code, otherwise, it can show errors.

Read More: VBA Code to Compare Two Excel Sheets and Copy Differences


Things to Remember

Open/Close both sheets: Open the two Excel sheets that you want to compare. You can open them in separate windows or arrange them in split view within the same Excel window. Or you may need to close both sheets in different cases before starting to compare them.

Arrange windows: If you have opened the sheets in separate windows, arrange the windows side by side on your computer screen. You can do this by manually resizing and positioning the windows or by using the “Arrange All” feature in Excel.

Lock headings: If your sheets have headings or column labels, it can be helpful to lock them in place so that they remain visible while scrolling through the data. To do this, select the row containing the headings, right-click, and choose “Freeze Panes” or “Freeze Top Row” from the context menu.

Scroll together: To compare the data in both sheets, make sure that scrolling is synchronized between the two windows. This way, when you scroll vertically or horizontally on one sheet, the other sheet scrolls accordingly. Some versions of Excel provide a “Synchronous Scrolling” option in the “View” tab or a similar feature to enable this.

Highlight differences: To easily spot differences between the two sheets, you can use conditional formatting. Select the range of cells you want to compare, go to the “Home” tab, click on “Conditional Formatting,” and choose the desired rule to highlight the differences. For example, you can highlight cells that are not equal or cells with unique values.

Use formula-based comparisons: Excel provides various functions that can help you compare data between sheets. Functions like VLOOKUP, INDEXMATCH, or IF can be used to retrieve information from one sheet based on values in another sheet, allowing you to identify matching or mismatched data.

Take notes: If you need to make observations or document the differences you find, consider having a separate sheet or document open where you can take notes while comparing the sheets side by side. This can be helpful for future reference or when discussing the findings with others.


Frequently Asked Questions

1. Can you use VLOOKUP to compare two spreadsheets?

You can use the VLOOKUP function to compare sheets side by side using the following formula stated below,

=VLOOKUP(C5,'[Sales Value in 2022.xlsm]2022 Sales Value'!$C$4:$C$15,1,FALSE)

Comparing values of worksheet side by side using the VLOOKUP function

  • We compared the values in 2021 with the values in 2022. If the match values are found, then the value will stay in place. Otherwise, N/A will be shown in the cell.
  • In this way, you can compare sheets side by side.

2. How to compare two columns in different Excel sheets using if?

You can use the below formula to compare the values of a cell from one parent sheet to another.

=IF(C5='E:\Onedrive - Softeko\OneDrive\Softeko\62-0128_Rubayed Razib_excel compare sheets side by side\[Sales Value in 2022.xlsm]2022 Sales Value'!C4, "Match", "Value Not Matched")
  • Here in the formula, we can see that the user compared cell C5 with another sheet in a separate workbook and show whether they matched or not.

Using the IF function to compare the sheets side-by-side

  • If the cell values matched, the “Match” will be written on the side. Or “ Value Not Matched”.

3. How to compare two columns in different Excel sheets for matches?

With the combination of the IF, ISNUMBER, and MATCH functions, we can compare two columns in different Excel sheets

You can use the below formula to compare the values of a cell from one parent sheet to another.

=IF(ISNUMBER(MATCH(A1, Sheet2!A:A, 0)), A1, "")

Here, the A1 denotes the values in the parent sheet cell value, and this will search for this cell value in column A in Sheet 2. If there are any matched values, then that value will be placed in the A1 cell. If there is no match, the cell will be empty.

Using the IF, ISNUMBER, and MATCH functions to compare sheet side by side

  • After dragging the fill handle to cell D16.
  • After dragging the fill handle to cell D16, we will see that the range of cell D5:D16 is now filled with the verdict whether they are matched with other sheet values or not.

Range of cells showing whether the values are matched or not between two sheets


Download Practice Workbooks

Download the following workbook to practice by yourself.


<< Go Back to Learn Excel | Compare | Worksheets


Conclusion

In this article, we have shown how, in Excel, we can compare multiple sheets side by side. The methods included VBA macro, Side by Side view window, Compare and Merge, using the Inquire tool, etc. Which method a user wants to use completely depends on the user’s choice and their own problem statement. Feel free to comment anything, we will reply to that as early as possible.


Related Article

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo