In today’s data-driven world, working with Excel spreadsheets is a common practice. Often, you might find yourself needing to compare two Excel workbooks or different versions of the same file to identify changes, errors, or inconsistencies. Whether you’re auditing financial data, tracking project updates, or simply ensuring data integrity, knowing how to effectively compare Excel files for differences is a crucial skill. Microsoft Spreadsheet Compare is a powerful tool designed exactly for this purpose, allowing you to generate detailed reports on the discrepancies and issues it detects.
Important Note: Spreadsheet Compare is a feature 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. Ensure you have one of these versions to access this functionality.
Launching Spreadsheet Compare
To begin using Spreadsheet Compare, the first step is to open the application.
- Navigate to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t readily see it, start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare application to launch it.
Once launched, Spreadsheet Compare provides a user-friendly interface to facilitate the comparison process. It’s worth noting that alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, which is part of the same Office Professional Plus and Microsoft 365 Apps for enterprise packages.
Step-by-Step Guide: How to Compare Two Excel Workbooks for Differences
Comparing two Excel files using Spreadsheet Compare is a straightforward process. Follow these steps to identify differences between your workbooks:
-
Initiate File Comparison: In the Spreadsheet Compare window, click on Home > Compare Files. This action will open the Compare Files dialog box, which is the starting point for selecting the files you wish to compare.
-
Select the First Workbook (Compare File): In the Compare Files dialog box, locate the Compare box. Click the blue folder icon situated next to it. This will open a browser window allowing you to navigate to and select the earlier version of your Excel workbook. You can choose files stored locally on your computer, on a network drive, or even input a web address if your workbooks are saved on a website.
-
Select the Second Workbook (To File): Next, find the To box in the Compare Files dialog box. Click the green folder icon next to it. Again, a browser window will appear, enabling you to select the second Excel workbook that you want to compare against the first one. This is typically the more recent version of your file. Click OK after selecting the second file.
Pro 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 you have versions of the same workbook stored in different locations.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll see a list of options that define what aspects of the workbooks will be compared. You can customize the comparison by checking or unchecking options like Formulas, Macros, Cell Format, and others. If you want to compare all aspects, simply click Select All. Choose the options that are relevant to your comparison needs.
-
Run the Comparison: Once you have selected both files and chosen your comparison options, click OK to initiate the comparison process. Spreadsheet Compare will then analyze the two workbooks based on your selected criteria.
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 on the message and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files once you provide the correct passwords.
Understanding the Workbook Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid layout.
-
Side-by-Side Grid: The grid is divided into two panes. The left pane displays the workbook you selected as the “Compare” file (typically the older version), and the right pane shows 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 through them using the forward and back buttons located on the horizontal scroll bar at the bottom of the grid.
Important Note: Even if a worksheet is hidden in one or both of the original workbooks, Spreadsheet Compare will still compare it and display it in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to visually represent the differences between the two workbooks. The type of difference is indicated by both the cell fill color and the text font color. For example, cells containing “entered values” (cells that are not formulas) that have been modified are typically highlighted with a green fill color in the side-by-side grid and a green font in the detailed results list below the grids. A legend, usually located in the lower-left pane, clearly explains what each color code signifies, making it easy to interpret the comparison results.
In the example result shown, you can see that cells with direct data inputs (entered values) that have changed are marked with a green background. Cells containing formulas where the calculated results have changed due to upstream changes are marked with a different color, such as blue-green. This visual differentiation helps you quickly grasp the nature of the changes between your Excel files, whether they are direct data modifications or formula-driven updates.
-
Cell Content Display: If some cells are too narrow to fully display their content within the grid, Spreadsheet Compare provides a convenient Resize Cells to Fit option. Clicking this option will automatically adjust the column widths to ensure that you can view the complete content of each cell without having to manually resize columns.
Utilizing Excel’s Inquire Add-in for Workbook Analysis
Beyond Spreadsheet Compare, Excel itself offers the Inquire add-in (available in Excel 2013 and later, depending on your Office version), which provides additional tools for analyzing and understanding Excel workbooks. Once activated, the Inquire add-in adds an “Inquire” tab to the Excel ribbon. From this tab, you can access features to:
- Analyze workbook structure and complexity.
- Visualize relationships between cells, worksheets, and even links to external workbooks.
- Identify potential errors or inconsistencies in formulas.
- Clean up excessive formatting within a worksheet to improve file performance.
While the Inquire add-in doesn’t directly compare two files side-by-side in the same way as Spreadsheet Compare, it offers valuable tools for in-depth analysis of individual workbooks and can be used in conjunction with Spreadsheet Compare for a more comprehensive approach to managing and auditing your Excel data. If you have two Excel workbooks open and want to quickly launch Spreadsheet Compare, you can do so via the “Compare Files” command often found within the Inquire add-in if it’s enabled.
If you do not see the Inquire tab in your Excel ribbon, you may need to activate it. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Excel help or online.
Next Steps for Enterprise-Level Excel Management
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, Microsoft offers more advanced solutions for robust management and control. Microsoft Audit and Control Management Server provides enterprise-grade change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis capabilities. These server-based solutions are aimed at helping larger organizations mitigate risks associated with user-developed tools in Excel and Access by providing enhanced oversight, control, and audit trails.
To further explore the capabilities of Spreadsheet Compare, you can also refer to the Overview of Spreadsheet Compare for additional insights and information.
By using Spreadsheet Compare and understanding its features, you can significantly improve your ability to effectively compare Excel files, identify differences, and maintain data accuracy and integrity in your spreadsheet-based workflows.
