How to Compare 2 Worksheets in Excel: Different Methods to Identify Differences

When working with Excel, you may often find yourself dealing with multiple worksheets containing similar data. Whether you’re managing financial records, sales reports, or project data, comparing two worksheets to identify differences is a common and crucial task. This process helps ensure data accuracy, identify discrepancies, and track changes effectively. This guide will explore various methods to compare two worksheets in Excel, ranging from simple visual checks to more advanced techniques, helping you choose the best approach for your needs.

Visually Comparing Two Excel Worksheets Side by Side

For a quick and straightforward comparison, especially when dealing with smaller datasets, Excel’s “View Side by Side” feature offers an efficient visual method. This allows you to display two worksheets simultaneously, making it easy to spot differences with your own eyes. This method is useful for comparing both separate workbooks or two sheets within the same workbook.

Comparing Two Excel Workbooks Side by Side

Imagine you have two monthly budget spreadsheets and want to quickly compare spending patterns. Viewing them side-by-side allows for immediate visual analysis.

Here’s how to set up a side-by-side view for two Excel workbooks:

Open both Excel workbooks you intend to compare.
Navigate to the View tab on the Excel ribbon.
In the Window group, click the View Side by Side button.

Excel will automatically arrange the two workbook windows horizontally by default.

If you prefer a vertical arrangement, follow these steps:

After activating “View Side by Side”, in the Window group on the View tab, click Arrange All.
In the Arrange Windows dialog box, select Vertical, and then click OK.

With the windows side by side, you can enhance the comparison by enabling Synchronous Scrolling. This feature links the scrolling of both worksheets, allowing you to scroll through them simultaneously and compare corresponding rows easily.

To activate Synchronous Scrolling:

Ensure “View Side by Side” is enabled. The Synchronous Scrolling button, located directly under the “View Side by Side” button in the Window group of the View tab, is typically activated automatically. Verify that it is toggled on.

For a more detailed guide on this feature, refer to resources on viewing Excel workbooks side by side.

Viewing Multiple Excel Windows Side by Side

Excel also allows you to compare more than two workbooks at once. After opening all the desired workbooks, clicking “View Side by Side” will open the “Compare Side by Side” dialog box. Here, you can select which workbooks to display alongside the currently active one.

To arrange all currently open Excel workbooks, use the Arrange All button in the Window group of the View tab. This offers arrangements like tiled, horizontal, vertical, or cascade, allowing you to view all open workbooks simultaneously.

Comparing Two Worksheets Within the Same Workbook

Often, the worksheets you need to compare are within the same Excel workbook. To view two sheets from the same workbook side by side, follow these steps:

Open the Excel file.
Go to the View tab and in the Window group, click New Window. This opens a new window displaying the same workbook.
Click the View Side by Side button in either of the workbook windows to activate the side-by-side mode.
In each window, select the specific worksheet you want to compare.

Using Formulas to Create a Difference Report

For a more analytical approach, you can use Excel formulas to automatically generate a difference report. This method is particularly useful for identifying cells with different values and creating a summary of these discrepancies in a new worksheet.

To compare two worksheets and create a difference report:

Open a new, blank worksheet in your Excel workbook.
In cell A1 of the new sheet, enter the following formula:
```
=IF(Sheet1!A1<>Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
```
Replace “Sheet1” and “Sheet2” with the actual names of your worksheets.
Copy this formula down and across the new worksheet to cover the range of data you need to compare in both original sheets. You can do this efficiently by dragging the fill handle (the small square at the bottom-right of the selected cell).

This formula compares corresponding cells in “Sheet1” and “Sheet2”. If the values are different, it displays a text string indicating the values from each sheet. If the values are the same, it leaves the cell blank.

Important Note: Dates in the difference report might be displayed as serial numbers, which is Excel’s internal representation of dates. This can make direct date comparison slightly less intuitive in the report.

Highlighting Differences with Conditional Formatting

Conditional formatting provides a visual way to highlight cells that differ between two worksheets directly within one of the worksheets being compared. This method is excellent for quickly spotting discrepancies within the data itself.

Here’s how to use conditional formatting to highlight differences:

Select the range of cells you want to compare in the worksheet where you want the highlighting to appear. Typically, you would select the entire used range by clicking on the top-left cell (usually A1) and pressing Ctrl + Shift + End.
Go to the Home tab on the ribbon.
In the Styles group, click Conditional Formatting and then select New Rule.
In the “New Formatting Rule” dialog box, choose “Use a formula to determine which cells to format”.
Enter the following formula in the “Format values where this formula is true” box:
```
=A1<>Sheet2!A1
```
Again, replace “Sheet2” with the name of the worksheet you are comparing against.
Click the Format button to choose the formatting style (e.g., fill color, font color) to apply to the differing cells.
Click OK in both dialog boxes to apply the conditional formatting rule.

Cells with values different from their corresponding cells in “Sheet2” will now be highlighted according to the format you selected.

For more detailed instructions on conditional formatting, you can refer to tutorials on Excel conditional formatting based on another cell value.

Limitations of Formula and Conditional Formatting Methods:

While formulas and conditional formatting are useful, they have limitations for comprehensive worksheet comparison:

Value Comparison Only: They primarily compare cell values and do not easily identify differences in formulas or cell formatting.
Row/Column Sensitivity: Adding or deleting rows or columns in one sheet can skew the comparison, as subsequent rows and columns will be misaligned and incorrectly flagged as different.
Sheet-Level Focus: These methods are designed for worksheet-level comparisons and do not detect workbook-level structural differences like added or deleted sheets.

Compare and Merge Shared Workbooks for Collaborative Changes

When dealing with shared workbooks where multiple users might be making changes, Excel’s “Compare and Merge” feature becomes invaluable. This feature is specifically designed to consolidate changes from different copies of a shared workbook, showing you edits from all users in one place.

Preparation for Using “Compare and Merge”:

Share the Workbook: Before distributing the workbook for collaboration, it must be shared. To do this, go to the Review tab, in the Changes group, click Share Workbook (Legacy). Check the box labeled “Allow changes by more than one user at the same time. This also allows workbook merging.” and click OK. If prompted, allow Excel to save the workbook. (Note: The “Share Workbook (Legacy)” feature is being phased out by Microsoft, consider using modern collaboration features like OneDrive and Excel co-authoring for newer workbooks if possible).
Save Copies: Each user working on the shared workbook must save their own copy using a unique filename (.xls or .xlsx format).

Steps to Compare and Merge Workbooks:

1. Enable the “Compare and Merge Workbooks” Command

This command is not readily visible in Excel by default. You need to add it to the Quick Access Toolbar:

Click the Customize Quick Access Toolbar dropdown arrow (located at the very top left of the Excel window).
Select More Commands.
In the “Excel Options” dialog box, choose All Commands from the “Choose commands from” dropdown menu.
Scroll down and select Compare and Merge Workbooks.
Click Add to move it to the right-hand section, and then click OK.

2. Perform the Compare and Merge Operation

Open the original, shared workbook (the primary version).
Click the Compare and Merge Workbooks command on the Quick Access Toolbar.
In the dialog box, select the copies of the shared workbook you wish to merge. You can select multiple copies by holding down the Shift key while clicking filenames. Click OK.

Excel will merge the changes from the selected copies into the primary workbook.

3. Review the Merged Changes

To review all changes made by different users:

Go to the Review tab, in the Changes group, and click Track Changes (Legacy), then Highlight Changes.
In the “Highlight Changes” dialog box, set the “When” dropdown to All, “Who” to Everyone, clear the “Where” box, ensure “Highlight changes on screen” is checked, and click OK.

Excel will highlight changed cells with different colors for each user and mark changed rows and columns with red borders. Hovering over a changed cell will display information about who made the change.

Important Note: The “Compare and Merge Workbooks” feature only works for merging copies of a shared workbook. It cannot be used to merge changes from independent Excel files. If the command is greyed out, ensure you are working with a properly shared workbook and its copies.

Third-Party Tools for Advanced Excel Comparison

While Excel’s built-in features are useful, they might not suffice for complex comparisons requiring detailed analysis of values, formulas, formatting, and structural changes. Third-party tools are available that offer more advanced functionalities for comparing, updating, and merging Excel files.

Synkronizer Excel Compare: A Comprehensive Comparison and Merge Tool

Synkronizer Excel Compare is an add-in designed for in-depth Excel file comparison, merging, and updating. It provides features beyond Excel’s native capabilities, making it a powerful option for users needing detailed comparisons.

Key features of Synkronizer Excel Compare include:

Detailed Difference Identification: Detects differences in values, formulas, cell formats, comments, and names.
Merge and Update Capabilities: Allows selective merging of differences from one worksheet to another.
Difference Reporting: Generates comprehensive and easy-to-read reports detailing all identified differences.
Customizable Comparison: Offers various comparison options, including comparing as normal worksheets, databases, or specific ranges.
Visual Difference Highlighting: Highlights differences directly in the worksheets for easy visual review.

Example Use Case: Consider managing event participant data in Excel, where multiple managers update separate copies of the same participant list. Synkronizer can efficiently compare these versions and highlight all discrepancies.

Synkronizer provides both a summary report and a detailed difference report, making it easy to understand the types and locations of all changes.

Synkronizer Excel Compare offers a trial version for users to test its capabilities.

Ablebits Compare Sheets for Excel: User-Friendly Worksheet Comparison

Ablebits Compare Sheets for Excel is another tool designed for comparing worksheets, integrated into their Ultimate Suite for Excel add-in. It focuses on user-friendliness and intuitive workflow.

Key aspects of Ablebits Compare Sheets include:

Step-by-Step Wizard: Guides users through the comparison process, simplifying configuration.
Comparison Algorithms: Offers different algorithms tailored to various data structures (no key columns, by key columns, cell-by-cell).
Review Differences Mode: Displays compared sheets side-by-side in a special mode for easy review and management of differences.
Difference Highlighting and Navigation: Visually highlights differences and provides tools to navigate through them and decide on actions (merge or ignore).

Alt text: Ablebits Compare Sheets wizard step showing options to select comparison range as current table in Excel.

Ablebits Compare Sheets also offers a trial version for users to evaluate its features.

xlCompare and Change Pro for Excel: Additional Comparison Tools

Other third-party tools like xlCompare and Change pro for Excel also offer robust Excel comparison capabilities, including features for comparing VBA projects, handling layout changes, and creating detailed difference reports. Change pro for Excel also extends its capabilities to mobile devices and server-based comparisons.

Online Excel Comparison Services

For quick, one-off comparisons without software installation, online services offer a convenient option. These services allow you to upload two Excel files and compare them directly in your web browser.

XLComparator and CloudyExcel are examples of online services that provide basic Excel sheet comparison. These tools are useful for non-sensitive data comparisons where immediate results are needed.

Note on Security: Be cautious when using online services for sensitive data, as uploading files to external servers may pose security risks.

Conclusion

Comparing two worksheets in Excel can be accomplished through various methods, each suited to different needs and levels of complexity. From simple visual side-by-side comparisons to formula-based difference reports and conditional formatting, Excel provides basic tools for identifying discrepancies. For more advanced and comprehensive comparisons, especially when dealing with large datasets, complex formulas, or structural changes, third-party tools like Synkronizer Excel Compare and Ablebits Compare Sheets offer powerful solutions. Online services provide a quick and accessible option for basic comparisons.

Choose the method that best fits your specific requirements and data sensitivity to efficiently compare your Excel worksheets and maintain data accuracy.