Finding discrepancies between two Excel spreadsheets can be a tedious task. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to simplify this process. This article will guide you on how to effectively use Spreadsheet Compare to identify differences between two Excel files.
Utilizing Spreadsheet Compare for Efficient Comparison
Spreadsheet Compare, available in Office Professional Plus editions and Microsoft 365 Apps for enterprise, allows you to analyze two Excel workbooks and pinpoint variations in formulas, values, macros, and cell formatting. Here’s a step-by-step guide:
Launching Spreadsheet Compare
- Start Menu: Begin by searching for “Spreadsheet Compare” in the Windows Start Menu and launching the application.
Comparing Two Excel Files
-
Initiate Comparison: In Spreadsheet Compare, navigate to “Home” and select “Compare Files.”
-
Select Files: A dialog box will appear. Click the blue folder icon beside the “Compare” box to choose the older version of your workbook. Similarly, click the green folder icon next to the “To” box to select the newer version for comparison.
-
Comparison Options: Customize your comparison by selecting specific elements to analyze in the left pane. Options include formulas, macros, cell formatting, and more. You can select all options if needed.
-
Execute Comparison: Click “OK” to initiate the comparison process. If a workbook is password protected, you’ll be prompted to enter the password.
Interpreting 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 (usually the newer version). A detailed pane below highlights specific differences.
Color-coded highlighting indicates the type of change:
- Green: Indicates changes in entered values (non-formula cells).
- Blue-Green: Signifies changes in calculated values.
A legend in the lower-left pane clarifies the meaning of each color. Utilize the horizontal scroll bar to navigate between multiple worksheets. If cell contents are not fully visible, click “Resize Cells to Fit.”
Exploring Excel’s Inquire Add-in
Excel 2013 and later versions also offer the Inquire add-in, accessible via the “Inquire” tab (if enabled). This add-in provides further analysis capabilities, including:
- Workbook Analysis: Examine workbook structure and relationships.
- Cell Relationships: Identify dependencies between cells.
- Worksheet Relationships: Understand connections between worksheets.
- Clean Excess Formatting: Remove unnecessary formatting.
The Inquire add-in’s “Compare Files” command can also launch Spreadsheet Compare. For detailed information on enabling and using the Inquire add-in, consult Microsoft’s official documentation.
Conclusion
Spreadsheet Compare is an invaluable tool for efficiently comparing Excel spreadsheets and identifying differences. Its intuitive interface and clear highlighting make it easy to pinpoint discrepancies in values, formulas, and formatting. Leveraging this tool can significantly improve accuracy and save time when working with multiple versions of Excel files. For advanced spreadsheet management and control, consider exploring Microsoft’s Audit and Control Management Server and Discovery and Risk Assessment Server.
