Keeping track of changes in your Excel workbooks can be challenging, especially when multiple people are involved in editing. Have you ever opened a spreadsheet and wondered, “Who made these changes and what exactly did they do?” Microsoft Spreadsheet Compare is a powerful tool designed to answer these questions by meticulously identifying and highlighting the differences between two versions of your Excel sheets. This guide will walk you through how to effectively use Spreadsheet Compare to easily Compare Sheets and understand every alteration.
Step-by-Step Guide: How to Compare Sheets
To begin using Spreadsheet Compare, ensure you have access to it. It’s important to note that 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.
Open Spreadsheet Compare
First, launch the Spreadsheet Compare application. Once opened, you’ll be ready to set up your sheet comparison.
Select Comparison Options
In the lower-left pane of the Spreadsheet Compare window, you’ll find a range of options to customize your comparison. Here, you can specify what elements of the workbooks you want to compare sheets for. These options include:
- Formulas: Compares the formulas used in cells to identify any changes.
- Cell Formatting: Highlights differences in cell formatting, such as fonts, colors, and styles.
- Macros: Checks for alterations or modifications in the VBA macros within the workbooks.
You have the flexibility to select specific options based on your needs, or simply click “Select All” to include all available comparison categories.
Compare Files
Next, navigate to the “Home” tab in the Spreadsheet Compare ribbon and click on the “Compare Files” button. This action will open the “Compare Files” dialog box, prompting you to select the workbooks you want to compare.
In the “Compare Files” dialog box, you’ll see two rows labeled “Compare” and “To“.
-
In the “Compare” row, click the browse button to locate and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even input a web address if your workbooks are saved online. This is your baseline sheet for comparison.
-
In the “To” row, click the browse button to select the more recent version of the workbook that you want to compare against the earlier one. It’s important to note that you can compare sheets from two files even if they have the same name, as long as they are saved in different folders.
Run Comparison
Once you have selected both the “Compare” and “To” files, click “OK” in the “Compare Files” dialog box to initiate the sheet comparison process.
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 to proceed with the compare sheets operation. Microsoft provides resources to help manage passwords used in Spreadsheet Compare for analysis and comparison.
Understanding Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid. The workbook you selected as the “Compare” file is shown on the left pane, representing the earlier version. The workbook selected as the “To” file appears on the right pane, showing the later version. Below these grids, a details pane provides specific information about the identified changes.
Changes are visually highlighted within the grids using different colors. These colors are not arbitrary; they are systematically used to indicate the type of change detected, making it easy to quickly grasp the modifications between your compare sheets.
- The comparison starts by examining the leftmost worksheet in each workbook and proceeds through each sheet sequentially. Even if a worksheet is hidden in either workbook, Spreadsheet Compare will still display and include it in the compare sheets analysis.
- If the cell contents are wider than the cell display, making it difficult to read, simply click “Resize Cells to Fit“. This feature automatically adjusts the column widths to ensure that you can view the complete content of each cell without manually resizing columns.
- Differences are highlighted with distinct formatting. For instance, cells containing “entered values” (cells that do not contain formulas) are typically marked with a green fill color in the side-by-side grid view, and with a green font in the detailed results list pane. The lower-left pane of Spreadsheet Compare serves as a legend, explaining the meaning of each color code used in the compare sheets results.
Working with Comparison Results for Further Analysis
Spreadsheet Compare offers several options for working with the comparison results beyond the initial display. Whether you need to archive the results, share them, or perform more in-depth analysis, you can easily export or copy the comparison data.
Export Results
To save your comparison results for record-keeping or further analysis, you can export them to an Excel file. This is particularly useful for creating a more readable and shareable format of the compare sheets output. To export, click “Home > Export Results“.
Copy Results to Clipboard
If you need to incorporate the comparison results into another document or program, such as Microsoft Word or an email, you can copy and paste them. Click “Home > Copy Results to Clipboard“. This will copy the comparison data, allowing you to paste it into another application.
Show Workbook Colors
For a more visually accurate representation of the worksheets, including their original formatting, you can enable the display of workbook colors. Click “Home > Show Workbook Colors“. This option gives you a high-fidelity view of each worksheet, closely resembling how they appear in Excel, while still highlighting the differences found when you compare sheets.
Why Compare Sheets? – Use Cases for Spreadsheet Compare
Comparing sheets isn’t just about identifying who changed what; it’s a crucial process for maintaining data integrity and accuracy in various scenarios.
- Audit Trails: For organizations undergoing audits, Spreadsheet Compare provides an essential audit trail for critical workbooks. By comparing sheets month over month or year over year, you can track changes, ensuring compliance and helping to proactively identify and correct errors before auditors discover them. This use case highlights the importance of being able to effectively compare sheets for regulatory and compliance purposes.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also check for differences in VBA (Visual Basic for Applications) code. This feature is invaluable for developers who need to track changes in the underlying code of their Excel applications. The results are presented in a side-by-side window, making it straightforward to review and understand the modifications made to VBA code when you compare sheets that contain macros.
By using Spreadsheet Compare to compare sheets regularly, you can maintain better control over your Excel data, ensure accuracy, and streamline collaboration, making it an indispensable tool for anyone working with Excel workbooks in a collaborative or audit-sensitive environment.
