Comparing data in two Excel spreadsheets can be crucial for identifying discrepancies, tracking changes, or ensuring data accuracy. Whether you’re dealing with two versions of the same file or entirely separate workbooks, Microsoft offers a robust tool called Spreadsheet Compare to simplify this process. This guide outlines how to leverage Spreadsheet Compare for efficient data comparison.
Utilizing Spreadsheet Compare for Data Comparison
Spreadsheet Compare is a powerful tool designed to pinpoint differences between two Excel files. It’s available in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise.
Launching Spreadsheet Compare
To open Spreadsheet Compare:
- Click Start and search for Spreadsheet Compare. Select the application from the search results.
- Note that a similar tool, Microsoft Database Compare, is available for Access databases within the same Office suites.
Comparing Two Excel Workbooks
Follow these steps to compare two Excel files:
-
Initiate Comparison: In Spreadsheet Compare, navigate to Home > Compare Files. This opens the Compare Files dialog box.
-
Select Files: Use the folder icons beside the Compare and To boxes to browse and select the two Excel workbooks you wish to compare. You can even compare files with the same name, provided they’re in different folders. You can also input a web address if your files are stored online.
-
Specify Comparison Options: In the left pane, customize your comparison by selecting specific elements like Formulas, Macros, or Cell Format. For a comprehensive comparison, choose Select All.
-
Execute Comparison: Click OK to initiate the comparison process. If a workbook is password-protected, you’ll be prompted to enter the password.
Interpreting the Comparison Results
Spreadsheet Compare presents the results in a two-pane grid, displaying the “Compare” file (usually the older version) on the left and the “To” file (usually the newer version) on the right. A detailed pane below highlights specific differences.
-
Worksheet Comparison: Each worksheet in one file is compared with its counterpart in the other file. Hidden worksheets are also included in the comparison.
-
Highlighting Differences: Changes are indicated by color-coded cell fills and text fonts. A legend in the lower-left pane clarifies the meaning of each color. For instance, green typically signifies changes in entered values.
-
Navigating Worksheets: Use the forward and back buttons on the horizontal scroll bar to navigate between multiple worksheets.
-
Adjusting Cell Width: If cell contents are truncated, click Resize Cells to Fit for better visibility.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, providing further analysis capabilities. This add-in enables you to examine workbook relationships, cell dependencies, and clean up excess formatting. Enable the Inquire add-in to access these features.
Conclusion
Comparing Excel sheet data is made effortless with Spreadsheet Compare. By understanding its features and interpreting the results, you can efficiently identify discrepancies, track changes, and maintain data integrity. For more advanced analysis, consider leveraging Excel’s Inquire add-in.
