Comparing two Excel files to identify differences can be a crucial task for various professionals, from financial analysts ensuring data accuracy to project managers tracking changes in project plans. Manually scanning through spreadsheets is time-consuming and prone to error. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare that simplifies this process, allowing you to quickly pinpoint discrepancies and understand modifications between two versions of your Excel workbooks.
Important Note: Before we dive in, it’s essential to know that Spreadsheet Compare is not available in all versions of Microsoft Office. This powerful tool comes with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. If you are using a different version, you may need to consider upgrading or exploring alternative comparison methods, although Spreadsheet Compare remains the most integrated and feature-rich solution for users with access to these editions.
Launching Spreadsheet Compare
The first step is to open the Spreadsheet Compare tool. Here’s how to find it:
- Access via 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 launch the application.
Image showing the Compare Files command in Spreadsheet Compare, highlighting the blue and green folder icons for selecting Excel files to compare.
Once launched, you’ll be greeted with the Spreadsheet Compare interface, ready to help you analyze your Excel files.
Step-by-Step Guide to Comparing Two Excel Workbooks
Comparing two Excel files for differences using Spreadsheet Compare is straightforward. Follow these steps to effectively analyze your workbooks:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the Older Workbook (Compare File): Locate the Compare box within the dialog. Click on the blue folder icon situated next to it. This will open a file explorer window, allowing you to browse your computer or network to find the earlier version of your Excel workbook. You also have the option to input a web address if your files are stored online. Select the desired file and click Open.
-
Select the Newer Workbook (To File): Next, find the To box and click the green folder icon adjacent to it. Again, browse to the location of the more recent version of the Excel workbook that you wish to compare against the older one. Select this file and click OK.
Pro Tip: You can compare two files even if they have identical names, as long as they are stored in different folders. This is particularly useful when you have versions saved in separate project directories or date-stamped folders.
-
Choose Comparison Options: In the left-hand pane of the Compare Files dialog, you’ll see a list of options that determine what aspects of the workbooks will be compared. You can customize your comparison by checking or unchecking boxes next to options like Formulas, Macros, Cell Format, and more. If you want a comprehensive comparison, simply click Select All.
-
Execute the Comparison: After selecting your files and comparison options, click OK to initiate the comparison process.
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 needs the password to access and analyze the file content.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid within Spreadsheet Compare.
- Side-by-Side View: 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).
- Worksheet Navigation: If your Excel files contain multiple worksheets, Spreadsheet Compare compares them sheet by sheet. You can navigate between worksheets using the forward and back buttons on the horizontal scroll bar located above the grid. Importantly, even hidden worksheets are included in the comparison and displayed in the results.
- Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences detected. Cells with changes are marked with either a fill color or a text font color, depending on the nature of the modification. For example, cells containing values that were directly entered (not formulas) and have been altered are often highlighted with a green fill color in the grid and a green font in the detailed results list below.
Image showing comparison results in Spreadsheet Compare, highlighting color-coded differences between two Excel workbook versions, with a legend explaining the color codes.
- Detailed Legend: The lower-left pane of the Spreadsheet Compare window provides a legend that clearly explains what each color code signifies. This legend is crucial for quickly interpreting the comparison results and understanding the types of changes made.
Example Interpretation: Imagine you are comparing sales reports from two quarters. If you see green-filled cells in the “entered values” category in the sales figures columns, it indicates that the sales numbers themselves have been updated. If you then observe blue-green filled cells in a “calculated value changed” category in a totals column, it suggests that formulas have recalculated based on the changed input values. Furthermore, if a formula itself was corrected or altered, Spreadsheet Compare will also highlight this as a difference.
- Resize Cells for Better Viewing: If cell contents are truncated due to narrow columns, click the Resize Cells to Fit option. This will automatically adjust column widths to ensure you can fully view the content of each cell and understand the differences.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions (including those in Microsoft 365) offer another valuable tool called the Inquire add-in. Activating this add-in adds an “Inquire” tab to your Excel ribbon, providing a suite of features for workbook analysis.
From the Inquire tab, you can:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even links to external workbooks.
- Visualize Cell Relationships: Explore how cells are connected through formulas and dependencies.
- Clean Excess Formatting: Identify and remove unnecessary formatting that can bloat file size and potentially cause performance issues.
- Access Spreadsheet Compare: The Inquire tab also provides a direct link to launch Spreadsheet Compare from within Excel, streamlining your workflow if you are already working with the files you need to compare.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it manually through Excel Options > Add-ins > COM Add-ins > Go > and checking the “Inquire” box.
Next Steps for Enterprise Environments
For organizations managing mission-critical Excel workbooks and Access databases, Microsoft offers more advanced solutions. Microsoft Audit and Control Management Server provides robust change management capabilities specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features to help you manage risks associated with user-developed tools in Excel and Access. These server-based solutions provide a higher level of control, auditability, and risk mitigation for critical spreadsheet and database assets within an organization.
Further Resources: For a more comprehensive overview of Spreadsheet Compare, you can refer to the Overview of Spreadsheet Compare provided by Microsoft.
By utilizing Spreadsheet Compare, you can significantly enhance your efficiency and accuracy when comparing Excel files for differences, ensuring data integrity and facilitating better collaboration and version control.
