Microsoft Excel Spreadsheet Compare is a powerful tool designed to pinpoint differences between versions of your Excel workbooks. If you collaborate on spreadsheets or need to track changes over time, understanding how to use Spreadsheet Compare is essential. This guide will walk you through using this tool to efficiently compare Excel files and understand the results.
Spreadsheet Compare is a valuable feature, but it’s important to note its availability. It is included with specific Microsoft Office suites, namely 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 functionality.
Step-by-Step Guide to Comparing Excel Spreadsheets
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare. Follow these steps to effectively identify changes:
-
Launch Spreadsheet Compare: Begin by opening the Spreadsheet Compare application. You can typically find it by searching for “Spreadsheet Compare” in your Windows search bar or within your Office tools directory.
-
Select Comparison Options: In the bottom-left pane 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. You can choose to include:
- Formulas: Compares the formulas used in cells.
- Cell Formatting: Highlights differences in cell formatting such as fonts, colors, and styles.
- Macros: Compares VBA macros embedded within the workbooks.
- Comments: Checks for changes in cell comments.
For a comprehensive comparison, you can simply click “Select All” to include all these elements in your analysis.
-
Initiate File Comparison: 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.
-
Choose the “Compare” File (Earlier Version): 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 not only from your local computer or network but also from websites by entering a web address where your workbooks are stored. This is useful for comparing files stored in cloud services or shared online locations.
Alt text: Browse button icon for selecting the earlier version of an Excel file in Spreadsheet Compare.
-
Choose the “To” File (Later Version): Next, in the “Compare Files” dialog box, go to the “To” row and click the browse icon to select the more recent version of the workbook you want to compare against the earlier one.
Alt text: Browse button icon for selecting the later version of an Excel file for comparison in Spreadsheet Compare.
It’s worth noting that you can compare two files even if they have the same name, as long as they are saved in different folders. This is particularly helpful when you have versioned files stored in separate directories.
-
Run the Comparison: Once you have selected both the “Compare” and “To” files, click “OK” in the “Compare Files” dialog box to initiate the comparison process. Spreadsheet Compare will then analyze the two workbooks based on the options you selected and generate a detailed comparison report.
Password Protected Workbooks: If either of the workbooks you are trying to compare is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click “OK” and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and analyze the file content. You can find more information on password handling with Spreadsheet Compare in the Microsoft Office support documentation.
Understanding Spreadsheet Compare Results
After running the comparison, the results are presented in a clear, two-pane grid within the Spreadsheet Compare window.
-
Side-by-Side Grid: The left pane of the grid displays the workbook you selected as the “Compare” file (earlier version), while the right pane shows the “To” file (later version). Spreadsheet Compare compares worksheets from each file, starting from the leftmost worksheet in each workbook. Even if a worksheet is hidden in one of the Excel files, it will still be displayed and included in the comparison within Spreadsheet Compare.
-
Detailed Differences Pane: Below the side-by-side grid, you’ll find a details pane that provides a list of the specific changes detected.
-
Color-Coded Highlighting: Changes are visually highlighted in both the side-by-side grid and the details pane using colors to indicate the type of difference. For instance, cells containing “entered values” (cells with direct text or number inputs, not formulas) are typically highlighted with a green fill color in the grid and a green font color in the results list. A legend in the lower-left pane of Spreadsheet Compare explains the color-coding, allowing you to quickly interpret the types of changes identified.
-
Resize Cells for Better Viewing: If cell contents are truncated due to narrow column widths, you can click “Resize Cells to Fit” to automatically adjust column widths to display the full content of each cell, improving readability.
Alt text: Resize Cells to Fit button icon in Spreadsheet Compare for adjusting column widths.
Further Actions with Comparison Results
Spreadsheet Compare offers several options for working with and utilizing the comparison results beyond the initial display:
-
Export to Excel: To save the comparison results for record-keeping or further analysis in Excel, you can export them to a readily readable Excel file. Click “Home > Export Results“. This creates a new Excel file summarizing the comparison findings.
-
Copy to Clipboard: If you need to incorporate the comparison results into other documents or applications like Microsoft Word or email, you can copy them to the clipboard. Click “Home > Copy Results to Clipboard“. This allows you to paste the results into another program.
-
Show Workbook Colors: For a more visually accurate representation of the worksheets, including cell formatting as it appears in Excel, click “Home > Show Workbook Colors“. This option displays the worksheets with their original formatting, providing a high-fidelity view alongside the comparison highlighting.
Use Cases for Excel Workbook Comparison
Comparing Excel workbooks is beneficial in various scenarios, enhancing accuracy and control over spreadsheet data:
-
Audit Trail and Compliance: For organizations undergoing audits, Spreadsheet Compare provides a valuable audit trail for critical workbooks. By comparing workbooks month-over-month or year-over-year, you can track changes, identify potential errors, and ensure data integrity before audits, aiding in compliance and reducing risks.
-
Collaborative Workbook Management: When multiple users collaborate on Excel workbooks, tracking changes becomes crucial. Spreadsheet Compare helps teams manage collaborative edits effectively by clearly highlighting who changed what and when, facilitating better version control and reducing conflicts.
-
VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also compare VBA (Visual Basic for Applications) code embedded in Excel workbooks. This is essential for developers and users who rely on macros, allowing them to track changes in code logic and functionality between workbook versions. The VBA comparison results are displayed in a separate window, showing differences side-by-side for easy code review.
By leveraging Microsoft Excel Spreadsheet Compare, you can gain significant control over your Excel workbooks, ensuring accuracy, facilitating collaboration, and maintaining a clear history of changes. This tool is indispensable for anyone working with important Excel data and requiring robust change tracking and comparison capabilities.
