Comparing Excel files, especially when dealing with multiple versions or large datasets, can be a daunting task. Whether you need to identify changes, errors, or inconsistencies between two workbooks, manually sifting through data is time-consuming and prone to mistakes. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare, designed to streamline this process and highlight the differences that matter.
Important Note: Spreadsheet Compare is a feature included in 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 access this functionality.
How to Open Spreadsheet Compare
To begin using Spreadsheet Compare, the first step is to locate and open the application.
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t immediately see it, start typing “Spreadsheet Compare,” and it should appear as a search result.
- Click on Spreadsheet Compare to launch the application.
It’s worth noting that alongside Spreadsheet Compare, Microsoft also provides Database Compare for Access databases, which functions similarly for comparing Access files. This tool is also part of the Office Professional Plus suites and Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, you can easily initiate the comparison process:
-
Initiate File Comparison: Click on the Home tab, and then select Compare Files. This action will open the Compare Files dialog box.
-
Select the Older Workbook (Compare File): In the Compare Files dialog box, locate the Compare box. Click on the blue folder icon next to it. This will allow you to browse your file system and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Select the Newer Workbook (To File): Next, find the To box and click on the green folder icon adjacent to it. Browse to and select the more recent version of the Excel workbook that you wish to compare against the older version. Click OK to confirm your selection.
Tip: You can effectively compare two files that have the same name as long as they are saved in different folders. This is particularly useful when comparing versions saved in different project directories or date-stamped folders.
-
Choose Comparison Options: In the left-hand pane of the Compare Files dialog, you’ll see a list of options that determine what aspects of the workbooks will be compared. You can customize your comparison by checking or unchecking options such as Formulas, Macros, and Cell Format. If you want a comprehensive comparison, simply click Select All.
-
Run the Comparison: After selecting your desired options, click OK to initiate the comparison process. Spreadsheet Compare will then analyze both workbooks based on your chosen settings.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of the Excel files are password protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. For more information on password handling, refer to how passwords and Spreadsheet Compare work together.
Once the comparison is complete, the results will be displayed in a clear, two-pane grid, making it easy to review the differences between your Excel files.
Understanding the Comparison Results
The Spreadsheet Compare interface is designed for intuitive interpretation of the differences it finds:
-
Side-by-Side Grid View: The comparison results are presented in a side-by-side grid. The workbook on the left represents the “Compare” file (typically the older version), and the workbook on the right is the “To” file (usually the newer one). Each worksheet within the workbooks is compared against its counterpart in the other file. If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons located on the horizontal scroll bar.
Note: Even worksheets that are hidden in the Excel files are included in the comparison and displayed in the results. This ensures a complete and thorough analysis.
-
Color-Coded Highlighting: Differences are visually highlighted using distinct colors within the grid. The type of difference determines the color used, making it easy to quickly identify the nature of each change. For instance, cells containing “entered values” (cells with manually inputted data rather than formulas) are typically highlighted with a green fill color in the side-by-side grid and a green font in the details pane below. A legend in the lower-left pane clearly explains the meaning of each color code.
In the example illustrated, the Q4 results in the earlier workbook version were preliminary. The updated version incorporates the finalized figures in column E for Q4.
Examining the comparison results, you’ll notice that cells E2:E5 in both versions are filled with green, indicating that entered values have been modified. Consequently, the calculated values in the YTD column have also changed – cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying alterations in calculated values.
Furthermore, the calculated result in cell F5 has changed, but crucially, in the older version, the formula in F5 was incorrect (it only summed B5:D5, omitting Q4’s value). In the updated workbook, the formula in F5 was corrected to
=SUM(B5:E5). Spreadsheet Compare effectively highlights not just data changes, but also formula discrepancies and corrections. -
Resize Cells for Better Viewing: If cell contents are truncated due to column width, simply click Resize Cells to Fit. This option automatically adjusts column widths to ensure that all cell content is visible, enhancing readability and analysis.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analytical capabilities. Once activated, the Inquire add-in adds an “Inquire” tab to your Excel ribbon.
From the Inquire tab, you can perform in-depth workbook analysis, visualize relationships between cells, worksheets, and even external workbooks, and remove excessive formatting that can bloat file size and hinder performance. If you have two Excel workbooks open and wish to compare them using Spreadsheet Compare, you can conveniently launch it directly using the “Compare Files” command within the Inquire add-in.
If the Inquire tab is not visible in your Excel ribbon, you may need to enable it. See Turn on the Inquire add-in for instructions on how to activate it. To learn more about the full suite of tools available within the Inquire add-in, explore What you can do with Spreadsheet Inquire.
Next Steps for Advanced Excel Management
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers comprehensive inventory and analysis capabilities. These server solutions are aimed at mitigating risks associated with end-user developed tools in Excel and Access, ensuring data integrity and control in critical business processes.
Further reading on Spreadsheet Compare is available in the Overview of Spreadsheet Compare.
