Do you need to compare two Excel workbooks, identify discrepancies between versions, or pinpoint potential errors like broken formulas or manually entered totals? Microsoft Spreadsheet Compare provides a detailed report highlighting differences and issues within your spreadsheets. This article guides you through utilizing Spreadsheet Compare effectively.
Selecting files to compare in Spreadsheet Compare
Accessing Spreadsheet Compare
Spreadsheet Compare is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. To open it:
- Click Start and search for Spreadsheet Compare.
- Select the Spreadsheet Compare application.
Note: A companion program, Microsoft Database Compare, is also available for Access databases within the same Office suites.
Comparing Two Excel Workbooks
Follow these steps to compare two Excel files:
- Launch Spreadsheet Compare and click Home > Compare Files.
- Use the blue folder icon beside the Compare box to navigate to the older version of your workbook. You can select files from your computer, network, or even enter a web address.
- Use the green folder icon next to the To box to locate the newer workbook for comparison. Click OK.
- In the left pane, select the elements you wish to compare (Formulas, Macros, Cell Format, etc.). Selecting Select All compares everything.
- Click OK to initiate the comparison.
If a workbook is password protected, you’ll be prompted to enter the password.
Understanding the Comparison Results
Spreadsheet Compare presents the results in a two-pane grid:
- The left pane displays the “Compare” file (typically the older version).
- The right pane displays the “To” file (typically the newer version).
A detailed pane below the grids describes the specific differences. Changes are color-coded for clarity:
Example of comparison results highlighting changes
-
Green highlights indicate changes in entered values.
-
Blue-green highlights signify changes in calculated values.
-
A legend in the lower-left pane explains the color coding.
If cell contents are not fully visible, click Resize Cells to Fit. Hidden worksheets are also included in the comparison.
Leveraging Excel’s Inquire Add-In
Excel 2013 and later versions include the Inquire add-in, offering additional analysis capabilities. To enable it:
- Go to File > Options > Add-Ins.
- In the Manage box (at the bottom), select COM Add-ins and click Go.
- Check the Inquire box and click OK.
The Inquire tab provides tools to analyze workbook relationships, cell dependencies, and clean excess formatting. You can launch Spreadsheet Compare directly from the Inquire tab using the Compare Files command. For more detailed information, consult Microsoft’s documentation on the Inquire add-in.
Conclusion
Microsoft Spreadsheet Compare is a valuable tool for identifying discrepancies and potential errors in Excel workbooks. Utilizing its features and understanding the results allows for efficient analysis and informed decision-making. For organizations with critical spreadsheet data, consider exploring Microsoft’s spreadsheet and database management tools for enhanced control and risk mitigation.
