Comparing two Excel files to identify differences can be a crucial task for data analysis, auditing, and version control. Whether you’re trying to track changes, find errors, or merge data, knowing how to effectively Compare Two Xls Files is essential. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose, allowing you to generate detailed reports on the discrepancies between two Excel workbooks. This guide will walk you through the process of using Spreadsheet Compare to efficiently analyze and understand the variations in your Excel files.
What is Spreadsheet Compare?
Spreadsheet Compare is a utility from Microsoft designed to highlight the differences between two Excel workbook files. It’s particularly useful when you need to see what has changed between different versions of a spreadsheet, identify potential errors, or ensure data consistency. This tool is not just about spotting changes in values; it can also detect differences in formulas, macros, and even cell formatting.
Important Note: Spreadsheet Compare is included with specific Microsoft Office suites, namely Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Office, you may need to check if you have access to this tool or consider upgrading to a version that includes it.
Step-by-Step Guide to Comparing Excel Files
Let’s delve into how to use Spreadsheet Compare to effectively compare two xls files. The process is straightforward and can save you significant time compared to manual comparison.
Opening Spreadsheet Compare
First, you need to launch the Spreadsheet Compare application.
- Access via Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: If you don’t see it immediately, simply type “Spreadsheet Compare” in the search bar.
- Launch the Application: Select Spreadsheet Compare from the search results to open the application.
Alongside Spreadsheet Compare, you might also find Microsoft Database Compare, a similar tool for Access databases, which is part of the same Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Selecting the Excel Files to Compare
Once Spreadsheet Compare is open, you can specify the two Excel files you want to compare.
-
Navigate to Compare Files: Click on Home > Compare Files. This action will open the Compare Files dialog box.
-
Choose the First File (Compare): Click the blue folder icon located next to the Compare box. This will allow you to browse your file system. Locate and select the earlier version or the first of the two Excel files you wish to compare. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the Second File (To): Next, click the green folder icon next to the To box. Browse and select the second Excel file you want to compare against the first one. This is often the later version or the file you are comparing to. Click OK after selecting the file.
Tip: You can compare two files even if they have the same name, as long as they are saved in different folders. This is useful when comparing versions of the same report saved in different locations.
Choosing Comparison Options
Before running the comparison, you can customize what aspects of the Excel files you want to analyze.
-
Select Comparison Options: In the left pane of the Compare Files dialog box, you’ll see a list of options. These include Formulas, Macros, Cell Format, and others.
-
Customize Your Comparison: Check the boxes next to the options you want to include in your comparison. For a comprehensive comparison, you can simply click Select All. If you are only interested in specific aspects, such as formula changes, select only the Formulas option.
Running the Comparison
With your files selected and options chosen, you are ready to initiate the comparison process.
-
Start the Comparison: Click OK in the Compare Files dialog box to begin the comparison.
-
Password Prompt (If Necessary): If either of the Excel workbooks is password protected, you might receive an “Unable to open workbook” message. Click OK and enter the password for the protected workbook when prompted. Spreadsheet Compare needs to access the content of the files to perform the comparison.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid. This visual representation makes it easy to identify and understand the differences between your two xls files.
-
Side-by-Side Grid: The results are presented in a side-by-side layout. The workbook on the left corresponds to the “Compare” file (typically the older file), and the workbook on the right is the “To” file (usually the newer one).
-
Worksheet Navigation: If your Excel files contain multiple worksheets, you can navigate between them using the forward and back buttons on the horizontal scroll bar located above the grids. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight different types of changes. The legend in the lower-left pane explains what each color represents. Common color highlights include:
- Green Fill: Indicates “entered values” (non-formula cells) that have been changed.
- Green Font: Also used for “entered values” changes, often in the results list pane below.
- Blue-Green Fill: Signifies changes in “calculated values” (formula cells).
For example, in the image above, changes in entered values (green fill) in columns E have led to changes in calculated values (blue-green fill) in column F. It also highlights a formula correction in cell F5, demonstrating that Spreadsheet Compare not only detects value changes but also formula updates.
-
Resize Cells: If cell contents are truncated due to narrow columns, you can click Resize Cells to Fit to automatically adjust column widths and view the full content.
Excel’s Inquire Add-in as an Alternative
While Spreadsheet Compare is a dedicated tool, Excel itself offers the Inquire add-in, which provides some overlapping functionalities for analyzing and comparing workbooks, although it’s not a direct substitute for Spreadsheet Compare for side-by-side comparisons.
-
Inquire Tab: The Inquire add-in, available in Excel 2013 and later, adds an “Inquire” tab to the Excel ribbon. From this tab, you can analyze workbook structures, explore relationships between cells and worksheets, and even clean up excessive formatting.
-
Using Inquire with Spreadsheet Compare: If you have two workbooks open in Excel and want to use Spreadsheet Compare, you can initiate the comparison directly from the Inquire tab using the “Compare Files” command. This can be a convenient way to access Spreadsheet Compare when working within Excel.
-
Enabling Inquire Add-in: If you don’t see the Inquire tab in Excel, you may need to activate the add-in. Go to File > Options > Add-ins, select “Inquire” from the “COM Add-ins” dropdown, and click “Go…”. Check the box next to “Inquire Add-in” and click “OK”.
Next Steps for Advanced Excel Management
For organizations heavily reliant on Excel and Access for critical operations, Microsoft offers more robust solutions for managing and auditing these files.
-
Microsoft Audit and Control Management Server: This server solution provides advanced change management features for Excel and Access files, offering greater control and oversight for critical spreadsheets and databases.
-
Microsoft Discovery and Risk Assessment Server: Complementing the Audit and Control Management Server, this tool focuses on inventory and analysis, helping organizations understand the landscape of their Excel and Access usage and identify potential risks associated with end-user developed tools.
These server solutions are designed to reduce risks associated with using user-created tools in Excel and Access, providing enterprise-level management and control.
Conclusion
Effectively comparing two xls files is crucial for maintaining data integrity, tracking changes, and ensuring accuracy in your spreadsheets. Microsoft Spreadsheet Compare offers a dedicated and efficient way to perform these comparisons, highlighting differences in values, formulas, and formatting. By following the steps outlined in this guide, you can leverage Spreadsheet Compare to streamline your workflow and gain better insights into your Excel workbooks. Whether you are a data analyst, financial professional, or anyone who works with Excel regularly, mastering the use of Spreadsheet Compare will significantly enhance your productivity and data management capabilities.
