Comparing two Excel files to pinpoint differences can be crucial for various tasks, from identifying data entry errors to tracking changes between versions. Microsoft offers a powerful tool called Spreadsheet Compare to simplify this process. This guide outlines how to use Spreadsheet Compare effectively to identify discrepancies and potential issues in your Excel workbooks.
Using Spreadsheet Compare in Excel
Spreadsheet Compare, a feature available in Office Professional Plus editions and Microsoft 365 Apps for enterprise, provides a comprehensive report highlighting differences between two Excel files.
Launching Spreadsheet Compare
- Open the Start menu and search for “Spreadsheet Compare”.
- Select the Spreadsheet Compare application from the search results. You’ll also find a similar tool for Access databases called Microsoft Database Compare in the same location.
Comparing Two Excel Files Step-by-Step
- Open the Compare Files Dialog: In Spreadsheet Compare, click Home > Compare Files.
- Select Files to Compare:
- Click the blue folder icon next to the Compare box to browse and select the older version of your Excel file.
- Click the green folder icon next to the To box to browse and select the newer version for comparison. You can even compare files with the same name if they are stored in different folders. Web addresses can also be used to select files stored online.
- Specify Comparison Options: Select the elements you want to compare (Formulas, Macros, Cell Format, etc.) by checking the corresponding boxes in the left pane. You can also use the Select All option for a comprehensive comparison.
- Initiate the Comparison: Click OK to start the comparison process. If a password-protected workbook is encountered, you’ll be prompted to enter the password.
- Review Results: The comparison results will be displayed in a two-pane grid, with the older version on the left and the newer version on the right. A detailed pane below the grids highlights specific changes using color-coded indicators.
Understanding the Results
- Color-Coded Differences: Changes are highlighted with different colors, indicating the type of difference (e.g., entered value changes, formula changes, calculated value changes). A legend in the lower-left pane explains the meaning of each color.
- Worksheet Navigation: If your workbooks contain multiple worksheets, use the forward and back buttons on the horizontal scroll bar to navigate between them. Hidden worksheets are also included in the comparison.
- Resizing Cells: If cell contents are not fully visible, click Resize Cells to Fit to adjust the column widths.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analysis capabilities. This add-in enables you to examine workbook relationships, identify cell dependencies, and clean up excess formatting. To activate the Inquire add-in, go to File > Options > Add-Ins and select COM Add-ins from the Manage dropdown menu. Then check the box next to Inquire and click OK.
Conclusion
Effectively comparing Excel files is essential for data accuracy and version control. By leveraging Spreadsheet Compare and the Inquire add-in, you can streamline this process, identify discrepancies quickly, and ensure data integrity within your spreadsheets. For organizations handling sensitive data, consider exploring Microsoft’s more advanced spreadsheet and database management tools for robust change management and risk assessment capabilities.
