Comparing data across two spreadsheets is a common task, whether you’re checking for discrepancies between versions, identifying manual errors, or verifying formula accuracy. Microsoft provides a powerful tool called Spreadsheet Compare to streamline this process. This article will guide you on how to use Spreadsheet Compare effectively.
Utilizing Spreadsheet Compare for Data Comparison
Spreadsheet Compare, a feature available in specific Office versions (Office Professional Plus 2013, 2016, 2019, and Microsoft 365 Apps for enterprise), allows you to analyze two Excel workbooks and pinpoint differences.
Launching Spreadsheet Compare
To open Spreadsheet Compare:
-
Navigate to the Start screen and click Spreadsheet Compare. If the icon isn’t readily visible, start typing “Spreadsheet Compare” in the search bar and select the appropriate option.
-
Note that a companion program, Microsoft Database Compare, exists for Access databases and requires the same Office versions.
Comparing Two Excel Files
Follow these steps to compare your workbooks:
-
Initiate the comparison by clicking Home > Compare Files within Spreadsheet Compare. This will open the Compare Files dialog box.
-
Use the blue folder icon next to the Compare box to locate and select the older version of your workbook. You can browse your computer, network, or even enter a web address.
-
Similarly, use the green folder icon beside the To box to select the newer version for comparison. Click OK after selecting both files.
-
Customize the comparison by selecting specific elements like Formulas, Macros, or Cell Format in the left pane. Alternatively, choose Select All to compare every aspect.
-
Click OK to execute the comparison. If a workbook is password-protected, you’ll be prompted to enter the password.
Interpreting the Results
Spreadsheet Compare presents the results in a two-pane grid, with the older version on the left and the newer version on the right. A detailed pane below highlights the differences using color-coded indicators.
-
Each worksheet is compared individually, accessible via navigation buttons. Even hidden worksheets are included in the analysis.
-
Color-coding signifies different types of changes. For instance, green highlights changes in entered values, while blue-green indicates changes in calculated values. A legend clarifies the color meanings.
-
If cell contents are truncated, use the Resize Cells to Fit option.
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 in-depth workbook inspection, reveals cell relationships, and cleans up excess formatting. Activate the Inquire add-in to access these features.
Conclusion
Spreadsheet Compare provides a robust solution for comparing data in two Excel workbooks. By understanding its features and interpreting the results effectively, you can efficiently identify discrepancies, track changes, and ensure data accuracy. For organizations dealing with critical spreadsheets, consider exploring Microsoft’s advanced spreadsheet and database management tools for enhanced control and risk mitigation.
