Comparing two Excel files to identify differences can be a crucial task for various reasons. Whether you’re managing multiple versions of a workbook, auditing data changes, or ensuring formula consistency, knowing how to effectively compare Excel files is a valuable skill. Microsoft Excel provides a built-in tool called Spreadsheet Compare to help you achieve this, especially if you have Office Professional Plus or Microsoft 365 Apps for enterprise. This guide will walk you through the steps on how to use Spreadsheet Compare to pinpoint the differences between two Excel files.
Understanding Spreadsheet Compare and its Availability
Spreadsheet Compare is a powerful utility designed to highlight the discrepancies between two Excel workbooks. It’s particularly useful for identifying changes in data, formulas, formatting, and even VBA code. However, it’s important to note that Spreadsheet Compare is not available in all versions of Excel. It is included with:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you have one of these versions, you can readily access and utilize Spreadsheet Compare to streamline your Excel file comparison tasks.
Launching Spreadsheet Compare
To begin comparing your Excel files, you first need to open the Spreadsheet Compare application. Here’s how:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare. You can scroll through the app list or simply type “Spreadsheet Compare” to search for it.
- Click on Spreadsheet Compare to launch the application.
Once opened, you’ll be ready to start comparing your Excel workbooks. Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access, which is accessed similarly and has the same version requirements.
Step-by-Step Guide to Compare Excel Files
Let’s dive into the process of comparing two Excel workbooks for differences using Spreadsheet Compare:
-
Initiate the Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files.
-
Select Files to Compare: The Compare Files dialog box will appear.
- Click the blue folder icon next to the Compare box. This is for selecting the earlier version of your Excel file. Browse to the file location, which can be on your local computer, a network drive, or even a website URL where the file is saved.
- Next, click the green folder icon beside the To box. Browse to and select the more recent version of the Excel file that you want to compare against the earlier one. 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.
-
Choose Comparison Options: In the left pane of the Compare Files dialog, you’ll see a list of options. These options allow you to specify what aspects of the workbooks you want to compare. You can check or uncheck options such as:
- Formulas: Compares formulas in cells.
- Macros (VBA): Checks for differences in VBA code.
- Cell Format: Examines formatting differences like fonts, colors, and styles.
- Comments: Compares comments added to cells.
For a comprehensive comparison, you can simply click Select All. Choose the options that best suit your comparison needs and click OK to proceed.
-
Run the Comparison: Click OK in the Compare Files dialog to start the comparison process.
- Password Protected Files: If either of the workbooks is password-protected, you might receive an “Unable to open workbook” message. Click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file content.
-
Review the Comparison Results: Once the comparison is complete, the results are displayed in a split-screen grid.
- The left pane shows the “Compare” file (typically the older version).
- The right pane shows the “To” file (typically the newer version).
- A details pane appears below the grids, providing a summary of the differences.
Differences are highlighted with distinct colors to indicate the type of change. A legend in the lower-left pane explains what each color signifies.
Interpreting the Comparison Results
Understanding the color-coding and layout is key to effectively interpreting the comparison results:
-
Worksheet Navigation: The side-by-side grid displays worksheet comparisons. If your workbooks have multiple worksheets, use the forward and back buttons on the horizontal scroll bar to navigate through them. Note that even hidden worksheets are included in the comparison and results.
-
Color-Coded Differences: Differences are visually represented using cell fill colors or text font colors. For example, cells containing directly entered values (not formulas) that have been modified are often highlighted with a green fill in the grid and green font in the results list.
In the example result above, you can observe:
- Green Fill (Entered Value Change): Cells E2:E5 in both versions are filled with green, indicating that entered values have been changed in these cells.
- Blue-Green Fill (Calculated Value Change): Cells F2:F4 and E6:F6 have a blue-green fill, meaning that calculated values have changed. This is often a ripple effect from changes in input values or formulas.
- Formula Correction: Cell F5 highlights a more significant change. While the value changed, the underlying reason is that the formula in the earlier version was incorrect (=SUM(B5:D5)). The updated version corrected the formula to =SUM(B5:E5), leading to a different, and correct, calculated result.
-
Resize Cells for Better View: If cell contents are truncated due to narrow columns, click Resize Cells to Fit to automatically adjust column widths and view the full content.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in. This add-in provides a dedicated “Inquire” tab in Excel, offering a suite of analysis tools. While Spreadsheet Compare is a standalone application, the Inquire add-in integrates directly into Excel.
Key features of the Inquire add-in include:
- Workbook Analysis: Provides a comprehensive overview of a workbook’s structure, formulas, errors, and hidden information.
- Relationship Exploration: Visualizes relationships between cells, worksheets, and even links to other workbooks, helping to understand data flow and dependencies.
- Excess Formatting Cleanup: Helps in removing unnecessary cell formatting, which can reduce file size and improve performance.
If you have Excel workbooks open and wish to compare them directly from within Excel, you can use the Compare Files command available within the Inquire add-in, which essentially launches Spreadsheet Compare.
If you don’t see the Inquire tab in your Excel, you might need to activate it. You can find instructions on how to Turn on the Inquire add-in. To learn more about the capabilities of this add-in, explore What you can do with Spreadsheet Inquire.
Advanced Solutions for Critical Excel Management
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 these file types. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis capabilities. These server solutions are designed to mitigate risks associated with user-developed tools in Excel and Access by providing enhanced control and oversight.
Conclusion
Spreadsheet Compare is an invaluable tool for anyone needing to compare two Excel files for differences. By following these steps, you can effectively identify changes in data, formulas, and more, ensuring data integrity and accuracy across different versions of your workbooks. Whether for auditing, version control, or troubleshooting, mastering Excel file comparison techniques will significantly enhance your productivity and data management capabilities.
