Have you ever opened an Excel workbook and wondered who made changes and what those changes were? Microsoft Spreadsheet Compare is a powerful tool designed to answer exactly these questions, allowing you to easily identify and highlight differences between versions of your Excel spreadsheets. This guide will walk you through how to use Spreadsheet Compare to effectively track and understand changes in your Excel files.
Important: Spreadsheet Compare is included with 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 access this feature.
Step-by-Step Guide to Comparing Excel Spreadsheets
Let’s dive into the process of Comparing Differences In Excel Spreadsheets using Spreadsheet Compare:
-
Launch Spreadsheet Compare. Find “Spreadsheet Compare” in your applications menu and open it.
-
Select Comparison Options. In the bottom-left panel 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, such as:
- Formulas: Compares the formulas used in cells.
- Cell Formatting: Identifies changes in cell styles, colors, fonts, and other formatting elements.
- Macros: Checks for differences in VBA macros.
- Comments: Compares comments added to cells.
- Sheet Names: Identifies changes in worksheet names.
You can choose specific options based on your needs, or simply click Select All to compare all aspects of the workbooks.
-
Choose Files for Comparison. Navigate to the Home tab in the Spreadsheet Compare ribbon and click on Compare Files. This will open the Compare Files dialog box.
-
Select the “Compare” File (Older Version). In the Compare row of the dialog box, click Browse icon to locate 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.
-
Select the “To” File (Newer Version). In the To row, click the Browse icon to select the more recent version of the workbook you wish to compare against the earlier version.
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 useful for version control when you might save iterations of a file in separate directories.
-
Initiate the Comparison. Click OK in the Compare Files dialog box to start the comparison process. Spreadsheet Compare will analyze both workbooks based on the options you selected.
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 on the message and you will be prompted to enter the password(s). Spreadsheet Compare can work with password-protected files, ensuring you can still track changes even in sensitive documents. For more detailed information on password handling, you can refer to how passwords and Spreadsheet Compare work together.
Understanding the 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 “Compare” file (older version), and the right pane shows the “To” file (newer version). Spreadsheet Compare compares worksheets starting from the leftmost sheet in each workbook. 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.
- Visual Highlighting: Changes are highlighted with distinct colors directly in the grid, making it easy to spot differences at a glance. The color-coding depends on the type of change. For instance, cells containing entered values (not formulas) are typically highlighted with a green fill in the grid and a green font in the results list pane.
To improve readability, especially when cell content is truncated due to column width, click Resize Cells to Fit. This ensures you can view the complete content of each cell.
A legend in the lower-left pane clearly explains what each color in the highlighting scheme represents, allowing you to quickly interpret the types of changes made.
Further Actions with Comparison Results
Spreadsheet Compare offers options to work with your comparison results beyond just viewing them on screen:
- Export to Excel: To save the comparison results for record-keeping or further analysis, you can export them to a readily readable Excel file. Simply click Home > Export Results. This creates a new Excel file summarizing the differences.
- Copy to Clipboard: If you need to incorporate the comparison results into other documents or applications like Microsoft Word, use the Home > Copy Results to Clipboard option. This copies the results, which you can then paste into another program.
- Show Workbook Colors: For 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 Workbooks? Practical Applications
Comparing Excel workbooks is not just about identifying changes; it serves several crucial purposes in various scenarios:
- Audit Trails and Compliance: For organizations undergoing audits, Spreadsheet Compare is invaluable. It allows you to create a clear audit trail of critical workbooks, showing changes over time (month-over-month or year-over-year). This helps in proactively identifying and correcting errors before auditors discover them, ensuring compliance and data integrity.
- Version Control and Collaboration: When multiple people collaborate on Excel spreadsheets, or when you maintain different versions of a workbook, Spreadsheet Compare helps track who changed what and when. This is essential for managing version control, resolving conflicts, and understanding the evolution of your data.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code embedded in Excel workbooks. The VBA code comparison results are displayed in a separate window, highlighting line-by-line changes, which is crucial for developers and those managing Excel-based applications.
By effectively comparing differences in Excel spreadsheets, you can maintain data accuracy, ensure accountability, and streamline collaborative workflows. Spreadsheet Compare is an indispensable tool for anyone working with Excel in professional or complex environments.
