Comparing two Excel spreadsheets for differences can be a tedious task, especially with large datasets. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to simplify this process. This guide will walk you through how to use Spreadsheet Compare to efficiently identify discrepancies between two Excel files. We’ll cover everything from opening the application to understanding the results.
Launching Spreadsheet Compare
Spreadsheet Compare is available in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. To open it:
- Click the Start button.
- Type “Spreadsheet Compare” in the search bar.
- 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, requiring the same Office versions.
Comparing Excel Files Step-by-Step
Here’s a detailed guide on how to compare two Excel workbooks using Spreadsheet Compare:
- Select Files for Comparison: In Spreadsheet Compare, click Home > Compare Files. A dialog box will appear.
- Choose the Older Workbook: Click the blue folder icon next to the Compare box. Browse to the location of the older version of your workbook. You can select files from your computer, network, or even enter a web address.
- Choose the Newer Workbook: Click the green folder icon next to the To box. Locate and select the newer version of the workbook for comparison. Click OK. Note that you can compare files with the same name if they are saved in different folders.
- Specify Comparison Options: In the left pane, select the elements you want to compare: Formulas, Macros, Cell Format, or select Select All for a comprehensive comparison.
- Initiate Comparison: Click OK to start the comparison process. If a workbook is password-protected, you’ll be prompted to enter the password.
Interpreting the Comparison Results
The results are displayed in a two-pane grid. The left pane shows the “Compare” (older) file, and the right pane displays the “To” (newer) file. A detailed pane below the grids highlights the differences.
Color-coding indicates the type of change:
- Green: Changes in entered values (non-formula cells).
- Blue-Green: Changes in calculated values.
A legend in the lower-left pane explains the color meanings. You can navigate between multiple worksheets using the forward and back buttons on the horizontal scroll bar. Hidden worksheets are also included in the comparison. If cell contents are not fully visible, click Resize Cells to Fit.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in. This add-in provides deeper analysis capabilities, including:
- Workbook Analysis: Examine workbook structure and relationships.
- Cell Relationships: Trace dependencies between cells.
- Worksheet Relationships: Understand connections between worksheets.
- Clean Excess Formatting: Remove unnecessary formatting.
To activate the Inquire add-in, go to Excel Options and enable it in the Add-Ins section. The “Inquire” tab will then appear in the Excel ribbon. The Compare Files command within the Inquire tab offers another way to launch Spreadsheet Compare.
Conclusion
Spreadsheet Compare is a valuable tool for efficiently comparing Excel data sheets. By understanding its features and following the steps outlined in this guide, you can quickly identify differences and potential errors between two versions of your workbooks, saving time and ensuring data accuracy. For more in-depth analysis, consider exploring the capabilities of the Inquire add-in.
