Comparing two Excel files to identify differences can be a crucial task for professionals in various fields. Whether you are auditing financial data, tracking changes in project spreadsheets, or merging data from different sources, knowing how to efficiently compare Excel files is an invaluable skill. Microsoft Spreadsheet Compare, a powerful tool included with certain versions of Microsoft Office, simplifies this process, allowing you to pinpoint discrepancies and understand modifications made between workbooks or different versions of the same workbook. This guide will walk you through how to use Spreadsheet Compare to effectively analyze and compare your Excel files, ensuring data accuracy and streamlining your workflow.
Understanding Microsoft Spreadsheet Compare
Microsoft Spreadsheet Compare is a utility designed to compare Excel workbook files, providing a detailed report on their differences. It is particularly useful for identifying changes in data, formulas, macros, and even formatting. However, it’s important to note that Spreadsheet Compare is not universally available across all Office versions. It is included with:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
Before proceeding, ensure you have one of these versions installed to access Spreadsheet Compare. Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, found within the same suite of tools for users with the appropriate Office licenses.
Step-by-Step Guide: Comparing Two Excel Workbooks
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare. Follow these steps to effectively identify differences between your files:
1. Open Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application.
- For Windows 10 and later versions: Click the Start button, then scroll through your applications list to find Spreadsheet Compare. Alternatively, simply type “Spreadsheet Compare” in the search bar and select the application from the results.
Once opened, Spreadsheet Compare presents a straightforward interface ready for you to begin your file comparison.
2. Select Files for Comparison
With Spreadsheet Compare open, you need to specify the Excel files you wish to compare.
-
Click Home > Compare Files. This action opens the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. This prompts a browse window to open, allowing you to navigate to and select the earlier version or the first Excel file you want to compare. You can choose files from your local computer, network locations, or even enter a web address if your files are stored online.
-
Next, click the green folder icon located next to the To box. Again, a browse window will appear. This time, locate and select the later version or the second Excel file you are comparing against the first one. Click OK after selecting your file.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders.
3. Choose Comparison Options
Before running the comparison, you can customize what aspects of the Excel files Spreadsheet Compare should focus on.
-
In the left pane of the Compare Files dialog box, you will see a list of options. These options include Formulas, Macros, Cell Format, and others.
-
Check the boxes next to the options that are relevant to your comparison. For a comprehensive comparison, you can simply check Select All. This ensures that all aspects of the workbooks, from formulas to formatting, are included in the comparison report.
4. Run the Comparison
Once you have selected your files and chosen your comparison options, you are ready to initiate the comparison process.
- Click OK in the Compare Files dialog box. Spreadsheet Compare will then analyze the two Excel files based on your selected options and generate a comparison report.
5. Handling Password-Protected Files
During the comparison, you might encounter a message: “Unable to open workbook.” This typically indicates that one or both of the Excel workbooks are password protected.
- If this message appears, click OK. Spreadsheet Compare will then prompt you to enter the password for the protected workbook.
- Enter the correct password to allow Spreadsheet Compare to access and analyze the file.
For more information on managing passwords with Spreadsheet Compare, you can refer to Microsoft’s documentation on password handling for file analysis and comparison.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a user-friendly, two-pane grid.
-
Side-by-Side Grid: The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer version). Each worksheet in the files is compared against its counterpart in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to indicate different types of changes between the files. The specific colors and their meanings are explained in a legend located in the lower-left pane. For instance, cells containing “entered values” (values not derived from formulas) that have changed are often highlighted with a green fill color in the grid and a green font in the results list. Cells with changed calculated values might be highlighted with a different color, such as blue-green.
Example Interpretation: Imagine you are comparing sales data between two quarterly reports. If the results show green highlighting in specific cells for sales figures in the latest report compared to the older one, it indicates that these were directly entered values that have been updated. If you see blue-green highlighting in cells containing year-to-date totals, it suggests that these calculated values have changed because the underlying input values were modified. Spreadsheet Compare not only highlights the changes but also helps you understand the nature of these changes, whether they are in entered values, formulas, or formatting.
- Resize Cells: If cell contents are truncated due to narrow columns, click Resize Cells to Fit to expand column widths and view the complete data within the cells.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers an “Inquire” add-in, available in Excel 2013 and later, which provides a range of analytical tools. To use it, you may need to activate it first.
- Enabling Inquire Add-in: If you don’t see the “Inquire” tab in your Excel ribbon, you can enable it by going to File > Options > Add-ins. In the “Manage” dropdown at the bottom, select “COM Add-ins” and click “Go…”. Then, check the box next to “Inquire” and click “OK”. This will add the “Inquire” tab to your Excel ribbon.
The Inquire add-in offers features like workbook analysis, relationship visualization between cells and worksheets, and tools to clean up excessive formatting. Notably, within Excel, the Inquire add-in also provides a direct way to launch Spreadsheet Compare. If you have two Excel workbooks open that you wish to compare, you can go to the Inquire tab and use the Compare Files command to initiate the comparison directly from within Excel, seamlessly integrating the Spreadsheet Compare functionality into your Excel workflow.
To learn more about the capabilities of the Inquire add-in, explore Microsoft’s guide on what you can do with Spreadsheet Inquire.
Next Steps for Advanced Excel Management
For organizations heavily reliant on Excel workbooks and Access databases for critical operations, consider exploring Microsoft’s comprehensive management tools. Microsoft Audit and Control Management Server offers robust change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis capabilities, designed to mitigate risks associated with using end-user developed tools in Excel and Access environments. These server solutions provide an enterprise-grade approach to managing and securing your important spreadsheet and database assets.
Furthermore, for a broader overview of Spreadsheet Compare and its capabilities, you can refer to Microsoft’s general overview documentation on Spreadsheet Compare.
By mastering Spreadsheet Compare and understanding its integration with Excel’s Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the integrity of your Excel data. This tool is an essential asset for anyone working with multiple Excel files or versions, providing clarity and control over spreadsheet changes and differences.
