Comparing two Excel files to pinpoint differences can be a crucial task for various reasons, from identifying data entry errors to tracking changes between versions. Thankfully, Microsoft offers a robust solution called Spreadsheet Compare, designed specifically for this purpose. This article will guide you through the process of using Spreadsheet Compare to effectively compare Excel files and highlight discrepancies.
How to Use Spreadsheet Compare in Excel
Spreadsheet Compare is a powerful tool included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. It allows you to compare two Excel workbooks and identify differences in formulas, values, macros, and even cell formatting.
Launching Spreadsheet Compare
To open Spreadsheet Compare:
- Click the Start button.
- Search for “Spreadsheet Compare” and select the application.
Comparing Two Excel Files
Follow these steps to compare your Excel files:
-
In Spreadsheet Compare, click Home > Compare Files.
-
Browse to the location of the older version of your workbook in the Compare dialog box and select it. You can also enter a web address if your file is stored online.
-
Similarly, browse and select the newer version of your workbook in the To dialog box.
-
Customize your comparison by selecting the elements you want to compare in the left pane, such as Formulas, Macros, or Cell Format. Selecting Select All will compare everything.
-
Click OK to initiate the comparison.
If a workbook is password-protected, you’ll be prompted to enter the password.
Understanding the Results
Spreadsheet Compare presents the comparison results in a two-pane grid. The left pane displays the “Compare” file (typically the older version), while the right pane shows the “To” file (typically the newer version). A detailed list of differences appears in a pane below the grids.
Color-coded highlighting makes it easy to spot changes:
- Green: Entered values (non-formula cells) that have changed.
- Blue-Green: Calculated values that have changed.
- Other colors represent different types of changes, and a legend in the lower-left pane clarifies what each color signifies.
You can navigate between different worksheets within the compared workbooks using the forward and back buttons on the horizontal scroll bar. If cells are too narrow to display their contents fully, click Resize Cells to Fit.
Beyond Comparing: Excel’s Inquire Add-in
Besides Spreadsheet Compare, Excel offers the Inquire add-in, providing additional analysis capabilities. This add-in enables you to examine workbook relationships, track cell precedents and dependents, and clean up excess formatting. To activate the Inquire add-in, you might need to enable it in Excel’s options.
Conclusion: Streamlining Excel File Comparison
Spreadsheet Compare offers a user-friendly yet powerful way to compare Excel files and highlight differences, simplifying tasks like error checking, version control, and data analysis. Leveraging this tool allows for efficient identification and resolution of discrepancies within your spreadsheets. Combined with the Inquire add-in, Excel provides a comprehensive suite for in-depth spreadsheet analysis.
