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 launching the tool to interpreting the results.
Launching Spreadsheet Compare
Spreadsheet Compare is included with specific versions of Microsoft Office: Office Professional Plus 2013, 2016, 2019, and Microsoft 365 Apps for enterprise. To open it:
- Search for the application: Click the Start button and type “Spreadsheet Compare.” Select the application from the search results.
Comparing Two Excel Files
Once you have Spreadsheet Compare open, follow these steps to compare your workbooks:
-
Select the Files: In the Spreadsheet Compare window, click “Home” and then “Compare Files.” A dialog box will appear. Browse to the location of your first spreadsheet (“Compare,” often the older version) and your second spreadsheet (“To,” often the newer version). You can select files from your computer, network, or even a web address.
-
Choose Comparison Options: Before comparing, you can customize the comparison criteria. Select which elements to compare, such as formulas, macros, cell formatting, or simply select “Select All.”
-
Initiate the Comparison: Click “OK” to start the comparison process. If a file is password-protected, you’ll be prompted to enter the password.
Interpreting the Results
Spreadsheet Compare presents the results in a clear, side-by-side view:
-
Side-by-Side Grid: Each worksheet in one workbook is compared to the corresponding worksheet in the other. Navigate between worksheets using the forward and back buttons. Hidden worksheets are also included in the comparison.
-
Color-Coded Differences: Differences are highlighted with different colors depending on the type of change. For example, changes in entered values are typically highlighted in green, while changes in calculated values are often blue-green. A legend at the bottom left of the window clarifies what each color represents.
-
Detailed Change Information: The pane below the grids provides details about each difference, specifying the cell location, the type of change, and the old and new values.
-
Adjusting Cell Width: If the cell contents are not fully visible, click “Resize Cells to Fit” to adjust the column widths.
Excel’s Inquire Add-in
Excel also includes the Inquire add-in, offering additional analysis tools. You can activate this add-in to access features like workbook analysis, cell relationship diagrams, and formatting cleanup. The “Compare Files” command within the Inquire tab can also launch Spreadsheet Compare.
Conclusion
Spreadsheet Compare is a valuable tool for anyone working with Excel, providing a quick and easy way to identify differences between spreadsheets. Its intuitive interface and color-coded highlighting make it simple to pinpoint discrepancies, ensuring data accuracy and facilitating efficient collaboration. Remember that Spreadsheet Compare is only available in specific Office versions, so verify your software edition before attempting to use the tool.
