How to Compare Two Sheets in Excel: Find Key Differences

When working with data in Excel, you’ll often find yourself needing to compare two spreadsheets. Whether you’re auditing financial records, tracking changes in project data, or merging information from different sources, knowing how to effectively compare two Excel sheets is a crucial skill. This guide will walk you through various methods to compare two sheets in Excel, from simple visual checks to advanced techniques that highlight even the smallest discrepancies.

Understanding how to compare sheets in Excel is essential for maintaining data integrity and accuracy. By mastering these techniques, you can quickly identify differences, ensuring your spreadsheets are consistent and reliable. Let’s explore the most effective ways to compare your Excel sheets and pinpoint exactly what has changed.

Side-by-Side Viewing: A Simple Visual Comparison of Excel Sheets

For a quick and straightforward comparison, especially when dealing with smaller datasets or needing a general overview, viewing Excel sheets side by side is an excellent starting point. This method allows you to visually scan two sheets simultaneously and spot obvious differences with your own eyes. Excel offers a built-in feature to make this process seamless.

Comparing Two Excel Workbooks Side by Side

Imagine you have two versions of a sales report, perhaps one from this month and another from the last. You want to quickly see how sales figures have changed. Viewing these workbooks side by side allows for an immediate visual comparison. Here’s how to set it up:

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

Excel will automatically arrange the two open workbooks to be displayed next to each other. By default, they are often arranged horizontally.

If you prefer a vertical arrangement, which is often more convenient for comparing data columns, you can easily change the layout:

With both workbooks in side-by-side view, click the Arrange All button, also located in the Window group of the View tab.
In the Arrange Windows dialog box, select the Vertical option.
Click OK.

To enhance the comparison further, Excel offers Synchronous Scrolling. When enabled, scrolling in one sheet will automatically scroll the other sheet in tandem. This is incredibly helpful for comparing data row by row, ensuring you’re always looking at corresponding sections of both sheets. Synchronous Scrolling is usually activated automatically when you enable View Side by Side. You can verify or toggle this feature in the Window group under the View Side by Side button.

For a more in-depth look at this feature, Microsoft Support provides detailed information on viewing workbooks side by side.

Comparing Multiple Excel Windows Simultaneously

Sometimes, you might need to compare more than just two Excel files. Excel’s View Side by Side feature can also handle multiple workbooks. After opening all the workbooks, clicking View Side by Side will trigger a dialog box. This dialog lets you choose which workbooks to display along with the currently active workbook.

Alternatively, to arrange all open Excel workbooks, you can use the Arrange All button directly. Clicking Arrange All and selecting your preferred layout (Tiled, Horizontal, Vertical, or Cascade) will organize all your open Excel windows according to your chosen arrangement.

Comparing Two Sheets Within the Same Excel Workbook

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

Open the Excel workbook containing both sheets.
Go to the View tab and in the Window group, click New Window. This action opens a new window displaying the same Excel file.
Click the View Side by Side button in either of the new windows.
In each window, navigate to the specific sheet you want to compare. Select Sheet 1 in one window and Sheet 2 in the other.

Now you have two windows showing different sheets from the same workbook, perfectly positioned for side-by-side comparison.

Formula-Based Comparison: Creating a Difference Report in Excel

While visual comparison is useful for spotting general trends, for detailed and precise identification of differences in values, using Excel formulas is a more robust approach. This method allows you to generate a “difference report,” a new sheet that explicitly lists the discrepancies between two other sheets.

Here’s how to create a formula-based difference report:

Open your Excel workbook and navigate to or insert a new blank worksheet. This new sheet will serve as your difference report.
In cell A1 of the new sheet, enter the following formula:
```
=IF(Sheet1!A1<>Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
```
Explanation of the Formula:
- =IF(Sheet1!A1<>Sheet2!A1, ... , ...): This is an IF function that checks if the value in cell A1 of Sheet1 is not equal to (<>) the value in cell A1 of Sheet2.
- "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1": If the condition is TRUE (values are different), this part of the formula constructs a text string that displays the values from both sheets for easy comparison.
- "": If the condition is FALSE (values are the same), the formula returns an empty string, leaving the cell blank in the difference report, indicating no difference.
Copy the Formula: Drag the fill handle (the small square at the bottom-right corner of the selected cell A1) across and down to cover the entire range of data you want to compare in both sheets. This will automatically apply the formula to all corresponding cells.

After copying the formula, your difference report sheet will populate. Cells will remain blank where the values in the corresponding cells of Sheet1 and Sheet2 are identical. Where values differ, the difference report will display a text string indicating the values from each sheet, highlighting the discrepancies.

Important Note: Dates in the difference report might appear as serial numbers instead of formatted dates. This is because Excel stores dates as serial numbers internally. While this representation might not be immediately user-friendly, it’s a direct representation of the underlying data and confirms differences even in date values.

This formula-based method is effective for comparing values but has limitations. It doesn’t compare formulas or formatting and can become misaligned if rows or columns have been added or deleted in one sheet but not the other.

Conditional Formatting: Visually Highlight Differences in Excel Sheets

For a more visually intuitive way to identify differences directly within your worksheet, Excel’s conditional formatting feature is incredibly useful. You can set up rules that automatically highlight cells that differ between two sheets, making discrepancies instantly visible.

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

Select the Range: In the worksheet where you want to highlight the differences, select the entire range of cells you wish to compare. Start by clicking the top-left cell of your data range (usually A1) and press Ctrl + Shift + End to select all used cells in your sheet.
Open Conditional Formatting: On the Home tab, in the Styles group, click on Conditional Formatting, and then select New Rule….
Create a New Rule: In the New Formatting Rule dialog box, choose “Use a formula to determine which cells to format”.
Enter the Formula: In the “Format values where this formula is true” box, enter the following formula:
```
=A1<>Sheet2!A1
```
Make sure to replace Sheet2 with the actual name of the sheet you are comparing against.
Choose Formatting: Click the Format… button. In the Format Cells dialog, go to the Fill tab and select a highlight color (e.g., yellow or red) to visually mark the different cells. You can also adjust font, border, or number formatting if needed. Click OK to close the Format Cells dialog, and then OK again to close the New Formatting Rule dialog.

Now, in your selected worksheet, any cell that has a different value from the corresponding cell in Sheet2 will be highlighted with the color you chose. This visual highlighting makes it very easy to quickly scan through your data and pinpoint exactly where discrepancies lie.

For a more detailed guide on conditional formatting based on other cell values, refer to Microsoft’s documentation on using formulas in conditional formatting.

Limitations of Formulas and Conditional Formatting:

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

Value Comparison Only: They primarily compare cell values. They do not readily compare formulas themselves or cell formatting.
Row/Column Sensitivity: They are sensitive to the structure of the sheets. If rows or columns are inserted or deleted in one sheet but not the other, the cell-by-cell comparison will become misaligned for subsequent rows/columns, leading to inaccurate difference detection.
Sheet-Level Focus: These methods operate at the sheet level and cannot detect workbook-level differences, such as added, deleted, or renamed sheets.

For more complex comparison needs, especially when dealing with structural differences or needing to compare formulas and formatting, more advanced tools and methods are required.

Compare and Merge Shared Workbooks: Tracking Changes Collaboratively

Excel’s “Compare and Merge Workbooks” feature is designed specifically for scenarios where multiple users are collaborating on the same Excel workbook. This feature is particularly useful for consolidating changes from different versions of a shared workbook, allowing you to review and integrate modifications made by various contributors.

To use the “Compare and Merge” feature effectively, you need to prepare your workbook for sharing beforehand:

Share the Workbook: Before distributing the workbook to collaborators, you must enable sharing. Go to the Review tab, in the Changes group, click the Share Workbook button (in newer versions of Excel, this might be Share Workbook (Legacy)).
Enable Multi-User Editing: In the Share Workbook dialog box, check the box labeled “Allow changes by more than one user at the same time. This also allows workbook merging.” and click OK. Excel may prompt you to save the workbook if it hasn’t been saved already.
Save Copies: Each user who needs to make edits should save a copy of this shared workbook using a unique file name. It’s crucial that everyone works on copies, not the original shared file directly, for the merge process to work correctly.

Once everyone has made their edits and saved their copies, you can merge these copies back into the primary version of the shared workbook.

Enabling the “Compare and Merge Workbooks” Command

The “Compare and Merge Workbooks” command is not visible in Excel’s ribbon by default. You need to add it to your 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, under Choose commands from, select All Commands.
Scroll down the list and find Compare and Merge Workbooks. Select it and click the Add>> button to move it to the right-hand side, under the Quick Access Toolbar customizations.
Click OK to close the Excel Options dialog.

Comparing and Merging Workbook Copies

With the “Compare and Merge Workbooks” command added and copies of the shared workbook collected from collaborators, you can now proceed with merging:

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

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

Reviewing Merged Changes

After merging, to review all the changes made by different users:

Go to the Review tab, Changes group, and click Track Changes > Highlight Changes….
In the Highlight Changes dialog box, set the criteria for reviewing changes:
- When: Select All to see all changes made since sharing was enabled.
- Who: Select Everyone to see changes from all users.
- Where: Leave this box clear to review changes across the entire workbook.
- Highlight changes on screen: Ensure this box is checked to visually highlight the changes.
Click OK.

Excel will highlight columns and rows with changes in red. Individual cell edits from different users are marked with different colors. Hovering over a changed cell will display information about who made the change and when.

Important Note: The “Compare and Merge Workbooks” feature is specifically designed for merging copies of a shared workbook. It will not work if you attempt to merge unrelated Excel files. If the command is greyed out, ensure you are working with the original shared workbook and attempting to merge copies of it.

Third-Party Excel Comparison Tools: Advanced Features and Comprehensive Comparison

While Excel’s built-in features offer several ways to compare sheets, for more advanced comparison needs, particularly when dealing with large datasets, complex workbooks, or the need for detailed difference reports, third-party tools can provide significant enhancements. These tools often offer features beyond basic value comparison, including formula comparison, formatting difference detection, and more robust merging capabilities.

Here are a few notable third-party tools for comparing Excel files:

Synkronizer Excel Compare: A Comprehensive Excel Comparison Add-in

Synkronizer Excel Compare is a powerful add-in designed to compare, merge, and update Excel files efficiently. It offers a range of features that go beyond Excel’s native capabilities.

Key Features of Synkronizer Excel Compare:

Detailed Difference Identification: Compares not only values but also formulas, comments, names, and cell formatting.
Intelligent Merging: Allows selective merging of differences, enabling you to update one sheet with specific changes from another without overwriting other data.
Difference Reporting: Generates detailed and easy-to-read reports that summarize all types of differences found.
Flexible Comparison Options: Offers various comparison modes, including “Compare as normal worksheets,” “Compare with link options,” “Compare as database,” and “Compare selected ranges,” catering to different data structures and comparison needs.
Visual Difference Highlighting: Highlights differences directly in the sheets for easy visual identification.

Using Synkronizer Excel Compare:

Installation and Launch: After installing the add-in, you can access it from the Add-ins tab in Excel. Click the Synkronizer icon to open the Synkronizer pane.
Select Workbooks and Sheets: In the Synkronizer pane, select the two Excel workbooks you want to compare. The add-in automatically matches sheets with the same names across workbooks. You can manually select sheets or specify matching criteria.
Choose Comparison Options: Select the comparison type that best fits your data structure (e.g., “Compare as normal worksheets” for general spreadsheets or “Compare as database” for structured data). You can also choose which content types to compare (values, formulas, formats, comments, etc.) using the Select tab in the Synkronizer ribbon.
Start Comparison: Click the Start button. Synkronizer will compare the sheets and display the results.

Analyzing Differences with Synkronizer:

Synkronizer provides both a summary report and a detailed difference report. The summary report gives an overview of all difference types found (cell changes, format changes, added/deleted rows, etc.). The detailed report lists each specific difference.

Clicking on a difference in the detailed report highlights the corresponding cells in both compared sheets, making it easy to examine each discrepancy.

Synkronizer can also generate a hyperlinked difference report in a separate Excel workbook, allowing you to navigate directly to each difference from the report.

Comparing All Sheets at Once:

If you have multiple sheets in your workbooks, Synkronizer can compare all matching sheet pairs simultaneously and present a summary report for all comparisons.

Highlighting and Merging Differences:

Synkronizer highlights differences using color-coding (e.g., yellow for value differences, lilac for format differences, green for inserted rows). You can customize which differences are highlighted using the Outline options.

For merging, Synkronizer allows you to selectively update differences from one sheet to another. You can choose to update individual cells, rows, or columns, and control the direction of the update.

Synkronizer Excel Compare offers a robust set of features for comprehensive Excel file comparison and merging. A trial version is available for download from their website.

Ablebits Compare Sheets for Excel: User-Friendly and Step-by-Step Comparison

Ablebits Compare Sheets for Excel is another powerful tool, integrated into their Ultimate Suite for Excel, designed for intuitive and user-friendly worksheet comparison.

Key Features of Ablebits Compare Sheets:

Step-by-Step Wizard: Guides you through the comparison process, making it easy to configure various options.
Comparison Algorithms: Offers different comparison algorithms (No key columns, By key columns, Cell-by-cell) to suit various data structures.
Review Differences Mode: Presents compared sheets side-by-side in a “Review Differences” mode, allowing you to manage differences one-by-one.
Flexible Matching Options: Provides options for matching rows based on “First match,” “Best match,” or “Full match only,” offering flexibility in how rows are compared.
Comprehensive Difference Detection: Compares values, formulas, and formatting, and allows you to specify which types of differences to highlight or ignore.

Using Ablebits Compare Sheets:

Launch Compare Sheets: After installing Ablebits Ultimate Suite, find the Compare Sheets button on the Ablebits Data tab in Excel.

Launching Ablebits Compare Sheets from the Ablebits Data tab in Excel.
Select Worksheets: The wizard prompts you to select the two worksheets you want to compare. You can choose entire sheets, current tables, or specific ranges.
Choose Algorithm and Matching Type: Select the appropriate comparison algorithm and match type based on your data structure and comparison goals. The default “No key columns” algorithm is suitable for general sheet comparison.
Specify Differences to Highlight: Choose which differences to highlight (values, formulas, formatting) and which to ignore (e.g., hidden rows/columns).
Compare and Review: Click the Compare button. Ablebits Compare Sheets processes the data and opens the worksheets in “Review Differences” mode.

Reviewing and Merging Differences with Ablebits:

In “Review Differences” mode, the compared sheets are displayed side-by-side with differences highlighted using color-coding (blue for rows unique to Sheet 1, red for rows unique to Sheet 2, green for differing cells).

Each worksheet has a vertical toolbar that allows you to navigate through the differences one-by-one and decide whether to merge or ignore each difference.

Ablebits Compare Sheets provides a user-friendly and efficient way to compare and manage differences between Excel worksheets. A trial version of Ablebits Ultimate Suite, including Compare Sheets, is available for download.

Other Third-Party Tools

Other notable third-party Excel comparison tools include:

xlCompare: xlCompare is a utility focused on comparing and merging workbooks, sheets, and VBA projects. It offers features for finding duplicates, updating records, and merging data, with options to filter and highlight comparison results.
Change pro for Excel: Change pro for Excel is designed for comparing Excel sheets on both desktop and mobile devices. It focuses on identifying formula and value differences, layout changes, and embedded objects. It also offers reporting, filtering, and integration with document management systems.

Online Excel Comparison Services: Quick and Convenient Comparison

For quick, one-off comparisons, especially when security isn’t a primary concern and your files don’t contain sensitive data, online Excel comparison services offer a convenient option. These services allow you to upload two Excel files and quickly view the differences directly in your web browser, without needing to install any software.

Examples of online Excel comparison services include:

XLComparator: XLComparator is a web-based service specifically for comparing Excel files.
CloudyExcel: CloudyExcel is another online tool that provides Excel comparison functionality.

Using Online Services (Example: CloudyExcel):

Access the Website: Go to the website of the online service (e.g., CloudyExcel).
Upload Files: Upload the two Excel workbooks you want to compare using the service’s file upload interface.
Compare Sheets: Click the “Find Difference” or similar button to initiate the comparison.
View Results: The service will process the files and highlight the differences directly on the screen, usually within the browser window.

Online services are generally easy to use and provide immediate results. However, be mindful of uploading sensitive or confidential data to third-party websites.

Conclusion: Choosing the Right Method to Compare Excel Sheets

Comparing two sheets in Excel can be approached in various ways, each suited to different scenarios and needs. From simple side-by-side viewing for a quick visual check to formula-based difference reports and conditional formatting for highlighting value discrepancies, Excel offers built-in tools for basic comparisons.

For more comprehensive and advanced comparison requirements, especially when dealing with complex workbooks, structural differences, or the need to compare formulas and formatting, third-party tools like Synkronizer Excel Compare and Ablebits Compare Sheets provide robust solutions. Online services offer a quick and convenient option for less sensitive data and basic comparisons.

Choosing the right method depends on the complexity of your data, the level of detail required in the comparison, and your comfort level with different Excel features or third-party add-ins. By understanding these various methods, you can effectively compare two sheets in Excel and ensure data accuracy and consistency in your spreadsheets.