Comparing two Excel files to identify differences can be a crucial task for various reasons. Whether you’re auditing data, tracking changes between versions, or ensuring consistency across spreadsheets, knowing how to effectively compare Excel files is a valuable skill. Microsoft Spreadsheet Compare, a tool available with specific versions of Microsoft Office, provides a robust solution to tackle this challenge. This article will guide you through the process of using Spreadsheet Compare to efficiently compare two Excel files and understand the results.
Opening Spreadsheet Compare
First, you need to access the Spreadsheet Compare tool. It’s important to note that this application is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. If you have one of these versions, you can find Spreadsheet Compare by following these steps:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t see it immediately, start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare application to open it.
Once opened, Spreadsheet Compare is ready to help you analyze and compare your Excel files. Alongside Spreadsheet Compare, you might also notice Microsoft Database Compare, a similar tool designed for Access databases, which is also part of the Office Professional Plus suite.
Step-by-Step Guide to Compare Two Excel Workbooks
Comparing two Excel files with Spreadsheet Compare is a straightforward process. Follow these steps to quickly identify the differences between your workbooks:
-
Initiate the Comparison: In Spreadsheet Compare, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the First Excel File (Compare File): Click the blue folder icon located next to the Compare box. This will open a browse window, allowing you to locate and select the earlier version or the first Excel file you wish to compare. You can choose files from your local computer, network locations, or even input a web address if your files are stored online.
-
Select the Second Excel File (To File): Next, click the green folder icon beside the To box. Browse to and select the second Excel workbook that you want to compare against the first one. Click OK to confirm your selection in the browse window and return to the Compare Files dialog box.
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. This is particularly useful when comparing different versions of the same report.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options to refine your comparison. You can select or deselect options such as Formulas, Macros, and Cell Format to specify what aspects of the Excel files you want to compare. For a comprehensive comparison, you can simply click Select All.
-
Run the Comparison: Once you have selected your files and comparison options, click OK to start the comparison process. Spreadsheet Compare will analyze the two Excel workbooks based on your chosen settings.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of your Excel files are password protected. Click OK on the message and you will be prompted to enter the password for the protected workbook. For more details on password handling, refer to how passwords and Spreadsheet Compare work together.
After running the comparison, the results will be displayed in a clear, two-pane grid. The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer one). A detailed pane below the grids provides specific information about the detected differences. Changes are visually highlighted with different colors, each representing a specific type of modification.
Understanding the Comparison Results
The comparison results are presented in an intuitive side-by-side grid, making it easy to spot differences between the two Excel files. Here’s how to interpret the results:
-
Worksheet Comparison: Each worksheet from the compared files is matched and displayed 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 within the grid.
Note: Even hidden worksheets are included in the comparison and will be displayed in the results, ensuring a complete analysis.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight different types of changes. The highlighting can be in the form of cell fill colors or text font colors, depending on the nature of the difference. For instance, cells containing “entered values” (cells with manually inputted data rather than formulas) are typically marked with a green fill color in the grid and a green font in the results list pane. A legend in the lower-left pane clearly explains what each color signifies.
Consider the example illustrated above. In this scenario, the Q4 results in the earlier version of the workbook were preliminary. The updated version incorporates the final figures for Q4 in column E.
In the comparison output, cells E2:E5 in both versions are filled with green, indicating that an entered value has been modified. Consequently, the calculated Year-To-Date (YTD) results in column F have also changed. Cells F2:F4 and E6:F6 are highlighted with a blue-green fill, signifying a change in calculated values.
Furthermore, the calculated value in cell F5 has changed, but the underlying reason is more significant: the formula in the earlier version was incorrect. It was only summing cells B5:D5, omitting the Q4 value. Upon updating the workbook, the formula in F5 was corrected to =SUM(B5:E5).
- Resize Cells for Better Visibility: If cell contents are truncated due to narrow columns, click Resize Cells to Fit. This option adjusts the column widths to ensure that you can see the complete content of each cell, making it easier to review the comparison results.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analytical capabilities. Activating the Inquire add-in in Excel adds an “Inquire” tab to your ribbon. From this tab, you can perform workbook analysis, visualize relationships between cells, worksheets, and even external workbooks, and remove excessive formatting from worksheets to improve performance.
While Spreadsheet Compare is excellent for direct file comparison, the Inquire add-in in Excel offers tools for deeper workbook analysis. If you need to compare two open Excel workbooks using Spreadsheet Compare features directly from Excel, you can utilize the “Compare Files” command within the Inquire add-in.
If you don’t see the Inquire tab in your Excel ribbon, you may need to enable it. See Turn on the Inquire add-in for instructions. To explore the full range of tools available in the Inquire add-in, refer to What you can do with Spreadsheet Inquire.
Next Steps for Enterprise Environments
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, it’s worth considering Microsoft’s enterprise-level management tools. Microsoft Audit and Control Management Server offers advanced change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides functionalities for inventorying and analyzing these files. These tools collectively aim to mitigate risks associated with user-developed tools in Excel and Access, ensuring better control and governance over critical data assets.
Further reading on the overview of Spreadsheet Compare is available at Overview of Spreadsheet Compare.
By utilizing Microsoft Spreadsheet Compare and understanding its features, you can effectively Compare 2 Excel Files, ensuring data accuracy, version control, and efficient auditing processes.
