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 ensuring data integrity, knowing how to effectively compare Excel files is essential. Microsoft Spreadsheet Compare is a powerful tool designed for this purpose, allowing you to generate reports on the discrepancies and potential issues between two workbooks or different versions of the same workbook. This guide will walk you through the process of using Spreadsheet Compare to effectively analyze and compare your Excel files.
Understanding Spreadsheet Compare and its Availability
Microsoft Spreadsheet Compare is a utility specifically designed to compare Excel files. It is particularly useful for identifying differences in formulas, values, formatting, and more. It goes beyond simple visual inspection and provides a detailed report of changes, making it an invaluable tool for ensuring accuracy and consistency in your spreadsheets.
Important Availability Note: It’s crucial to note that Spreadsheet Compare is not a standalone application available with all versions of Microsoft Office or Microsoft 365. It is specifically included with:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you are using a standard version of Office or Microsoft 365, you may not have access to Spreadsheet Compare. Ensure you have one of the compatible versions to utilize this tool.
Step-by-Step Guide to Comparing Two Excel Files
Here’s a detailed walkthrough on how to use Spreadsheet Compare to analyze the differences between two Excel workbooks:
1. Opening Spreadsheet Compare
The first step is to locate and open the Spreadsheet Compare application.
-
For Windows:
- Go to the Start Menu.
- Look for Spreadsheet Compare in the application list. If you don’t see it immediately, start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare option to launch the application.
2. Selecting Files for Comparison
Once Spreadsheet Compare is open, you need to specify the two Excel files you want to compare.
-
Navigate to the Home tab in the Spreadsheet Compare window.
-
Click on the Compare Files button. This will open the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. Browse to and select the location of the first Excel workbook (typically the older version or the file you want to consider as the baseline). You can choose files from your local computer, network drives, or even enter a web address if your files are saved online.
-
In the To box, click the green folder icon. Browse to and select the second Excel workbook you want to compare against the first one (typically the newer version or the file you are comparing to). Click OK after selecting the file.
Tip: You can effectively compare two files even if they have the same name, as long as they are stored in different folders. This is particularly useful when comparing versions of the same report saved in different directories.
3. Choosing Comparison Options
Before running the comparison, you can customize what aspects of the Excel files Spreadsheet Compare should analyze.
-
In the left pane of the Compare Files dialog box, you will see a list of options. These options allow you to specify what elements to include in the comparison report.
-
Select or deselect the checkboxes next to the options based on your needs. Common options include:
- Formulas: Compares the formulas used in cells.
- Values: Compares the calculated or entered values in cells.
- Cell Format: Compares formatting attributes such as font, color, and cell styles.
- Macros: Compares VBA macros included in the workbooks.
- Comments: Compares comments added to cells.
- Hidden Sheets: Includes hidden worksheets in the comparison.
- Select All: Checks all options for a comprehensive comparison.
-
After choosing your desired options, click OK to initiate the comparison process.
4. Interpreting Comparison Results
After clicking OK, Spreadsheet Compare will analyze the two Excel files based on your selected options and display the results in a two-pane grid.
-
Side-by-Side Grid: The results are presented in a split-screen view.
- The left pane displays the workbook you selected in the Compare box (typically the older file).
- The right pane shows the workbook from the To box (typically the newer file).
- Each workbook is displayed as a grid, mirroring the worksheet structure of the Excel files.
- If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons on the horizontal scroll bar located below the grid. Even hidden worksheets are included in the comparison and results.
-
Color-Coded Highlights: Differences between the files are highlighted using colors to denote the type of change.
- Green Fill: Often indicates “entered values” (cells containing manually inputted data rather than formulas) that have changed.
- Green Font: Also used for entered values, especially in the detail pane below the grids.
- Blue-Green Fill: Typically signifies “calculated values” that have changed. This usually happens when the input values (entered values) that formulas depend on are modified.
-
Details Pane: Below the side-by-side grid, a details pane provides a legend explaining the color codes and may offer more specific information about the changes.
Example of Result Interpretation:
Consider a scenario where you are comparing quarterly sales reports. In an earlier version of a workbook, the Q4 figures were preliminary. The latest version contains finalized Q4 numbers.
-
In the comparison results, cells in the columns containing Q4 data (e.g., column E) might be highlighted with a green fill in both versions. This indicates that the “entered values” (the sales figures) have been changed between the versions.
-
Consequently, columns that contain year-to-date (YTD) totals or other calculations dependent on the Q4 figures (e.g., column F) might be highlighted with a blue-green fill. This shows that the “calculated values” have changed because the input values were updated.
-
Spreadsheet Compare can also identify formula errors. For instance, if a formula in the earlier version was incorrect (e.g., summing only Q1-Q3 data instead of Q1-Q4), and it was corrected in the newer version, Spreadsheet Compare would highlight this formula change as well.
-
Resizing Cells: If cell contents are truncated due to narrow columns, click on Resize Cells to Fit option, usually found in the ribbon or context menu, to expand column widths and view the full content.
Leveraging Excel’s Inquire Add-in for Advanced Analysis
Beyond Spreadsheet Compare, Excel offers the Inquire add-in (available in some versions of Excel, often Professional Plus and Microsoft 365 Apps for enterprise) which provides additional analytical capabilities.
- Inquire Tab: Once enabled, the Inquire add-in adds an “Inquire” tab to the Excel ribbon.
- Workbook Analysis: From the Inquire tab, you can:
- Analyze Workbook: Get a comprehensive overview of a workbook’s structure, formulas, potential errors, and inconsistencies.
- Workbook Relationships: Visualize the connections between cells, worksheets, and even external workbooks. This is helpful for understanding data flow and dependencies.
- Clean Excess Cell Formatting: Remove unnecessary formatting that can increase file size and potentially cause compatibility issues.
- Compare Files within Excel: The Inquire add-in also provides a Compare Files command directly within Excel, which launches Spreadsheet Compare. If you have two workbooks already open in Excel, this can be a quick way to start the comparison.
If you don’t see the Inquire tab, you may need to enable it in Excel’s add-in settings. (See: Turn on the Inquire add-in). For more in-depth information on the Inquire add-in’s features, refer to: What you can do with Spreadsheet Inquire.
Next Steps for Enterprise Environments
For organizations that rely heavily on Excel for critical operations and data management, consider implementing more robust solutions like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These enterprise-level tools offer advanced change management, inventory, and analysis features for Excel and Access files, helping to mitigate risks associated with end-user developed tools and ensure data governance.
Conclusion
Spreadsheet Compare is a valuable tool for anyone who needs to compare Excel files and understand the differences between them. By following this guide, you can effectively use Spreadsheet Compare to identify changes, audit spreadsheets, and maintain data accuracy. Whether you are comparing versions of a financial report, tracking project updates, or ensuring data integrity, mastering Excel file comparison techniques will significantly enhance your productivity and data management capabilities.
