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 consistency across different versions, knowing how to effectively compare Excel files is an invaluable skill. Microsoft Spreadsheet Compare is a powerful tool designed to help you achieve this, pinpointing discrepancies and potential issues with ease.
Important Note: Before we dive in, it’s essential to note that Microsoft Spreadsheet Compare is available as part of 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 functionality.
Understanding Microsoft Spreadsheet Compare
Microsoft Spreadsheet Compare is a dedicated utility designed to compare Excel workbooks, highlighting differences in content, formulas, and even formatting. It goes beyond simple visual inspection, providing a detailed report on changes, which is particularly useful when dealing with complex spreadsheets or multiple versions of the same file. This tool not only saves time but also increases accuracy in identifying modifications and potential errors.
Step-by-Step Guide to Comparing Excel Files
Let’s walk through the process of comparing two Excel workbooks using Spreadsheet Compare:
Opening Spreadsheet Compare
First, you need to launch the application.
- Go to the Start menu in Windows and click Spreadsheet Compare.
- If you don’t see it immediately, simply type Spreadsheet Compare and select it from the search results.
Alongside Spreadsheet Compare, you might notice Microsoft Database Compare, a similar tool for Access databases, also requiring Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Selecting Files for Comparison
Once Spreadsheet Compare is open, you’re ready to select the Excel files you want to compare.
-
Click Home > Compare Files. This will open the Compare Files dialog box.
Alt text: Compare Files dialog box is open in Microsoft Spreadsheet Compare, prompting users to select files for comparison.
-
Next to the Compare box, click the blue folder icon. Browse to the location of the older version of your Excel workbook and select it. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are saved online.
-
Click the green folder icon next to the To box. Browse to and select the more recent version of the workbook you want to compare against the first one. Click OK.
Tip: You can easily compare files with identical names as long as they are stored in different folders.
Choosing Comparison Options
Before running the comparison, you can specify what aspects of the workbooks you want to examine.
- In the left pane of the Compare Files dialog box, you’ll see a list of options. Select or deselect the checkboxes next to options like Formulas, Macros, and Cell Format to customize your comparison.
- If you want to compare everything, simply click Select All.
Running the Comparison
With your files selected and options chosen, you are ready to initiate the comparison process.
- Click OK in the Compare Files dialog box to start the comparison.
Handling Password-Protected Files
Occasionally, you might encounter an “Unable to open workbook” message. This usually indicates that one or both of the Excel files are password protected.
- If this message appears, click OK. You will then be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file content.
Understanding the Comparison Results
After running the comparison, the results are displayed in a clear, two-pane grid.
- The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer version).
- A details pane below the grids provides a legend and further information about the differences found.
- Changes are highlighted with different colors, each representing a specific type of modification.
Side-by-Side Grid View
The primary view is the side-by-side grid, where each worksheet from the compared files is placed next to its counterpart.
- If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar.
- Importantly, even hidden worksheets are included in the comparison and shown in the results, ensuring no changes are overlooked.
Color-Coded Differences
Spreadsheet Compare uses a color-coding system to visually represent the types of differences between the files.
- Cell fill colors and text font colors are used to highlight changes.
- For instance, cells containing “entered values” (cells with manually typed data, not formulas) are often marked with a green fill in the grid and green font in the results list.
The legend in the lower-left pane clearly explains what each color signifies, making it easy to interpret the comparison results.
Alt text: Side-by-side comparison results in Microsoft Spreadsheet Compare, highlighting differences in cell values and formulas with color-coded fills.
Example of Result Interpretation:
Consider an example where you are comparing quarterly sales data. In an earlier version of a workbook, the Q4 results were preliminary. The updated version contains the final Q4 numbers in column E.
In the comparison results:
- Cells E2:E5 in both versions might have a green fill, indicating that an entered value has changed.
- Because these values changed, the calculated year-to-date (YTD) results in column F also changed. Cells F2:F4 and E6:F6 might have a blue-green fill, signifying a change in calculated values.
- Cell F5 might also show a change, possibly due to a corrected formula in the newer version. For example, an incorrect formula in the older version (=SUM(B5:D5)) might have been corrected to include Q4 (=SUM(B5:E5)) in the updated workbook.
Resizing Cells
If cell contents are truncated due to column width, simply click Resize Cells to Fit to expand columns and view the complete data.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in. This add-in provides an “Inquire” tab in Excel, offering further analytical capabilities.
From the Inquire tab, you can:
- Analyze workbooks for potential issues and inconsistencies.
- Visualize relationships between cells, worksheets, and even different workbooks.
- Clean up excessive formatting within a worksheet.
While Spreadsheet Compare is excellent for side-by-side file comparisons, the Inquire add-in offers deeper workbook analysis directly within Excel. You can even launch Spreadsheet Compare from Excel using the Compare Files command within the Inquire tab if you have two workbooks open that you wish to compare.
If you don’t see the Inquire tab in your Excel, you may need to activate it. Instructions on how to do this can be found by searching for “Turn on the Inquire add-in” in Microsoft Office support.
Advanced Solutions for Enterprise Environments
For organizations dealing with “mission-critical” Excel workbooks and Access databases, Microsoft offers more robust management tools.
Microsoft Audit and Control Management Server provides advanced change management features for Excel and Access files.
Microsoft Discovery and Risk Assessment Server complements this by offering inventory and analysis features.
These enterprise-level solutions are designed to help reduce risks associated with user-developed tools in Excel and Access, providing greater control and oversight.
Further information about Spreadsheet Compare can be found in the Overview of Spreadsheet Compare.
By using Microsoft Spreadsheet Compare, you can efficiently and accurately compare two Excel files, identify changes, and ensure data integrity. This tool is an essential asset for anyone working with Excel in a professional environment, offering significant improvements in data management and accuracy.
