It’s a common scenario: you share an Excel workbook for collaboration, and upon its return, you’re faced with the daunting task of identifying what exactly has been altered. Manually scanning through rows and columns is tedious and error-prone. Fortunately, Microsoft provides a robust solution to this problem with Spreadsheet Compare, a powerful tool designed to meticulously Compare Excel Files and highlight every modification.
Important Note: Spreadsheet Compare is a feature included in 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 functionality.
Let’s delve into how you can effectively compare Excel files using Spreadsheet Compare and understand the insights it provides.
Step-by-Step Guide to Compare Excel Files
-
Launch Spreadsheet Compare: Begin by opening the Spreadsheet Compare application.
-
Select Comparison Options: In the bottom-left panel, customize your comparison by choosing the elements you want to analyze. Options include formulas, cell formatting, comments, and even macros. For a comprehensive comparison, simply click “Select All“.
-
Initiate File Comparison: Navigate to the “Home” tab and click on “Compare Files“.
-
Choose the Original Workbook: In the “Compare Files” dialog box, locate the “Compare” row. Click the browse icon to select the earlier version of your Excel workbook. Spreadsheet Compare allows you to choose files from your local computer, network locations, or even by entering a web address if your workbooks are stored online.
Browse button in Spreadsheet Compare dialog box, used to select the original Excel file for comparison.
-
Choose the Revised Workbook: In the “To” row, click the browse icon to select the modified version of the workbook you want to compare against the original.
Browse button in Spreadsheet Compare dialog box, used to select the revised Excel file for comparison.
Note: You can effectively compare excel files even if they share the same name, as long as they are saved in different folders.
-
Execute the Comparison: Click “OK” to initiate the file comparison process.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password protected. Click “OK” and you will be prompted to enter the password(s). Spreadsheet Compare is designed to work with password-protected files, ensuring you can still compare excel files even with security measures in place.
Understanding the Comparison Results
Once the comparison is complete, Spreadsheet Compare presents the findings in a user-friendly, two-pane grid. The original workbook (“Compare” file) is displayed on the left, and the revised workbook (“To” file) is on the right. A detailed pane beneath the grids provides a comprehensive list of changes.
Changes are visually highlighted using distinct colors, each representing a specific type of modification. This color-coding system allows for quick and easy identification of the nature of each change.
-
Side-by-Side Worksheet Comparison: Spreadsheet Compare intelligently compares corresponding worksheets in each file, starting from the leftmost sheet. Even hidden worksheets are included in the comparison, ensuring no changes are missed.
-
Cell Content Visibility: If cell contents are truncated due to narrow columns, simply click “Resize Cells to Fit” to automatically adjust column widths for optimal readability.
Resize Cells to Fit button in Spreadsheet Compare, enhancing readability of cell contents.
-
Color-Coded Difference Legend: The lower-left pane acts as a legend, explaining the color codes used to highlight different types of changes. For instance, cells containing “entered values” (non-formula cells) might be highlighted with a green fill in the grid and a green font in the results list. This consistent color scheme makes it straightforward to interpret the comparison results.
Advanced Options for Working with Comparison Data
Spreadsheet Compare offers several options to further analyze and utilize the comparison results:
-
Export Results to Excel: For easier sharing, archiving, or further analysis, you can export the comparison results to a readily readable Excel file. Simply click “Home > Export Results“.
-
Copy Results to Clipboard: To integrate 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 into another program seamlessly.
-
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, preserving the original formatting for better context.
Why Compare Excel Files? Real-World Applications
The ability to compare excel files effectively extends beyond simply tracking changes. It’s a valuable tool for various professional scenarios:
-
Audit Trails and Compliance: In organizations undergoing audits, Spreadsheet Compare provides a crucial audit trail for critical workbooks. By comparing files month-over-month or year-over-year, you can meticulously track changes, identify potential errors, and ensure data integrity before auditors identify discrepancies.
-
VBA Code Comparison: Spreadsheet Compare isn’t limited to worksheet content; it can also compare excel files for differences in VBA (Visual Basic for Applications) code. The results are presented in a dedicated window, allowing you to review code modifications side-by-side, essential for developers and those managing complex Excel-based applications.
By mastering Spreadsheet Compare, you gain a significant advantage in managing and auditing Excel workbooks, ensuring accuracy, accountability, and efficient collaboration. Start leveraging this powerful tool to streamline your workflow and maintain control over your critical spreadsheet data.
