Finding discrepancies between two Excel files can be a tedious task. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to simplify this process. This guide provides a comprehensive walkthrough on how to leverage Spreadsheet Compare to efficiently identify differences between two Excel workbooks.
Using Spreadsheet Compare for Excel File Comparison
Spreadsheet Compare, available in Office Professional Plus editions and Microsoft 365 Apps for enterprise, allows you to pinpoint variations in formulas, values, macros, and cell formatting between two Excel files. Here’s how to use it:
Launching Spreadsheet Compare
- Start Menu: Search for “Spreadsheet Compare” in the Windows Start Menu and select the application.
Comparing Two Excel Files
-
Open the Compare Files Dialog: In Spreadsheet Compare, navigate to Home > Compare Files.
-
Select Files to Compare: Click the blue folder icon next to the “Compare” box to choose the older version of your Excel file. Click the green folder icon next to the “To” box to select the newer version. You can select files from your computer, network, or even a web address.
-
Specify Comparison Options: In the left pane, select the elements you want to compare (Formulas, Macros, Cell Format, etc.). You can choose “Select All” to compare every aspect.
-
Initiate Comparison: Click OK to begin the comparison process. If a file is password-protected, you’ll be prompted to enter the password.
Interpreting Comparison Results
The comparison results are displayed in a two-pane grid. The left pane shows the “Compare” file (older version), and the right pane displays the “To” file (newer version). A detailed pane below highlights the specific differences. Color-coding indicates the type of change:
- Green Fill: Indicates a change in entered values (non-formula cells).
- Blue-Green Fill: Represents a change in calculated values.
A legend in the lower-left pane clarifies the meaning of each color. 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 feature an “Inquire” add-in with tools for in-depth workbook analysis. This add-in enables you to:
- Examine cell relationships within and across workbooks.
- Identify and remove unnecessary formatting.
- Access Spreadsheet Compare directly.
To activate the Inquire add-in, go to File > Options > Add-Ins > Manage: COM Add-ins > Go and check the box for “Inquire.”
Conclusion
Spreadsheet Compare offers a robust solution for comparing Excel files and identifying discrepancies. By understanding its features and utilizing the Inquire add-in, you can significantly streamline the process of data validation and analysis. For organizations handling sensitive data, Microsoft offers more advanced tools like Audit and Control Management Server and Discovery and Risk Assessment Server for enhanced control and risk mitigation.
