Comparing Excel Sheets for Differences: A Detailed Guide

Comparing Excel Sheets For Differences is a common task for professionals across various fields. Whether you’re managing financial data, tracking project progress, or collaborating on spreadsheets, identifying discrepancies between versions is crucial for accuracy and informed decision-making. Microsoft Spreadsheet Compare is a powerful tool designed to streamline this process, allowing you to quickly pinpoint changes between Excel workbooks. This guide will walk you through how to effectively use Spreadsheet Compare to analyze your Excel files and understand the comparison results.

What is Spreadsheet Compare?

Spreadsheet Compare is a Microsoft tool specifically created to compare Excel workbook files and report on the differences it finds. It’s incredibly useful for identifying not only content changes but also potential issues like broken formulas or manually entered totals that should be calculated.

Important Note: It’s crucial to note that Spreadsheet Compare is not available in all versions of Microsoft Office. It’s 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 have access to this valuable tool for comparing Excel files.

How to Open Spreadsheet Compare

Accessing Spreadsheet Compare is straightforward.

1. Go to the Start Menu in Windows.
2. Look for Spreadsheet Compare. You might find it listed directly.
3. If you don’t see it immediately, simply start typing Spreadsheet Compare. The option should appear in the search results. Click on it to open the application.

Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, which functions similarly for comparing Access files. Database Compare also requires one of the Office Professional Plus versions or Microsoft 365 Apps for enterprise.

Step-by-Step Guide to Compare Two Excel Workbooks

Let’s delve into the process of comparing two Excel workbooks for differences using Spreadsheet Compare:

1. Initiate the Comparison: Once Spreadsheet Compare is open, click on Home > Compare Files. This action will open the Compare Files dialog box.

2. Select the Older Workbook: In the Compare Files dialog box, locate the Compare box. Click the blue folder icon next to it. This will open a browse window, allowing you to navigate to the location of the earlier version of your Excel workbook. You can select files from your local computer, network drives, or even enter a web address if your files are stored online.

3. Select the Newer Workbook: Next, find the To box and click the green folder icon beside it. Browse to and select the workbook you want to compare against the older version. Click OK to confirm your selections.

   Tip: You can effectively compare two files that have the same name as long as they are saved in different folders. This is particularly useful when comparing versions saved in different project directories or date-stamped folders.

4. Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll see a list of options. These options determine what aspects of the workbooks Spreadsheet Compare will analyze. You can choose to compare:

   • Formulas: Checks for changes in formulas.
   • Macros: Compares VBA macro code.
   • Cell Format: Identifies differences in cell formatting (font, color, number format, etc.).
   • And more: There are other options available depending on your needs.

   You can select specific options or simply click Select All to compare everything.

5. Run the Comparison: Once you’ve selected your comparison options, click OK to initiate the comparison process.

   Password Protected Workbooks: If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this happens, 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 Spreadsheet Compare Results

After running the comparison, the results are displayed in a user-friendly two-pane grid.

• Side-by-Side View: The workbook you selected as the “Compare” file (typically the older version) is shown on the left pane. The “To” file (usually the newer version) is displayed on the right pane.

• Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons located on the horizontal scroll bar below the grid.

  Note: Even hidden worksheets are included in the comparison and will be displayed in the results.

• Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the differences between the workbooks. The specific color indicates the type of change:

  • Green Fill Color (in grid): Typically indicates “entered values” (cells containing manually inputted data rather than formulas) that have been changed.
  • Green Font Color (in results list): Also used for changed entered values, often in the details pane.
  • Blue-Green Fill Color: Usually signifies “calculated values” (cells with formulas) where the result has changed. This could be due to changes in input values or formula modifications.

• Legend Pane: The lower-left pane of the Spreadsheet Compare window provides a legend explaining what each color code represents, making it easy to interpret the results.

Example Interpretation: In the example image, cells E2:E5 in both versions are highlighted with green fill, indicating that entered values in these cells have changed. Consequently, the calculated values in the “YTD” column (F2:F4 and E6:F6) are highlighted with blue-green, showing that these calculated results have been affected by the changes in the entered values. Cell F5 also shows a change; importantly, it highlights a formula correction in the newer version, where the formula was updated to correctly include all relevant cells in the sum.

• Resize Cells to Fit: If cell contents are truncated due to narrow columns, you can click Resize Cells to Fit to automatically adjust column widths for better readability.

Leveraging Excel’s Inquire Add-in

Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional workbook analysis capabilities. Once activated, the Inquire add-in adds an “Inquire” tab to the Excel ribbon.

From the Inquire tab, you can:

• Analyze Workbook: Get a comprehensive overview of workbook structure, formulas, and potential errors.
• Cell Relationship Exploration: Visualize the relationships between cells, worksheets, and even links to other workbooks.
• Clean Excess Formatting: Remove unnecessary formatting that can bloat file size and hinder performance.

While Spreadsheet Compare is ideal for direct file comparison, the Inquire add-in offers a broader suite of analysis tools within Excel itself. You can even launch Spreadsheet Compare directly 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.

Next Steps and Advanced Tools

For organizations heavily reliant on “mission-critical” Excel workbooks and Access databases, Microsoft offers more advanced management and auditing solutions.

• Microsoft Audit and Control Management Server: Provides robust change management features specifically designed for Excel and Access files, offering enhanced control and tracking of modifications.
• Microsoft Discovery and Risk Assessment Server: Focuses on inventory and analysis, helping organizations understand the landscape of user-developed Excel and Access tools to better manage risks associated with these critical assets.

These server-based solutions provide a higher level of governance and oversight for organizations needing to manage and secure their spreadsheet and database environments.

For a general overview of Spreadsheet Compare and its features, you can refer to the Overview of Spreadsheet Compare.

Conclusion

Spreadsheet Compare is an invaluable tool for anyone who regularly works with and needs to compare Excel sheets. By providing a clear, visual representation of differences, it significantly simplifies the process of identifying changes, ensuring data accuracy, and maintaining spreadsheet integrity. Whether you’re auditing financial reports, tracking version changes, or simply ensuring consistency across collaborative spreadsheets, Spreadsheet Compare is a powerful asset in your Excel toolkit.