How to Excel Compare Two Sheets for Differences: A Comprehensive Guide

Have you ever worked with multiple versions of an Excel workbook and struggled to pinpoint the exact changes made? Or perhaps you need to audit changes across different periods? Microsoft Spreadsheet Compare is a powerful tool designed to help you identify and highlight the differences between two Excel files, making it an indispensable asset for anyone working with spreadsheets. This guide will walk you through how to effectively excel compare two sheets using Spreadsheet Compare, ensuring you can track changes, maintain accuracy, and streamline your workflow.

Important: Spreadsheet Compare is a feature available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to utilize this tool.

Step-by-Step Guide to Compare Excel Sheets

Let’s dive into the process of comparing two Excel sheets to identify differences. Follow these steps to effectively use Spreadsheet Compare:

1. Open Spreadsheet Compare.

   First, locate and launch the Spreadsheet Compare application on your computer. It is typically found within your Office tools suite if you have a compatible version installed.

2. Select Comparison Options.

   In the bottom-left pane of the Spreadsheet Compare window, you’ll find a range of options to customize your comparison. These options allow you to specify what aspects of the workbooks you want to compare. You can choose to include:

   • Formulas: Compares the formulas used in cells.
   • Cell Formatting: Highlights differences in cell styles, fonts, colors, and other formatting elements.
   • Macros: Checks for variations in VBA macros embedded within the workbooks.
   • Comments: Compares comments added to cells.

   For a comprehensive comparison, you can simply click Select All to include all available options. Choose the options that best suit your needs for the current comparison task.

3. Choose the Excel Files to Compare.

   On the Home tab of the Spreadsheet Compare ribbon, click the Compare Files button. This action opens the Compare Files dialog box, where you will specify the two Excel files you want to compare.

   • In the Compare row, use the Browse button to navigate to and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are saved online.

   • In the To row, click the Browse button again to select the later version (or the second file you wish to compare against the first).

   Note: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders. This is particularly useful for version control.

4. Run the Comparison.

   Once you have selected both the “Compare” and “To” files, click OK in the Compare Files dialog box to initiate the comparison process. Spreadsheet Compare will analyze the two workbooks based on the options you selected.

5. Handling Password-Protected Files.

   If either of the workbooks you are trying to compare is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK in the message box. You will then be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and analyze the file content.

Understanding the Comparison Results

After running the comparison, Spreadsheet Compare presents the results in an intuitive two-pane grid.

• Side-by-Side Grid: The workbook you selected as the “Compare” file is displayed on the left pane, and the “To” file is on the right pane. The comparison starts with the leftmost worksheet in each workbook and proceeds through each sheet. Even hidden worksheets are included in the comparison and displayed.

• Detailed Differences Pane: Below the two grids, a details pane provides a list of specific changes detected, offering a summary view of all modifications.

• Visual Highlighting: Differences are visually highlighted within the grid using color-coding. The colors indicate the type of change:

  • Green Fill Color (in grid): Typically indicates “entered values” or cells where the content (non-formula) has been changed.
  • Green Font Color (in results list): Also represents entered value changes, but in the detailed results pane.
  • Other colors may be used to represent different types of changes, such as formula modifications, formatting changes, or structural differences.

• Resize Cells for Better Visibility: If cell contents are truncated due to column width, click Resize Cells to Fit on the ribbon.

  This will automatically adjust column widths to ensure you can see the complete content of each cell, making it easier to review the differences.

Working Further with Comparison Results

Spreadsheet Compare offers several options to help you work with and utilize the comparison results beyond the initial display.

• Export Results to Excel: To save the comparison results for record-keeping or further analysis, you can export them to an easily readable Excel file. Click Home > Export Results. This creates a new Excel file summarizing the comparison findings.

• Copy Results to Clipboard: For sharing results or incorporating them into other documents, you can copy the comparison details to your clipboard. Click Home > Copy Results to Clipboard. You can then paste this information into applications like Microsoft Word or email.

• Show Workbook Colors: If you need to see the worksheets with their original formatting as closely as possible to how they appear in Excel, click Home > Show Workbook Colors. This option renders the worksheets in the comparison grid with the original cell formatting intact, providing a more visually accurate representation.

Why Use Excel Compare Two Sheets? Practical Applications

Comparing Excel sheets is not just about identifying changes; it’s about maintaining data integrity and efficiency. Here are a few compelling reasons and scenarios where using Spreadsheet Compare is invaluable:

• Audit Trails and Accuracy: For organizations requiring audit compliance, Spreadsheet Compare provides a clear trail of changes made to critical workbooks over time. This is essential for identifying and correcting errors before audits, ensuring data accuracy and accountability.

• Version Control and Collaboration: When collaborating on Excel files, especially across different teams or departments, tracking changes becomes crucial. Spreadsheet Compare helps teams understand modifications made by different users, preventing confusion and ensuring everyone is working with the most accurate and agreed-upon data.

• VBA Code Comparison: Beyond worksheet data, Spreadsheet Compare can also check for differences in VBA (Visual Basic for Applications) code embedded in Excel workbooks. This is vital for developers and advanced users who manage macros and custom functions, ensuring code consistency and identifying unintended alterations. The VBA comparison results are displayed in a separate window, showing code differences side-by-side.

Conclusion

Spreadsheet Compare is a robust tool that significantly simplifies the process of excel compare two sheets and workbooks. Whether you are tracking changes for auditing, collaborating on documents, or simply ensuring data accuracy, mastering this tool will enhance your efficiency and data management capabilities in Excel. By following this guide, you can effectively utilize Spreadsheet Compare to identify, understand, and manage differences between Excel files, ensuring data integrity and streamlining your workflow.