Comparing two Excel files to identify differences is crucial for various tasks, from tracking changes to troubleshooting errors. Microsoft offers a powerful tool called Spreadsheet Compare to streamline this process. This article provides a comprehensive guide on how to leverage Spreadsheet Compare to effectively compare Excel files and understand the results.
Launching Spreadsheet Compare
Spreadsheet Compare is included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. To open it:
- Type “Spreadsheet Compare” in the Windows search bar.
- Select the Spreadsheet Compare application from the search results.
Comparing Two Excel Workbooks
Follow these steps to compare two Excel files:
- Open Spreadsheet Compare: Launch the application as described above.
- Select Files: In Spreadsheet Compare, click “Home” > “Compare Files”. A dialog box will appear.
- Choose Files to Compare:
- Click the blue folder icon next to the “Compare” box to select the older version of your workbook.
- Click the green folder icon next to the “To” box to select the newer version of your workbook.
- You can use files stored locally, on a network, or even from a website by entering the web address.
- Select Comparison Options: In the left pane, choose the specific elements you want to compare, such as Formulas, Macros, Cell Format, or simply “Select All.”
- Initiate Comparison: Click “OK” to begin the comparison process. If a file is password-protected, you’ll be prompted to enter the password.
Interpreting the Comparison Results
Spreadsheet Compare presents the results in a clear, two-pane grid:
- Side-by-Side Comparison: The left pane displays the “Compare” (older) file, and the right pane displays the “To” (newer) file. Each worksheet is compared individually. Use the horizontal scroll bar to navigate between worksheets.
- Highlighting Differences: Changes are highlighted with different colors depending on the type of difference. For example, changes in entered values are highlighted in green, while changes in calculated values are highlighted in blue-green. A legend in the lower-left pane explains the color coding.
- Detailed Results: A pane below the grids provides more specific details about each difference.
Leveraging Excel’s Inquire Add-in
Excel also offers the Inquire add-in, providing additional analysis capabilities. This add-in allows you to:
- Analyze workbook structure and relationships between cells and worksheets.
- Identify and clean excess formatting.
- Access Spreadsheet Compare directly through the “Compare Files” command.
Conclusion
Spreadsheet Compare provides a comprehensive solution for comparing Excel files, enabling you to identify differences quickly and accurately. By understanding its features and how to interpret the results, you can streamline your workflow and improve data accuracy. For organizations with complex spreadsheet management needs, Microsoft offers more advanced tools like Microsoft Audit and Control Management Server for robust change management and risk assessment.
