Comparing two Excel workbooks to identify discrepancies is a common task. Whether you’re tracking changes, troubleshooting errors, or merging data, pinpointing differences efficiently is crucial. Microsoft offers a powerful tool called Spreadsheet Compare, specifically designed for this purpose. This article will guide you on how to leverage Spreadsheet Compare to effectively compare Excel workbooks and understand the results.
Using Spreadsheet Compare for Workbook Comparison
Spreadsheet Compare is available in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. To access it, simply type “Spreadsheet Compare” in the Windows Start menu.
Comparing Two Excel Files:
-
Launch Spreadsheet Compare: Open Spreadsheet Compare from the Start menu.
-
Select Files for Comparison: In the application, click “Home” > “Compare Files.” A dialog box will appear.
-
Browse and Choose Files: Click the blue folder icon next to the “Compare” box to select the older version of your workbook. Similarly, click the green folder icon next to the “To” box to select the newer version. You can compare files from various locations, including your computer, network drives, and even web addresses.
-
Specify Comparison Options: Customize your comparison by selecting specific elements to analyze in the left pane. Options include formulas, macros, cell formatting, and more. You can also select “Select All” to compare everything.
-
Initiate Comparison: Click “OK” to begin 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 (older version) on the left and the “To” file (newer version) on the right. A detailed pane below highlights the specific differences.
Understanding the Highlighted Differences:
-
Color-Coded Changes: Changes are indicated by different colors, representing various types of modifications. For instance, entered values (non-formula cells) are highlighted in green, while changes in calculated values are shown in blue-green. A legend in the lower-left pane clarifies the meaning of each color.
-
Worksheet Navigation: If the workbooks contain multiple worksheets, navigate between them using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison.
-
Cell Resizing: If cell contents are not fully visible, click “Resize Cells to Fit” to adjust the column width.
Beyond Spreadsheet Compare: Excel’s Inquire Add-in
Excel also offers the Inquire add-in, providing further analysis capabilities. This add-in allows you to examine workbook relationships, identify cell dependencies, and remove unnecessary formatting. To access these features, enable the Inquire add-in and explore the “Inquire” tab in Excel. You can also launch Spreadsheet Compare directly from the Inquire tab using the “Compare Files” command.
Conclusion
Spreadsheet Compare offers a comprehensive solution for comparing Excel workbooks, enabling you to quickly identify and analyze differences. By understanding its features and interpreting the results effectively, you can streamline your workflow and ensure data accuracy. For organizations with complex spreadsheet management needs, Microsoft provides additional tools like Microsoft Audit and Control Management Server for enhanced control and risk mitigation.
