Comparing two Excel workbooks is a common task for professionals who manage and analyze data. Whether you’re auditing spreadsheets, tracking changes, or ensuring data integrity, identifying differences between workbooks is crucial. Microsoft Spreadsheet Compare, a powerful tool available with specific Microsoft Office suites, simplifies this process. This guide will walk you through how to effectively Compare Two Excel Workbooks using Spreadsheet Compare, enabling you to pinpoint discrepancies and maintain accuracy in your data.
Getting Started with Spreadsheet Compare
Before you can compare two excel workbooks, you need to open the Spreadsheet Compare application. This tool is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise.
To open Spreadsheet Compare:
- Navigate to the Start menu on your Windows system.
- Look for Spreadsheet Compare in your list of applications. If you don’t see it immediately, simply start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare option to launch the application.
Once opened, you’ll notice a straightforward interface designed for comparing files. Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, available with the same Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Excel Workbooks
Comparing Excel files with Spreadsheet Compare is a straightforward process. Follow these steps to effectively compare two excel workbooks:
-
Initiate the Comparison: In the Spreadsheet Compare window, click Home > Compare Files. This action will open the Compare Files dialog box, which is the starting point for selecting the workbooks you want to analyze.
-
Select the Workbooks:
- In the Compare Files dialog box, locate the Compare box. Click the blue folder icon situated next to it. This will open a browser window, allowing you to navigate to the location of the earlier version of your Excel workbook. You can select files from your local computer, network locations, or even enter a web address if your workbooks are stored online.
- Next, find the To box and click the green folder icon beside it. Browse to the location of the second workbook – the one you want to compare against the earlier version. Once you’ve selected both files, click OK.
Tip: Spreadsheet Compare allows you to compare two files even if they share the same name, as long as they are saved in different folders. This is particularly useful for comparing versions of the same report saved in different project folders.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll see a list of options that define what aspects of the workbooks will be compared. You can customize your comparison by checking or unchecking options such as:
- Formulas: Compares the formulas used in cells.
- Macros: Checks for differences in VBA macros.
- Cell Format: Identifies changes in cell formatting, including fonts, colors, and styles.
- Values: Compares the numerical and text values within cells.
To perform a comprehensive comparison, you can simply click Select All. Choose the options that best suit your comparison needs and click OK to proceed.
-
Run the Comparison: After selecting your workbooks and comparison options, click OK in the Compare Files dialog box to initiate the comparison process. Spreadsheet Compare will analyze the two Excel files based on your selected criteria.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it’s likely that one of the workbooks is password protected. Click OK on the message and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the contents of protected files.
Understanding and Interpreting Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid within Spreadsheet Compare.
-
Side-by-Side Grid: The left pane displays the workbook you selected as the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). Each workbook’s worksheets are compared against the corresponding worksheet in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Notably, even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the differences between the workbooks directly in the side-by-side grid. Changes are visually represented by cell fill colors or text font colors, depending on the nature of the difference. A legend in the lower-left pane clearly explains what each color signifies.
Example Interpretation:
Consider a scenario where you’re comparing quarterly sales reports. The earlier version (left pane) might contain preliminary Q4 figures, while the newer version (right pane) has the finalized numbers.
In the comparison results:
-
Cells with directly entered values that have been modified (e.g., updated sales figures in column E for Q4) are highlighted with a green fill color.
-
Cells containing formulas whose calculated results have changed due to value updates are marked with a blue-green fill. This indicates that changes in input values have propagated through formulas, affecting dependent cells (e.g., Year-to-Date totals in column F).
-
Formula corrections are also identified. For instance, if a formula in the older version was incorrect (e.g., summing an incomplete range), and it has been corrected in the newer version, Spreadsheet Compare will highlight this change.
-
Resize Cells: If cell content is truncated due to column width, click Resize Cells to Fit to automatically adjust column widths and display the full content.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional workbook analysis capabilities. Once activated, the Inquire add-in adds an “Inquire” tab to the Excel ribbon.
From the Inquire tab, you can:
- Analyze Workbook: Gain insights into workbook structure, formula relationships, and potential errors.
- Show Relationships: Visualize dependencies between cells, worksheets, and even links to other workbooks.
- Clean Excess Formatting: Optimize workbook performance by removing unnecessary cell formatting.
While Spreadsheet Compare is a dedicated tool for comparing files externally, the Inquire add-in offers in-depth analysis tools directly within Excel. You can even launch Spreadsheet Compare from within Excel using the “Compare Files” command in the Inquire tab if you have two workbooks open that you wish to compare.
If the Inquire tab is not visible in your Excel, you may need to activate it through Excel Options > Add-Ins > COM Add-ins > Go > check “Inquire” > OK.
Enhancing Workbook Management with Microsoft Tools
For organizations managing critical Excel workbooks and Access databases, Microsoft offers advanced management tools.
- Microsoft Audit and Control Management Server: Provides robust change management features specifically designed for Excel and Access files, ensuring audit trails and version control.
- Microsoft Discovery and Risk Assessment Server: Offers inventory and analysis capabilities to identify and assess risks associated with user-developed Excel and Access tools, improving governance and reducing potential errors.
These server-based solutions complement Spreadsheet Compare and the Inquire add-in, offering enterprise-level control and oversight for spreadsheet and database management.
Conclusion
Comparing two Excel workbooks is essential for maintaining data accuracy and managing changes effectively. Microsoft Spreadsheet Compare provides a dedicated and user-friendly solution for this task. By following this guide, you can confidently compare two excel workbooks, understand the differences highlighted, and leverage these insights for better data management and decision-making. Whether you are auditing financial reports, tracking project data, or ensuring formula integrity, mastering Spreadsheet Compare will significantly enhance your Excel proficiency and data handling capabilities.
