Collaborating on Excel spreadsheets can be a source of confusion and errors, especially when multiple users are involved. 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 these exact questions. By highlighting the differences between various versions of your Excel files, it brings clarity and control to your spreadsheet management.
Important: It’s worth noting that 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.
Step-by-Step Guide to Comparing Excel Sheets with Spreadsheet Compare
Let’s walk through the process of using Spreadsheet Compare to effectively analyze changes in your Comparative Excel Sheet.
1. Open Spreadsheet Compare and Select Comparison Options
First, launch the Spreadsheet Compare application. Once open, locate the lower-left pane. Here, you’ll find a range of options to customize your comparison. You can choose to include specific elements in your analysis, such as:
- Formulas: Track changes made to calculations and formulas within your sheets.
- Cell Formatting: Identify alterations in cell styles, colors, and number formats.
- Macros: Detect modifications or additions to VBA macros embedded in your workbook.
To ensure a comprehensive comparison, you can simply click “Select All” to include all available options.
2. Choose the Excel Files for Comparison
Navigate to the “Home” tab in the Spreadsheet Compare ribbon and click on “Compare Files“. This action will open the “Compare Files” dialog box, prompting you to select the workbooks you want to analyze.
In the “Compare” row, use the browse button to locate and select the earlier version of your Excel workbook. Spreadsheet Compare allows you to choose files from your local computer, network drives, or even by entering a web address if your workbooks are stored online.
Next, in the “To” row, use the browse button to select the more recent version of the workbook you wish to compare against the earlier one.
Note: Spreadsheet Compare is capable of comparing two files even if they share the same name, as long as they are saved in different folders. This is particularly useful for version control.
Once you have selected both the earlier and later versions of your comparative Excel sheet, click “OK” to initiate the comparison process.
Note: If you encounter an “Unable to open workbook” message, it’s likely that one or both of the Excel files are password protected. Click “OK” and you will be prompted to enter the password. For more detailed information on password-protected files and Spreadsheet Compare, you can refer to how passwords and Spreadsheet Compare work together.
3. Understanding the Excel Comparison Results
After running the comparison, Spreadsheet Compare will display the results in a clear, two-pane grid. The workbook you selected as the “Compare” file (earlier version) will appear on the left pane, and the “To” file (later version) will be on the right pane. Below these grids, a details pane provides further information about the identified changes.
Changes are visually highlighted using different colors, each indicating a specific type of modification. This color-coding system makes it easy to quickly grasp the nature of the changes made between the two Excel sheet versions.
Exploring Comparison Results and Further Options
Navigating and Resizing Cells
Within the side-by-side grid, each worksheet from both files is compared, starting from the leftmost sheet in each workbook. Even if a worksheet is hidden in one of the workbooks, Spreadsheet Compare will still display and include it in the comparison.
If cell contents appear truncated due to narrow column widths, click “Resize Cells to Fit“. This feature automatically adjusts column widths to ensure you can view the complete content of each cell.
Color-Coding Legend for Changes
Differences between the compared Excel sheets are highlighted with distinct visual cues. These cues include cell fill colors and text font colors, depending on the type of change detected. For instance, cells containing “entered values” (cells with data directly entered, not formulas) are typically formatted with a green fill color in the side-by-side grid and a green font in the results list pane.
The lower-left pane of the Spreadsheet Compare window acts as a legend, explaining the meaning of each color used in the comparison results. Refer to this legend to quickly interpret the types of changes identified.
Exporting and Copying Comparison Results
Spreadsheet Compare offers options to save or further analyze the comparison results outside of the application.
- Export to Excel: To save the results in an easily readable Excel file, click “Home > Export Results“. This generates a new Excel file summarizing the comparison findings.
- Copy to Clipboard: To copy the comparison results for use in other programs like Microsoft Word, click “Home > Copy Results to Clipboard“. You can then paste the results into another application for documentation or reporting purposes.
Displaying Workbook Colors
By default, Spreadsheet Compare highlights changes, but it may not display the original formatting from the Excel workbooks. If you need to see the cell formatting as it appears in Excel, click “Home > Show Workbook Colors“. This option provides a high-fidelity view of each worksheet, including cell formatting, closely resembling how they look in Excel itself.
Why Use Comparative Excel Sheets and Spreadsheet Compare?
Utilizing comparative Excel sheets and tools like Spreadsheet Compare offers significant advantages in various scenarios:
- Audit Trails and Compliance: For organizations facing audits, tracking changes in critical workbooks is crucial. Spreadsheet Compare provides a clear trail of modifications over time, helping identify and rectify errors before audits. This is vital for maintaining data integrity and compliance.
- Version Control and Collaboration: When teams collaborate on Excel files, managing different versions becomes challenging. Spreadsheet Compare simplifies version control by clearly highlighting changes, ensuring everyone understands what modifications have been made and why.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code embedded in Excel workbooks. The results are presented in a side-by-side window, making it easy to review and understand code modifications. This is invaluable for developers and users who rely on macros.
In conclusion, Microsoft Spreadsheet Compare is an indispensable tool for anyone working with comparative Excel sheets. It streamlines the process of identifying and understanding changes, enhancing collaboration, ensuring data accuracy, and simplifying version control. By leveraging this tool, you can gain better control over your spreadsheets and improve your overall data management practices.
