In today’s data-driven world, working with multiple Excel files or versions of the same workbook is a common scenario. Whether you’re tracking changes, auditing data, or simply ensuring consistency, the need to Excel Compare Two Files For Differences is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to help you efficiently identify discrepancies and potential issues within your Excel workbooks. This guide will walk you through how to use Spreadsheet Compare to effectively analyze and compare your Excel files, ensuring data accuracy and saving you valuable time.
What is Spreadsheet Compare and Who Can Use It?
Spreadsheet Compare is a utility from Microsoft specifically designed to highlight the differences between two Excel workbooks or even two versions of the same file. It generates a detailed report pinpointing changes in formulas, values, formatting, and more. This tool is invaluable for anyone needing to audit spreadsheets, understand modifications made over time, or troubleshoot errors arising from inconsistencies between workbooks.
Availability: It’s important to note that Spreadsheet Compare is not a standalone application for all Excel users. It is included with specific Microsoft Office suites, namely:
- 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 already have access to this powerful comparison tool.
Opening Spreadsheet Compare: Step-by-Step
Accessing Spreadsheet Compare is straightforward. Here’s how to launch it:
- From the Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: If you don’t immediately see “Spreadsheet Compare” in your start menu, simply begin typing “Spreadsheet Compare”.
- Select the Application: The “Spreadsheet Compare” option should appear in the search results. Click on it to open the application.
Once launched, you’ll be ready to begin comparing your Excel files.
How to Compare Two Excel Workbooks for Differences
Spreadsheet Compare simplifies the process of comparing two Excel files. Follow these steps to effectively identify the differences:
-
Launch Compare Files.
Once Spreadsheet Compare is open, navigate to the Home tab and click on Compare Files. This action will open the “Compare Files” dialog box, which is the starting point for your comparison. -
Select the Old and New Files.
In the “Compare Files” dialog box, you’ll see two boxes labeled Compare and To.-
Browse for the ‘Compare’ File: Click the blue folder icon located next to the Compare box. This will allow you to browse your computer or network to locate the earlier version of the Excel workbook you want to compare. You can even input a web address if your files are stored online.
-
Browse for the ‘To’ File: Next, click the green folder icon next to the To box. Browse to and select the workbook that you want to compare against the earlier version. Click OK after selecting the file.
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 file.
-
-
Choose Comparison Options.
Before running the comparison, you can customize what aspects of the workbooks you want to analyze. In the left pane of the “Compare Files” dialog box, you’ll find a list of options. Select or deselect the checkboxes next to options like Formulas, Macros, Cell Format, and others, depending on your comparison needs. To compare everything, simply click Select All. -
Run the Comparison.
After selecting your files and comparison options, click OK to initiate the comparison process. Spreadsheet Compare will analyze the two workbooks based on your chosen settings.Password Protected Files: If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can handle password-protected files, allowing you to compare even sensitive documents.
Once the comparison is complete, the results will be displayed in a clear, two-pane grid, making it easy to visualize the differences.
Understanding the Comparison Results
The results of the comparison are presented in a user-friendly, side-by-side grid. 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 version). A detailed pane below the grids provides further information about the detected changes.
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences between the files. The specific colors represent different categories of changes, such as:
- Green Fill: Often indicates “entered values” or cells where non-formula values have been changed.
- Blue-Green Fill: Typically signifies changes in “calculated values” or formulas, meaning the result of a formula has been altered.
- Other Colors: Different colors might represent changes in formatting, macros, or other elements, depending on the comparison options you selected.
A legend in the lower-left pane of the results window clearly explains what each color represents, ensuring you can easily interpret the findings.
Navigating Worksheets: If your workbooks contain multiple worksheets, Spreadsheet Compare will compare them sheet by sheet. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar located above the grid. Even hidden worksheets are included in the comparison and displayed in the results, ensuring a comprehensive analysis.
Resizing Cells: If cell contents are truncated due to column width, you can easily view the full content by clicking Resize Cells to Fit. This option adjusts the column widths to accommodate the text, making it easier to read and understand the cell values.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel also offers the “Inquire” add-in, which provides a range of analytical tools for workbooks. If you are working directly within Excel 2013 or later, the Inquire add-in can be a valuable asset. To activate it, you may need to enable it in Excel’s options. Once enabled, an “Inquire” tab appears in the Excel ribbon.
The Inquire add-in offers features like:
- Workbook Analysis: Gain insights into workbook structure, formula relationships, and potential errors.
- Relationship Visualization: See connections between cells, worksheets, and even external workbooks.
- Excess Formatting Cleanup: Optimize workbook performance by removing unnecessary formatting.
While Spreadsheet Compare is excellent for side-by-side file comparisons, the Inquire add-in provides a broader set of tools for in-depth workbook analysis and auditing directly within Excel.
Taking It Further: Enterprise-Level Solutions
For organizations managing “mission-critical” Excel workbooks and Access databases, Microsoft offers more robust enterprise-level solutions. Microsoft Audit and Control Management Server provides advanced change management capabilities for Excel and Access files, ensuring stricter control and audit trails. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features, helping organizations mitigate risks associated with user-developed tools in Excel and Access. These server-based solutions provide a higher level of governance and control for critical spreadsheet environments.
Conclusion
Spreadsheet Compare is an indispensable tool for anyone who needs to excel compare two files for differences. It streamlines the process of identifying changes, inconsistencies, and potential errors between Excel workbooks. By following the steps outlined in this guide, you can effectively utilize Spreadsheet Compare to ensure data accuracy, track workbook modifications, and maintain the integrity of your spreadsheets. Whether you are auditing financial data, managing project plans, or simply collaborating on spreadsheets, mastering Spreadsheet Compare will significantly enhance your efficiency and data management capabilities.
