Comparing two Excel spreadsheets to identify differences can be a tedious task, especially with large datasets. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to streamline this process. This article provides a comprehensive guide on how to use Spreadsheet Compare to efficiently compare Excel files and pinpoint discrepancies. We’ll cover everything from launching the tool to interpreting 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 and type “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. Both require the professional versions of Office or a Microsoft 365 Apps for enterprise subscription.
Comparing Excel Files Step-by-Step
Here’s a detailed walkthrough of how to compare two Excel workbooks:
-
Open the Compare Files Dialog Box: In Spreadsheet Compare, click Home > Compare Files.
-
Select the Files to Compare: Click the blue folder icon next to the Compare box to select the older version of your workbook. Click the green folder icon next to the To box to choose the newer version. You can compare files from your computer, network, or even a web address. Files with the same name but in different folders can also be compared.
-
Choose Comparison Options: Select the elements you want to compare, such as Formulas, Macros, or Cell Format. You can individually select options or choose Select All for a comprehensive comparison.
-
Initiate the Comparison: Click OK to start the comparison process. If a workbook is password protected, you’ll be prompted to enter the password.
Understanding the Comparison Results
Spreadsheet Compare presents the results in a two-pane grid. The left pane displays the “Compare” (older) file, and the right pane shows the “To” (newer) file. A detailed list of differences appears in a pane below the grids.
-
Worksheet Comparison: Each worksheet in one file is compared to its corresponding worksheet in the other file. Hidden worksheets are also included in the comparison. Navigate between worksheets using the forward and back buttons on the horizontal scroll bar.
-
Highlighting Differences: Changes are highlighted with different colors based on the type of difference. For example, 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 explains the meaning of each color.
-
Resizing Cells: If cell contents are not fully visible, click Resize Cells to Fit to adjust the column widths.
Leveraging Excel’s Inquire Add-in
Excel also offers the Inquire add-in, which provides additional analysis capabilities. You can activate this add-in to access features such as workbook analysis, cell relationship visualization, and formatting cleanup. The Inquire tab includes the Compare Files command, allowing you to launch Spreadsheet Compare directly from Excel. To enable the Inquire add-in, go to File > Options > Add-Ins and select Inquire.
Conclusion
Spreadsheet Compare is an invaluable tool for efficiently comparing Excel sheets and identifying differences. By understanding its features and how to interpret the results, you can significantly improve your workflow and ensure data accuracy. For organizations with critical spreadsheet data, consider exploring Microsoft’s more advanced spreadsheet and database management tools for enhanced control and risk mitigation.
