Comparing two Excel sheets to identify differences can be a common challenge, especially when dealing with multiple versions of workbooks or trying to pinpoint discrepancies. Whether you need to audit data, troubleshoot formulas, or simply understand changes between versions, Microsoft Spreadsheet Compare is a powerful tool designed for this purpose.
Important Note: Microsoft Spreadsheet Compare is exclusively available with specific Microsoft Office suites: 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 utilize this feature.
Opening Spreadsheet Compare
First, you need to launch the Spreadsheet Compare application. Here’s how:
- Go to the Start menu on your Windows system.
- Look for Spreadsheet Compare in your applications list. If you don’t see it immediately, start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare option to open the application.
Alongside Spreadsheet Compare, you might also find Microsoft Database Compare, a similar tool for Access databases, which also requires Office Professional Plus editions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare your Excel files:
-
Navigate to the Home tab and click on Compare Files.
This action will open the Compare Files dialog box. -
Identify the original or older version of your Excel workbook. Click the blue folder icon located next to the Compare field. This will allow you to browse your local files, network locations, or even enter a web address if your workbooks are saved online.
-
Next, specify the revised or newer version of the workbook you want to compare against. Click the green folder icon next to the To field. Browse to the location of this workbook and click OK.
Tip: You can effectively compare two files even if they share the same name, provided they are stored in different folders. This is useful when comparing versions saved in different locations.
-
Customize your comparison by selecting the aspects you want to examine. In the left pane of the Compare Files dialog, you’ll find a list of options such as Formulas, Macros, and Cell Format. Check the boxes next to the elements you wish to include in the comparison report. To compare everything, simply click Select All.
-
Initiate the comparison process by clicking OK.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of your Excel workbooks are password protected. Click OK on the message and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare is designed to work with password-protected files, ensuring you can still analyze and compare them.
After clicking OK, Spreadsheet Compare will analyze the two workbooks based on your selected options and present the results in a clear, side-by-side grid.
Understanding the Comparison Results
The comparison results are displayed in a two-pane grid, making it easy to visually identify differences.
- The left pane displays the workbook you selected as Compare (typically the older version).
- The right pane shows the workbook selected as To (typically the newer version).
- A detailed pane below the grids provides a summary and legend of the changes.
Spreadsheet Compare uses color-coding to highlight different types of changes:
- Cell Fill Color or Text Font Color: Differences are visually marked in cells that have been modified.
- Green Fill Color: Often indicates cells with “entered values” (non-formula cells) that have been changed. These cells will also have a green font in the results list pane.
- Blue-Green Fill Color: Signals that a “calculated value” has changed, usually due to modifications in formulas or input values.
The lower-left pane of the Spreadsheet Compare window includes a legend, clearly explaining what each color code represents.
For example, if you updated quarterly figures in your Excel sheet, Spreadsheet Compare would highlight the cells with the updated “entered values” in green. Consequently, if these changes affected calculated fields like year-to-date totals, those cells with “calculated values” would be highlighted in blue-green.
In cases where cell content is truncated due to column width, click Resize Cells to Fit to automatically adjust column widths and display the full content.
Utilizing Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in. Activating this add-in adds an “Inquire” tab to your Excel ribbon, providing a suite of analysis tools.
From the Inquire tab, you can:
- Analyze workbook structure and complexity.
- Visualize relationships between cells, worksheets, and workbooks.
- Clean up excessive formatting within a worksheet.
- And importantly, access the Compare Files command, which essentially launches Spreadsheet Compare from within Excel.
If you don’t see the Inquire tab, you may need to activate it through Excel Options > Add-Ins > Manage: COM Add-ins > Go > check “Inquire” > OK.
Next Steps
For organizations managing critical Excel workbooks or Access databases, consider exploring Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These server solutions offer advanced change management, inventory, and analysis features, helping to mitigate risks associated with end-user developed tools in Excel and Access environments.
Spreadsheet Compare is a valuable tool for anyone needing to effectively compare Excel sheets and understand the differences between versions. By following these steps, you can efficiently audit your spreadsheets, ensure data accuracy, and streamline your workflow.
