Comparing two Excel files is a common task for professionals who need to track changes, audit data, or merge information. Whether you’re working with different versions of a budget spreadsheet, comparing sales reports, or ensuring data consistency, identifying the differences between Excel files is crucial. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose. This guide will walk you through the process of using Spreadsheet Compare to effectively compare two Excel files and understand the results.
Understanding Spreadsheet Compare
Spreadsheet Compare is a utility from Microsoft designed to highlight the differences between two Excel workbooks. It’s particularly useful for identifying changes in formulas, values, formatting, and even VBA code. This tool is invaluable for tasks such as:
- Version Control: See what has changed between different iterations of a spreadsheet.
- Auditing: Verify data integrity and identify unintentional modifications.
- Troubleshooting: Find broken formulas or inconsistencies in calculations.
- Collaboration: Understand the changes made by different contributors.
Important Note: Spreadsheet Compare is included in 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 feature.
Step-by-Step Guide to Compare Excel Files
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare:
1. Open Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application.
- For Windows users: Click the Start button, then look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare,” and the option should appear. Select it to open the application.
2. Select 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 ribbon.
-
Click on the Compare Files button. This will open the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. This allows you to browse and select the older or the first file you want to compare. Locate the file and click Open. You can select files from your local computer, network drives, or even enter a web address if your files are stored online.
-
Next, in the To box, click the green folder icon. Browse to and select the second Excel file you want to compare against the first one. Click Open and then OK in the Compare Files dialog box.
Tip: You can compare files with the same name as long as they are saved in different folders. This is particularly useful when comparing versions of the same workbook saved in different locations.
3. Choose Comparison Options
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare should analyze.
-
In the left pane of the Compare Files dialog box, you’ll see a list of options. These options determine what elements of the Excel files will be included in the comparison report.
-
Select the checkboxes next to the features you want to compare. Common options include:
- Formulas: Compares formulas in cells.
- Values: Compares the values in cells.
- Cell Format: Identifies differences in cell formatting, like fonts, colors, and styles.
- Macros: Checks for changes in VBA macros (if applicable).
- Worksheet Name: Compares names of worksheets.
You can choose Select All if you want to compare all aspects of the workbooks.
-
Click OK to proceed with the comparison.
4. Run the Comparison
After selecting your files and comparison options, click OK in the Compare Files dialog box to initiate the comparison process.
- Password Protected Files: If one or both of your Excel files are password-protected, you might encounter an “Unable to open workbook” message. If this happens, click OK and enter the password for the protected workbook when prompted. Spreadsheet Compare needs the password to access and analyze the file content.
Once the comparison is complete, the results will be displayed in a split-screen interface.
Understanding the Comparison Results
The results of the comparison are presented in a user-friendly, side-by-side grid.
-
Side-by-Side Grid: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). Each workbook’s worksheets are compared against the corresponding worksheet in the other file. If there are multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and results.
-
Color-Coded Highlights: Differences are highlighted using colors to indicate the type of change. Here’s a general color scheme:
- Green fill: Indicates “entered values” or direct values in cells that have changed.
- Blue-green fill: Signifies changes in “calculated values” or formula results.
- Other colors: May be used for different types of formatting or structural changes.
-
Legend Pane: Below the two grids, you’ll find a legend that explains the color codes used in the comparison. Refer to this legend to understand the meaning of each color and the type of change it represents.
Example Interpretation: In the example results, if cells containing quarterly sales figures (Q4) are highlighted in green, it means the directly entered sales numbers have been updated between the two versions. If cells in the “Year-to-Date” (YTD) column are highlighted in blue-green, it indicates that the calculated totals have changed because the input values (Q4 figures) were modified. This visual representation makes it easy to quickly grasp the nature and location of changes.
- Resize Cells: If cell content is truncated due to column width, click Resize Cells to Fit to automatically adjust column widths and display the full content.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel offers another valuable tool for workbook analysis: the Inquire add-in. Available in Excel 2013 and later, the Inquire add-in provides an “Inquire” tab in the Excel ribbon, offering features such as:
- Workbook Analysis: Provides a comprehensive overview of a workbook’s structure, formulas, and potential issues.
- Relationship Exploration: Visualizes relationships between cells, worksheets, and even external workbooks, helping to understand data flow and dependencies.
- Excess Formatting Cleanup: Helps to remove unnecessary formatting that can bloat file size and impact performance.
While Spreadsheet Compare excels at direct file comparison, the Inquire add-in provides broader analytical capabilities within Excel itself. You can even launch Spreadsheet Compare directly from Excel using the Inquire add-in to compare two currently open workbooks.
If you don’t see the Inquire tab in your Excel, you may need to activate it. Go to File > Options > Add-Ins, in the “Manage” dropdown at the bottom, select “COM Add-ins” and click “Go…”. Then check the box next to “Inquire” and click “OK”.
Conclusion and Further Steps
Microsoft Spreadsheet Compare is an essential tool for anyone who works with Excel and needs to effectively track and understand changes between different versions of workbooks. By following the steps outlined in this guide, you can confidently compare two Excel files, interpret the results, and maintain data integrity.
For organizations managing critical Excel workbooks and Access databases, consider exploring Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These server-based solutions offer advanced change management, inventory, and analysis features, further enhancing control and reducing risks associated with user-developed tools in Excel and Access environments.
