Comparing two Excel files might seem daunting, especially when dealing with large datasets or complex spreadsheets. Whether you’re tracking changes, auditing data, or merging information, knowing how to effectively Compare Two Excel Files is crucial for accuracy and efficiency. Microsoft Spreadsheet Compare, a powerful tool within Microsoft Office Professional Plus and Microsoft 365 Apps for enterprise, is designed to simplify this process. This guide will walk you through how to use Spreadsheet Compare to identify differences between two Excel workbooks, understand the results, and explore related Excel features that can further enhance your data analysis.
Understanding Microsoft Spreadsheet Compare
Spreadsheet Compare is a specialized tool designed to highlight the differences between two Excel workbooks or different versions of the same workbook. It goes beyond simply comparing cell values; it can identify changes in formulas, macros, cell formatting, and more. This makes it invaluable for tasks such as:
- Version Control: Quickly see what has changed between different versions of a budget, financial model, or project plan.
- Error Detection: Find discrepancies like manually entered totals instead of formulas or broken formulas that could lead to incorrect calculations.
- Data Auditing: Ensure consistency and accuracy when multiple users are working on the same or similar workbooks.
- Merging Changes: Understand the modifications made in different versions before merging them into a single, updated file.
Important Note: Spreadsheet Compare is not included in all versions of Microsoft Office. It is specifically available in:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you are using one of these versions, you can access Spreadsheet Compare to streamline your Excel file comparison tasks.
Step-by-Step Guide: Comparing Two Excel Workbooks
Let’s delve into the process of comparing two Excel files using Spreadsheet Compare. Follow these steps to effectively identify the differences between your workbooks:
-
Open Spreadsheet Compare:
- Navigate to the Start menu on your Windows system.
- Look for Spreadsheet Compare. If you don’t see it immediately, type “Spreadsheet Compare” in the search bar and select the application when it appears.
Opening Spreadsheet Compare is the first step to start analyzing your Excel files for differences.
-
Select Files for Comparison:
-
Once Spreadsheet Compare is open, click on Home > Compare Files. This action will open the Compare Files dialog box.
-
In the Compare Files dialog box, you’ll see two boxes: Compare and To.
-
Click the blue folder icon next to the Compare box. This is where you will select the earlier version or the first Excel file you want to compare. Browse to the location of your workbook. You can select files from your computer, network locations, or even enter a web address if your workbooks are saved online.
- Next, click the green folder icon next to the To box. Here, you will select the later version or the second Excel file that you want to compare against the first one. Browse to its location and click OK.
Tip: You can 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 versions of the same report saved in different project folders.
-
-
Choose Comparison Options:
-
In the left pane of the Compare Files dialog box, you’ll find a list of options that determine what aspects of the workbooks will be compared. You can customize your comparison by checking or unchecking these options:
- Formulas: Compares the formulas used in cells.
- Macros: Checks for differences in VBA macros.
- Cell Format: Examines changes in cell formatting such as fonts, colors, and styles.
- Values: Compares the values within the cells.
- Worksheet Structure: Looks for differences in the structure of worksheets, like added, deleted, or renamed sheets.
-
For a comprehensive comparison, you can simply click Select All to check all options. Choose the options that are most relevant to your comparison needs and click OK to initiate the comparison.
-
-
Review the Comparison Results:
-
After clicking OK, Spreadsheet Compare will analyze the two Excel files based on your selected options. The results are displayed in a clear, two-pane grid.
-
The workbook you selected as the “Compare” file (typically the older version) is shown on the left pane, and the “To” file (typically the newer version) is on the right pane.
-
A details pane appears below the grids, providing a summary of the changes detected.
-
Differences are highlighted with distinct colors in both the grid and the details pane. These colors are coded to represent the type of change identified.
-
Navigate through worksheets using the forward and back buttons on the horizontal scroll bar if your workbooks contain multiple sheets. Even hidden worksheets are included in the comparison and displayed in the results.
-
Understanding the Color-Coded Results
Spreadsheet Compare uses a color-coding system to help you quickly understand the types of differences found between your Excel files. Here’s a breakdown of common color highlights:
- Green Fill (Entered Value Change): Indicates that a cell containing a manually entered value (not a formula) has been changed between the two versions. This is useful for spotting changes in raw data.
- Blue-Green Fill (Calculated Value Change): Highlights cells where the result of a formula has changed. This could be due to changes in input values or modifications to the formula itself.
- Other Colors: Spreadsheet Compare uses other colors to represent different types of changes, such as formatting changes, structural changes, or formula modifications. The legend in the lower-left pane of the results window provides a complete guide to the color codes used in your comparison.
Example Interpretation:
In the example result shown, cells E2:E5 in both versions are filled with green, indicating that entered values have changed in column E (presumably for Q4 results). Consequently, the calculated values in the YTD column (F) have also changed, highlighted in blue-green. Cell F5 also shows a change, pointing to a formula correction in the updated workbook where the formula was adjusted to correctly sum the range B5:E5 instead of just B5:D5.
Tip: If cell content is truncated due to column width, use the Resize Cells to Fit option to view the full content without manually adjusting column widths.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers a powerful add-in called Inquire. This add-in, available in Excel 2013 and later, provides a range of analytical tools that complement Spreadsheet Compare. To use Inquire, you may need to activate it first.
Enabling the Inquire Add-in:
If you don’t see the “Inquire” tab in your Excel ribbon, you need to enable the add-in:
- Go to File > Options > Add-ins.
- In the Manage dropdown at the bottom, select COM Add-ins and click Go.
- Check the box next to Inquire and click OK.
Once enabled, the “Inquire” tab appears in your Excel ribbon, offering functionalities like:
- Workbook Analysis: Provides a comprehensive overview of a workbook’s structure, formulas, errors, and hidden information.
- Relationship Exploration: Visually map relationships between cells, worksheets, and even across different workbooks, helping to understand data flow and dependencies.
- Excess Formatting Cleanup: Remove unnecessary cell formatting that can increase file size and potentially cause compatibility issues.
- Compare Files (Inquire): While the “Compare Files” command within the Inquire tab might sound similar to Spreadsheet Compare, it actually launches the Spreadsheet Compare tool we’ve been discussing. It serves as a convenient shortcut if you are already working within Excel.
For more in-depth information on the capabilities of the Inquire add-in, refer to What you can do with Spreadsheet Inquire.
Next Steps for Advanced Excel Management
For organizations that heavily rely on Excel workbooks and Access databases for critical operations, Microsoft offers advanced management tools:
- Microsoft Audit and Control Management Server: Provides robust change management features specifically designed for Excel and Access files. This helps maintain control and track modifications in crucial spreadsheets and databases.
- Microsoft Discovery and Risk Assessment Server: Offers inventory and analysis features to help organizations understand the scope and risk associated with user-developed tools in Excel and Access. This is crucial for identifying potential vulnerabilities and ensuring data governance.
These server solutions are designed to minimize risks associated with end-user developed applications in Excel and Access, providing a more controlled and auditable environment for critical business data.
Conclusion
Comparing two Excel files effectively is essential for maintaining data integrity, tracking changes, and ensuring accuracy in your spreadsheets. Microsoft Spreadsheet Compare offers a dedicated and efficient way to achieve this, highlighting differences in values, formulas, formatting, and more. Combined with Excel’s Inquire add-in for deeper workbook analysis, you have a powerful toolkit for managing and understanding your Excel data. Whether you are managing versions of financial reports, auditing data entries, or simply need to see what changed in a workbook, these tools will significantly enhance your productivity and data accuracy.
