Have you ever opened an Excel workbook and wondered what changes were made and by whom? Microsoft Spreadsheet Compare is a powerful tool designed to answer these questions directly. It allows you to meticulously compare different versions of your Excel workbooks, highlighting every alteration for your review.
Important Note: Microsoft Spreadsheet Compare is exclusively 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 feature.
Step-by-Step Guide to Compare Excel Files
Let’s walk through the process of using Spreadsheet Compare to effectively analyze differences between Excel workbooks:
-
Launch Spreadsheet Compare: Begin by opening the Spreadsheet Compare application on your computer.
-
Select Comparison Options: In the bottom-left corner of the application window, you’ll find a pane with various options. Choose the elements you want to include in your comparison. This can range from formulas and cell formatting to macros and more. For a comprehensive comparison, you can simply click Select All.
-
Initiate File Comparison: Navigate to the Home tab within Spreadsheet Compare and click on the Compare Files button.
-
Choose the Earlier Workbook Version: In the Compare Files dialog box, locate the Compare row. Click the browse icon to select the older version of your Excel workbook. Spreadsheet Compare supports files stored locally, on network drives, and even those hosted online via web addresses.
-
Choose the Workbook to Compare Against: In the same Compare Files dialog box, proceed to the To row. Click the browse icon to select the more recent version of the workbook you wish to compare against the earlier one.
Note: You have the flexibility to compare two files that share the same name, provided they are saved in different folders. This is particularly useful for version control.
-
Execute the Comparison: Click OK to initiate the Excel compare process. Spreadsheet Compare will now analyze both workbooks based on your selected options.
Note: If you encounter an “Unable to open workbook” message, it’s likely that one or both of the workbooks are password-protected. Click OK and you will be prompted to enter the password. For further information on password handling within Spreadsheet Compare, refer to how passwords and Spreadsheet Compare work together.
Understanding Excel Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
- Side-by-Side Grid: The left pane displays the earlier version of the workbook (the “Compare” file), while the right pane shows the later version (the “To” file). Spreadsheet Compare intelligently matches worksheets between the two files for comparison, starting from the leftmost sheet in each. Even hidden worksheets are included in the comparison and displayed.
- Detailed Differences Pane: Below the side-by-side grids, a detailed pane provides a list of specific changes detected.
Changes are visually highlighted using colors to denote the type of modification. For instance, cells containing “entered values” (cells without formulas) are typically marked with a green fill color in the grid and a green font in the results list. A legend in the lower-left pane clearly explains the color-coding system, ensuring you can easily interpret the comparison findings.
If cell content is truncated due to column width, simply click Resize Cells to Fit to expand columns and view the complete data.
Advanced Options for Working with Comparison Results
Spreadsheet Compare offers several options for managing and utilizing the comparison results beyond the visual display:
-
Export to Excel: To save the comparison results for later review or further analysis, you can export them to an easily readable Excel file. Click Home > Export Results. This generates a new Excel file summarizing the differences.
-
Copy to Clipboard: For integrating the comparison data into other documents or applications like Microsoft Word, use the Home > Copy Results to Clipboard option. This allows you to paste the results as text or formatted data.
-
Show Workbook Colors: To get a more visually accurate representation of each worksheet, including cell formatting as it appears in Excel, click Home > Show Workbook Colors. This provides a high-fidelity view that mirrors the original Excel formatting.
Why Use Excel Comparing? Practical Applications
Excel Comparing is not just about identifying changes; it’s a valuable tool for various scenarios:
-
Audit Trails and Compliance: For organizations undergoing audits, Spreadsheet Compare provides a crucial audit trail for critical workbooks. By tracking changes over time (monthly or yearly), you can proactively identify and rectify errors before auditors discover them, ensuring data integrity and compliance.
-
Collaborative Workbook Management: In collaborative environments where multiple users edit the same workbooks, comparing versions helps track contributions, understand modifications, and prevent accidental data loss or overwrites.
-
Error Detection and Correction: Comparing workbook versions after updates or modifications allows you to quickly pinpoint unintended changes or errors introduced during editing, facilitating efficient error correction.
-
VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare extends its capabilities to comparing VBA (Visual Basic for Applications) code within Excel workbooks. The results are presented in a dedicated window, highlighting line-by-line differences in the code, which is invaluable for developers managing VBA projects.
By leveraging Excel comparing effectively, you can maintain control over your spreadsheet data, ensure accuracy, and streamline collaborative workflows. Whether you’re managing financial data, project plans, or any critical information in Excel, Spreadsheet Compare is an essential tool for effective workbook management and version control.
