Excel How to Compare Two Worksheets: A Comprehensive Guide

In the realm of data analysis and management, Microsoft Excel stands as a ubiquitous tool. Often, users find themselves working with multiple Excel worksheets, perhaps different versions of a report, datasets from different periods, or information spread across several files. The need to quickly and accurately identify differences between these worksheets becomes paramount for data integrity, error detection, and informed decision-making. This guide will delve into various techniques on how to compare two Excel worksheets, ranging from built-in Excel features to specialized third-party tools, ensuring you can choose the most efficient method for your specific needs.

Whether you are auditing financial data, tracking project changes, or simply ensuring consistency across your spreadsheets, understanding how to effectively compare Excel worksheets is an invaluable skill. Let’s explore the methods available to you, from simple visual checks to advanced automated comparisons.

I. Visual Comparison: Side by Side Viewing in Excel

For a quick, initial comparison, especially with smaller datasets, Excel’s built-in “View Side by Side” mode offers a straightforward solution. This method allows you to visually scan two worksheets simultaneously, spotting discrepancies with the naked eye. It’s particularly useful for identifying high-level changes or when you have a good memory of the data and are looking for specific alterations.

1. Comparing Two Excel Workbooks Side by Side

Imagine you have monthly sales reports in two separate Excel files and want to quickly see month-over-month performance. Viewing them side by side is an effective way to achieve this.

Steps:

1. Open both Excel workbooks you wish to compare.

2. Navigate to the View tab on the Excel ribbon.

3. In the Window group, locate and click the View Side by Side button.

   By default, Excel will arrange the workbooks horizontally. To switch to a vertical arrangement, click the Arrange All button in the same Window group and select Vertical.

4. Synchronous Scrolling: To scroll through both worksheets at the same time, enabling row-by-row comparison, ensure that Synchronous Scrolling is activated. This option is usually enabled automatically when you activate “View Side by Side” and is located right under the “View Side by Side” button on the View tab.

2. Viewing Multiple Excel Windows Simultaneously

If you need to compare more than two Excel files, Excel allows you to arrange multiple windows side by side.

Steps:

1. Open all the Excel workbooks you need to compare.

2. Click the View Side by Side button. Excel will typically compare the currently active workbook with the previously active one.

3. To manage which workbooks are displayed side-by-side, the Compare Side by Side dialog box will appear. Select the workbooks you want to display alongside the active one.

4. Alternatively, to view all open Excel files at once, click Arrange All in the View tab, Window group. Choose from options like Tiled, Horizontal, Vertical, or Cascade to arrange all open workbooks on your screen.

3. Comparing Two Sheets Within the Same Workbook

Sometimes, the worksheets you need to compare are within the same Excel workbook. “New Window” feature can help view these sheets side by side.

Steps:

1. Open the Excel workbook containing the two sheets you want to compare.
2. Go to the View tab > Window group and click New Window. This opens a second window displaying the same workbook.
3. Click View Side by Side in either of the new windows.
4. In each window, navigate to the respective sheet you want to compare. For example, select “Sheet1” in one window and “Sheet2” in the other.

II. Formula-Based Worksheet Comparison for Value Differences

For a more detailed comparison that goes beyond visual inspection, you can use Excel formulas to create a difference report. This method is excellent for identifying cells with different values and generating a summary of discrepancies directly within Excel.

Steps:

1. Open a new, blank worksheet in your Excel workbook. This sheet will serve as your difference report.

2. In cell A1 of this new sheet, enter the following formula:

   =IF(Sheet1!A1<>Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

   Explanation of the formula:

   • Sheet1!A1 and Sheet2!A1 refer to cell A1 in “Sheet1” and “Sheet2” respectively. Adjust “Sheet1” and “Sheet2” to the actual names of your worksheets.
   • <> is the “not equal to” operator. The formula checks if the value in Sheet1!A1 is different from Sheet2!A1.
   • IF(condition, value_if_true, value_if_false) is the core function.
   • If the values are different (condition is true), it displays a text string indicating the values from both sheets: "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1.
   • If the values are the same (condition is false), it displays an empty string "", leaving the cell blank.

3. Copy the formula down and to the right. Use the fill handle (the small square at the bottom-right of the selected cell) to drag the formula across the range of cells you need to compare. This will automatically adjust the cell references (A1, B1, C1, A2, etc.) due to relative cell references.

4. Review the difference report. The new worksheet will now show cells with differences. Cells that are blank indicate identical values in the corresponding cells of “Sheet1” and “Sheet2”. Cells with text indicate differences and display the values from both sheets for easy comparison.

Limitations of Formula-Based Comparison:

• Value Comparison Only: This method only compares cell values. It does not compare formulas, formatting, or comments.
• Row/Column Sensitivity: If rows or columns are added or deleted in one sheet but not the other, the comparison will become misaligned after the point of insertion/deletion, marking subsequent rows/columns incorrectly as different.
• Sheet-Level Focus: It operates at the sheet level and cannot detect workbook-level structural changes like added or deleted worksheets.

III. Conditional Formatting to Highlight Differences

Another effective built-in Excel feature for highlighting differences is Conditional Formatting. This method allows you to visually emphasize cells that differ between two worksheets by applying a specific format, such as color fill, to the differing cells.

Steps:

1. Select the data range in the worksheet where you want to highlight the differences. Start by clicking the top-left cell of your data range (usually A1) and press Ctrl + Shift + End to select all used cells in the sheet.

2. Go to the Home tab on the ribbon.

3. In the Styles group, click Conditional Formatting > New Rule.

4. In the “New Formatting Rule” dialog box, select “Use a formula to determine which cells to format”.

5. In the “Format values where this formula is true” box, enter the following formula:

   =A1<>Sheet2!A1

   Replace “Sheet2” with the name of the worksheet you are comparing against. A1 refers to the top-left cell of your selected range in the current sheet.

6. Click the Format button to choose the formatting style for the differing cells (e.g., fill color, font color, etc.).

7. Click OK in both the “Format Cells” and “New Formatting Rule” dialog boxes.

Now, in your selected worksheet, any cell whose value differs from the corresponding cell in “Sheet2” will be highlighted with the format you selected.

Limitations of Conditional Formatting Comparison:

Similar to formula-based comparison, conditional formatting:

• Primarily compares values: It doesn’t inherently compare formulas, formatting, or structural elements.
• Sensitive to structural changes: Added or deleted rows/columns can disrupt the comparison alignment.
• Sheet-level operation: It doesn’t address workbook-level differences.

IV. Compare and Merge for Shared Workbooks

Excel’s “Compare and Merge Workbooks” feature is designed specifically for scenarios where multiple users collaborate on the same Excel workbook. It allows you to combine changes made in different copies of a shared workbook. This is particularly useful in collaborative environments where version control is important.

Prerequisites for Using “Compare and Merge”:

1. Share the Workbook: Before distributing the workbook for collaboration, it must be shared. To do this:
   • Go to the Review tab > Changes group.
   • Click Share Workbook (in older versions of Excel, or Share Workbook (Legacy) in newer versions if the button is added to the ribbon).
   • Check the box “Allow changes by more than one user at the same time…” and click OK. If prompted, allow Excel to save the workbook. Note: Modern Excel and collaboration via OneDrive/SharePoint offer superior co-authoring capabilities, making “Share Workbook (Legacy)” less relevant for real-time collaboration but still applicable for merging versions.
2. Copies with Unique Names: Each user working on the shared workbook must save their copy with a unique file name.

Steps to Compare and Merge Workbooks:

1. Enable “Compare and Merge Workbooks” Command: This command is not readily available in the Excel ribbon by default. You need to add it to the Quick Access Toolbar:

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

2. Perform the Merge:

   • Open the original, shared workbook (the primary version).
   • Click the Compare and Merge Workbooks command from your Quick Access Toolbar.
   • In the dialog box, select the copies of the shared workbook that you want to merge into the primary version. You can select multiple copies by holding down the Shift key while clicking file names. Click OK.

3. Review Tracked Changes: After merging, to see the changes made by different users:

   • Go to the Review tab > Changes group.
   • Click Track Changes > Highlight Changes.
   • In the “Highlight Changes” dialog, configure the settings as needed (e.g., “When”: All, “Who”: Everyone, clear “Where” to apply to the entire sheet, check “Highlight changes on screen”). Click OK.

   Excel will highlight rows and columns with changes in red and mark individual cell edits with different colors based on the user who made the change. Hovering over a changed cell will show details of the edit.

Limitations of “Compare and Merge”:

• Shared Workbook Requirement: It only works with workbooks that have been explicitly shared using the legacy “Share Workbook” feature. It’s not designed for comparing arbitrary Excel files.
• Limited Scope of Comparison: Focuses on merging changes from collaborative editing, not a comprehensive comparison of all possible differences.
• Legacy Feature: With the rise of real-time co-authoring in modern Excel via cloud services, “Share Workbook (Legacy)” and “Compare and Merge” are less frequently used for collaboration but can still be relevant for merging offline versions of shared workbooks.

V. Third-Party Excel Comparison Tools

While Excel’s built-in features offer basic comparison capabilities, they may fall short when you need a more robust and comprehensive comparison, especially for complex worksheets, formulas, formatting, and structural differences. This is where specialized third-party tools excel. These tools are designed specifically for comparing, merging, and synchronizing Excel files and worksheets, offering advanced features and greater efficiency.

Here are some prominent third-party tools for comparing Excel worksheets:

1. Synkronizer Excel Compare

Synkronizer Excel Compare is an Excel add-in that offers a comprehensive suite of features for comparing, merging, and updating Excel files. It goes beyond value comparison, delving into formulas, formatting, comments, and even structural changes.

Key Features of Synkronizer Excel Compare:

• Detailed Difference Identification: Identifies differences in values, formulas, formatting, comments, and names.
• Merge and Update Capabilities: Allows merging of differences from one sheet to another, updating your primary worksheet quickly.
• Difference Reporting: Generates detailed, easy-to-read difference reports, including summary and cell-level reports.
• Visual Difference Highlighting: Highlights differences directly in the worksheets with color-coding for different types of changes.
• Flexible Comparison Options: Offers different comparison modes, including “normal worksheets,” “with link options,” “as database,” and “selected ranges.”
• Sheet and Workbook Comparison: Compares individual worksheets or all sheets within two workbooks simultaneously.

Example Use Case – Comparing Event Participant Lists:

Imagine you are organizing an event and have two versions of a participant list in Excel, perhaps updated by different team members. Synkronizer Excel Compare can efficiently identify all discrepancies between these lists.

Steps to Compare with Synkronizer:

1. Install and Run Synkronizer: Install the Synkronizer Excel Compare add-in and find it in the Add-ins tab in Excel.

2. Select Workbooks and Sheets: In the Synkronizer pane, select the two Excel workbooks and the specific worksheets you want to compare. Synkronizer can automatically match sheets with the same names.

3. Choose Comparison Options: Select the comparison type (e.g., “Compare as normal worksheets”) and choose what content types to compare (values, formulas, formats, comments, etc.).

4. Start Comparison: Click the “Start” button. Synkronizer will compare the sheets and display the results.

5. Review Difference Reports: Examine the summary report for an overview of difference types and the detailed difference report for cell-level discrepancies.

6. Visualize Differences: Click on a difference in the report to jump to the corresponding cells in both worksheets. Differences are also highlighted directly in the sheets.

7. Merge Differences (Optional): Select differences in the Synkronizer pane and use the update buttons to merge changes from one sheet to the other, updating your worksheets based on the comparison results.

2. Ablebits Compare Sheets for Excel (Part of Ultimate Suite)

Ablebits Compare Sheets for Excel, part of their Ultimate Suite for Excel, is another powerful tool designed for intuitive and user-friendly worksheet comparison. It emphasizes a step-by-step wizard approach and a “Review Differences” mode for efficient difference management.

Key Features of Ablebits Compare Sheets:

• Step-by-Step Wizard: Guides you through the comparison process, simplifying configuration.
• Multiple Comparison Algorithms: Offers different algorithms (“No key columns,” “By key columns,” “Cell-by-cell”) optimized for various data structures.
• Review Differences Mode: Presents compared sheets side-by-side in a dedicated mode for easy review and management of differences.
• Difference Highlighting: Highlights different types of changes with distinct colors (e.g., added rows, removed rows, changed cells).
• Merge and Ignore Options: Allows you to merge or ignore differences directly within the “Review Differences” mode.
• Backup Creation: Automatically creates backup copies of your worksheets before comparison, ensuring data safety.

Example Use Case – Comparing Sales Data Reports:

Suppose you have two versions of a monthly sales report and need to identify discrepancies quickly and decide which data to keep. Ablebits Compare Sheets can streamline this process.

Steps to Compare with Ablebits Compare Sheets:

1. Install and Run Ablebits Ultimate Suite: Install the Ultimate Suite and find the “Compare Sheets” feature in the “Ablebits Data” tab.

2. Start the Wizard: Click the “Compare Sheets” button to launch the step-by-step wizard.

3. Select Worksheets: Choose the two worksheets you want to compare. You can select entire sheets, current tables, or specific ranges.

   Selecting a table for comparison in Ablebits Compare Sheets.

4. Choose Comparison Algorithm and Match Type: Select an appropriate comparison algorithm (e.g., “No key columns” for general sheet comparison) and match type (e.g., “Best match”).

5. Specify Differences to Highlight: Choose which differences to highlight (values, formulas, formatting) and whether to ignore certain elements like hidden rows/columns.

6. Compare: Click the “Compare” button. Ablebits Compare Sheets will process the worksheets and open them in “Review Differences” mode.

7. Review and Manage Differences: In “Review Differences” mode, the sheets are displayed side-by-side with differences highlighted. Use the toolbars to navigate through differences, merge changes, or ignore them.

8. Exit Review Differences Mode: Once you’ve reviewed all differences, exit “Review Differences” mode, saving changes or restoring backups as needed.

3. xlCompare

xlCompare is a dedicated utility focused on comparing and merging Excel workbooks, worksheets, names, and VBA projects. It provides a range of features for identifying and resolving differences, including duplicate record detection and data synchronization tools.

Key Features of xlCompare:

• Comprehensive Comparison: Compares workbooks, worksheets, names, and VBA projects.
• Duplicate Record Detection: Finds and removes duplicate records between worksheets.
• Data Synchronization: Updates records in one sheet with values from another and adds unique rows/columns.
• Merge Capabilities: Merges updated records from one workbook to another.
• Filtering and Sorting: Filters comparison results to show only differences or identical records and sorts data by key columns.
• Difference Highlighting: Highlights comparison results with colors.

4. Change pro for Excel

Change pro for Excel is designed for comparing Excel sheets on both desktop and mobile devices, with optional server-based comparison. It focuses on identifying changes in formulas, values, and layout, and offers features for reporting and collaboration.

Key Features of Change pro for Excel:

• Formula and Value Comparison: Finds differences in both formulas and values.
• Layout Change Detection: Identifies added/deleted rows and columns.
• Embedded Object Recognition: Recognizes changes in charts, graphs, and images.
• Difference Reporting: Creates and prints difference reports for formulas, values, and layout.
• Filtering and Sorting: Filters and sorts difference reports to focus on key changes.
• Integration: Compares files directly from Outlook and document management systems.
• Multi-language Support: Supports all languages, including multi-byte character sets.

VI. Online Excel Comparison Services

For quick, occasional comparisons, especially of non-sensitive data, online Excel comparison services offer a convenient alternative to desktop software. These services typically require you to upload your Excel files to their website and then perform the comparison, highlighting differences directly in your browser.

Example Online Service: XLComparator and CloudyExcel

Services like XLComparator and CloudyExcel allow you to upload two Excel workbooks and quickly identify differences between them.

Steps for Using Online Services:

1. Access the Website: Go to the website of the online comparison service (e.g., XLComparator or CloudyExcel).

2. Upload Files: Upload the two Excel workbooks you want to compare.

3. Initiate Comparison: Click a “Compare” or “Find Difference” button on the website.

4. Review Results: The service will process the files and display the worksheets with differences highlighted, usually with color-coding.

Considerations for Online Services:

• Security: Be cautious when uploading sensitive or confidential data to online services, as data security practices may vary.
• Functionality: Online services typically offer basic value comparison and highlighting but may lack the advanced features of desktop tools, such as formula comparison, formatting comparison, and merge capabilities.
• File Size Limits: Some online services may have limitations on the size of Excel files you can upload.

Conclusion

Comparing two Excel worksheets is a common task with various approaches available, ranging from simple visual checks to sophisticated software solutions. The best method for you depends on the complexity of your data, the level of detail required in the comparison, and your comfort level with different tools.

• Visual Side-by-Side Viewing: Quick and easy for initial checks and smaller datasets.
• Formula-Based Comparison: Good for identifying value differences and creating difference reports within Excel.
• Conditional Formatting: Visually highlights value differences directly in the worksheets.
• Compare and Merge (for Shared Workbooks): Designed for merging changes from collaborative editing of legacy shared workbooks.
• Third-Party Tools (e.g., Synkronizer, Ablebits Compare Sheets, xlCompare, Change pro): Offer comprehensive comparison capabilities, including formulas, formatting, structural changes, and merging features. Ideal for complex comparisons and professional use.
• Online Services: Convenient for quick, basic comparisons of non-sensitive data.

By understanding these different methods, you can effectively choose the most appropriate technique for your specific needs and efficiently manage and ensure the integrity of your Excel data.