Comparing two Excel files is crucial for identifying discrepancies, tracking changes, and ensuring data accuracy. Whether you’re dealing with different versions of the same workbook or entirely separate files, Microsoft offers robust tools to simplify this process. This article explores how to effectively compare Excel files using Spreadsheet Compare and the Inquire add-in.
Using Spreadsheet Compare for Detailed Analysis
Spreadsheet Compare, available in Office Professional Plus versions and Microsoft 365 Apps for enterprise, provides a comprehensive comparison of two Excel workbooks. Here’s how to use it:
Launching Spreadsheet Compare
- Start Menu: Search for “Spreadsheet Compare” in the Start menu and select the application. You’ll also find the companion program for Access databases, Microsoft Database Compare.
Comparing Two Workbooks
- Initiate Comparison: In Spreadsheet Compare, navigate to Home > Compare Files.
- Select Files: Browse and select the two Excel files you wish to compare using the respective file selection dialogs. You can even compare files stored online via a web address.
- Customize Comparison: Specify the elements you want to compare (Formulas, Macros, Cell Format, etc.) by selecting or deselecting the available options. Selecting “Select All” compares every aspect.
- Execute Comparison: Click OK to initiate the comparison process. If a workbook is password-protected, you’ll be prompted to enter the password.
Interpreting Results
Spreadsheet Compare presents the results in a two-pane grid, displaying the “Compare” file (typically the older version) on the left and the “To” file (typically the newer version) on the right.
- Worksheet Navigation: Each worksheet in one file is compared to its counterpart in the other file. Navigate between worksheets using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison.
- Highlighting Differences: Changes are highlighted with distinct colors based on the type of difference. For instance, 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 clarifies the color coding.
- Adjusting Cell Width: If cell contents are truncated, click Resize Cells to Fit for better visibility.
Leveraging Excel’s Inquire Add-in
Excel 2013 and later versions include the Inquire add-in, which provides additional analysis capabilities.
Activating the Inquire Add-in
If the “Inquire” tab isn’t visible in Excel, you need to activate the add-in through Excel’s options.
Utilizing Inquire Features
The Inquire tab offers tools for:
- Workbook Analysis: Examining workbook structure and relationships.
- Cell Relationships: Identifying dependencies between cells and formulas.
- Worksheet Relationships: Understanding connections between worksheets.
- Workbook Relationships: Analyzing links between different workbooks.
- Clean Excess Cell Formatting: Removing unnecessary formatting.
Conclusion
Microsoft provides powerful tools for comparing Excel files. Spreadsheet Compare allows for detailed analysis of differences between two workbooks, while the Inquire add-in offers broader analytical capabilities. By using these tools, you can easily identify discrepancies, track changes, and improve data accuracy within your Excel files.
