In today’s data-driven world, working with multiple Excel files or different versions of the same workbook is a common scenario. Whether you’re tracking changes, auditing data, or simply trying to understand the evolution of your spreadsheets, the need to Compare 2 Xls Files effectively is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to simplify this process, allowing you to pinpoint differences and potential issues between Excel workbooks with ease.
Important: Before we dive in, it’s essential to note that Spreadsheet Compare is exclusively available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to utilize this feature.
Accessing Spreadsheet Compare
Opening Spreadsheet Compare is straightforward.
- Navigate to your Start Menu in Windows.
- Look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare,” and the option should appear.
- Click on Spreadsheet Compare to launch the application.
Alongside Spreadsheet Compare, you might also notice Microsoft Database Compare, a similar tool designed for Access databases. Both programs are included in the Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Step-by-Step Guide: How to Compare 2 Excel Workbooks
Let’s walk through the process of comparing two Excel workbooks using Spreadsheet Compare.
-
Initiate the comparison by clicking Home > Compare Files.
This action will open the Compare Files dialog box, your control center for selecting the files to compare.
-
Specify the Original Workbook: Click the blue folder icon located next to the Compare box. This prompts a browse window to open, allowing you to navigate to the location of the earlier version of your Excel workbook. You’re not limited to local files; Spreadsheet Compare also supports workbooks saved on network drives or even accessible via a web address.
Compare Files dialog box in Spreadsheet Compare for selecting Excel workbooks
-
Select the Revised Workbook: Next, click the green folder icon beside the To box. Browse to and select the more recent version of the workbook that you intend to compare against the earlier one. Click OK to confirm your selection.
Tip: Spreadsheet Compare is capable of comparing two files even if they share the same name, provided they are stored in different folders. This is particularly useful when dealing with version-controlled documents.
-
Customize Comparison Options: In the left-hand pane of the Compare Files dialog, you’ll find a list of options that allow you to refine your comparison. You can choose to focus on specific elements such as Formulas, Macros, and Cell Format by checking or unchecking the corresponding boxes. For a comprehensive comparison, simply click Select All.
-
Execute the Comparison: Once you’ve selected your files and comparison options, click OK to initiate the comparison process.
In scenarios where one or both of the workbooks are password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and you will be prompted to enter the password for the protected workbook. For more information on password handling in Spreadsheet Compare, refer to how passwords and Spreadsheet Compare work together.
After processing, the comparison results will be presented in a clear, two-pane grid. The left pane displays the “Compare” file (typically the older version), while the right pane shows the “To” file (usually the newer one). A detailed pane beneath the grids provides specific information about the detected differences. Changes are visually highlighted with distinct colors based on the type of modification.
Deciphering the Comparison Results
Understanding the comparison results is key to leveraging the power of Spreadsheet Compare.
-
Side-by-Side Worksheet View: The tool presents a side-by-side grid, aligning worksheets from each file for direct comparison. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and displayed in the results, ensuring no changes are overlooked.
-
Color-Coded Differences: Differences are highlighted using cell fill colors and text font colors to denote the type of change. For instance, cells containing “entered values” (non-formula cells) that have been modified are marked with a green fill color in the grid and a green font in the results list. The lower-left pane serves as a legend, clearly explaining the meaning of each color code.
Comparison results in Spreadsheet Compare highlighting differences between two Excel workbook versions with color-coded cells
Consider the example where the Q4 results in an earlier workbook version were preliminary. The updated workbook reflects finalized numbers in column E for Q4. In the comparison, cells E2:E5 in both versions are highlighted with green, indicating a change in entered values. Consequently, the calculated values in the YTD column (F2:F4 and E6:F6) are marked with a blue-green fill, signifying changes in calculated values.
Furthermore, cell F5’s calculated result also changed, but crucially, the original formula in the older version was incorrect (=SUM(B5:D5), missing Q4). The updated workbook corrected this to =SUM(B5:E5). This example illustrates how Spreadsheet Compare not only highlights changes but also aids in identifying potential formula errors.
-
Resize Cells for Content Visibility: If cell content is truncated due to narrow columns, simply click Resize Cells to Fit to automatically adjust column widths for optimal readability.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analytical capabilities. Enabling the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can analyze workbooks, visualize relationships between cells, worksheets, and other workbooks, and even remove excessive formatting from worksheets. If you have two workbooks open in Excel and wish to compare them, the “Compare Files” command directly launches Spreadsheet Compare, streamlining your workflow.
If the Inquire tab is not visible in your Excel, you may need to activate it. See Turn on the Inquire add-in for instructions. To explore the full range of tools within the Inquire add-in, refer to What you can do with Spreadsheet Inquire.
Advanced Solutions for Excel and Access Management
For organizations managing mission-critical Excel workbooks and Access databases, Microsoft offers robust management tools. Microsoft Audit and Control Management Server delivers advanced change management features for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis functionalities. These tools are designed to mitigate risks associated with end-user developed tools in Excel and Access environments.
Further explore an Overview of Spreadsheet Compare for a broader understanding of its capabilities and applications.
Conclusion
Spreadsheet Compare is an invaluable asset for anyone who needs to compare 2 xls files and understand the differences between them. Whether you are auditing financial data, tracking project changes, or ensuring data integrity, this tool provides a clear and efficient way to analyze Excel workbooks. By highlighting modifications and potential issues, Spreadsheet Compare empowers you to maintain accuracy and control over your critical spreadsheet data.
