Compare 2 Excel Spreadsheets: A Detailed Guide

Comparing two Excel spreadsheets is a common task for professionals who manage data, track changes, or ensure data integrity. Whether you’re auditing financial records, comparing sales reports, or merging datasets, identifying differences between spreadsheets accurately and efficiently is crucial. Microsoft Spreadsheet Compare, a powerful tool available with specific Microsoft Office versions, is designed to streamline this process. This guide will walk you through how to effectively use Spreadsheet Compare to highlight discrepancies and ensure data consistency across your Excel workbooks.

What is Microsoft Spreadsheet Compare?

Spreadsheet Compare is a utility from Microsoft designed to compare two Excel workbook files, pinpointing differences in data, formulas, formatting, and more. It generates a detailed report, visually highlighting the variations between the two spreadsheets. This tool is invaluable for tasks such as:

• Auditing changes: Identifying modifications made between different versions of a workbook.
• Error detection: Locating inconsistencies, such as manually entered totals instead of calculated ones, or broken formulas.
• Data validation: Ensuring consistency when merging or consolidating data from multiple sources.
• Version control: Understanding the evolution of a spreadsheet over time.

Important Note: Spreadsheet Compare is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a different version of Excel, you may need to explore alternative comparison methods or consider upgrading your Office suite to access this feature.

Opening Spreadsheet Compare

Accessing Spreadsheet Compare is straightforward if you have a compatible version of Office installed. Here’s how to open it:

1. Via the Start Menu: Click on the Start button (Windows icon) on your taskbar.
2. Search for Spreadsheet Compare: Begin typing “Spreadsheet Compare”. As you type, the search results will filter.
3. Select Spreadsheet Compare: Click on the Spreadsheet Compare application when it appears in the search results.

This image shows how to locate and open the Spreadsheet Compare application by searching in the Windows Start Menu, emphasizing the ease of access to the tool.

Once opened, Spreadsheet Compare presents a clean interface ready for you to begin comparing your Excel files.

Step-by-Step Guide to Compare Two Excel Workbooks

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

1. Initiate the 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.

2. Select the “Compare” Workbook (Older Version): In the Compare Files dialog, locate the Compare box. Click the blue folder icon situated next to it. This will open a file explorer window, allowing you to browse and select the earlier version of your Excel workbook that you wish to use as the basis for comparison. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.

3. Select the “To” Workbook (Newer Version): Next, find the To box in the Compare Files dialog. Click the green folder icon adjacent to it. Again, a file explorer window will appear, prompting you to select the workbook you want to compare against the first one (typically the more recent version). Choose your file and click OK.

   Tip: You can efficiently compare two files even if they share the same name, as long as they are stored in different folders. This is particularly useful when comparing versions of the same report saved in different locations.

4. Customize Comparison Options: In the left pane of the Compare Files dialog, you’ll see a list of options that determine what aspects of the workbooks will be compared. These options include:

   • Formulas: Compares the formulas used in cells.
   • Macros: Checks for differences in VBA macros.
   • Cell Format: Identifies variations in cell formatting, such as fonts, colors, and number formats.
   • Values: Compares the numerical and text values within cells.
   • Worksheet Structure: Examines differences in worksheet order, hidden sheets, and sheet names.

   You can selectively check or uncheck these options based on your comparison needs. For a comprehensive analysis, you can simply click Select All to include all aspects in the comparison.

5. Execute the Comparison: After selecting your files and choosing your comparison options, click OK to initiate the comparison process. Spreadsheet Compare will then analyze the two workbooks based on your selected criteria.

6. Handle Password-Protected Workbooks (If Necessary): If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK on the message. You will then be prompted to enter the password for the protected workbook. Spreadsheet Compare requires the password to access and compare the contents of protected files.

This image shows the “Compare Files” dialog in Microsoft Spreadsheet Compare, highlighting the steps to select the older and newer Excel files for comparison using the folder icons.

Understanding the Comparison Results

Once the comparison is complete, Spreadsheet Compare presents the results in a user-friendly, two-pane grid.

• Side-by-Side Grid: The left pane displays the “Compare” workbook (typically the older file), and the right pane shows the “To” workbook (usually the newer one). Each workbook’s worksheets are compared sheet by sheet. 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. Even hidden worksheets are included in the comparison and displayed in the results.

• Detailed Results Pane: Below the side-by-side grid, a details pane provides a legend explaining the color-coding used to highlight differences. It also lists the specific changes found, often with more granular detail than what’s immediately visible in the grid.

• Color-Coded Highlighting: Differences are visually represented using color highlights in both the grid and the results list. The type of difference determines the color used. For example:

  • Green Fill: Typically indicates “entered values” (cells containing manually input data, not formulas) that have been changed.
  • Green Font: Also used for “entered values” in the results list, reinforcing the change.
  • Blue-Green Fill: Often signifies “calculated values” (cells with formulas) that have changed, usually as a consequence of changes in input values or formula modifications.

This image displays a comparison result in Spreadsheet Compare, showing two Excel sheets side-by-side with differences highlighted in colors like green and blue-green, along with a legend explaining the color codes.

Example Interpretation: Imagine comparing two versions of a quarterly sales report. If the Q4 figures were updated in the newer version, cells containing those figures would likely be highlighted with a green fill, indicating changed “entered values.” Furthermore, if formulas in the “Year-to-Date” (YTD) column were affected by these Q4 changes, those cells would be highlighted with a blue-green fill, signifying changed “calculated values.” Spreadsheet Compare not only points out changes but also helps you understand the ripple effect of data modifications throughout your spreadsheets.

• Resizing Cells: If cell contents are truncated due to column width, click Resize Cells to Fit. This option automatically adjusts column widths to ensure you can view the complete content of each cell in the comparison grid, improving readability.

Leveraging Excel’s Inquire Add-in

Beyond Spreadsheet Compare, Excel itself offers the “Inquire” add-in (available in Excel 2013 and later, Professional Plus versions and Microsoft 365 Apps for enterprise) which provides additional workbook analysis capabilities. Once enabled, the “Inquire” tab in Excel allows you to:

• Analyze Workbook: Generate reports detailing workbook structure, formula complexity, and potential errors.
• Show Workbook Relationships: Visualize the connections between cells, worksheets, and even external workbooks, helping you understand data dependencies.
• Clean Excess Cell Formatting: Remove unnecessary formatting that can increase file size and potentially cause compatibility issues.

While Spreadsheet Compare is a dedicated tool for comparing files side-by-side, the Inquire add-in offers a broader suite of analytical 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 currently open.

If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Microsoft Office support documentation. Exploring the “What you can do with Spreadsheet Inquire” documentation will further illuminate the capabilities of this powerful Excel add-in.

Next Steps and Enterprise Solutions

For organizations heavily reliant on Excel and Access for critical operations, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files, enhancing control and traceability. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features to help manage and mitigate risks associated with user-developed tools in Excel and Access environments.

These enterprise-level solutions, along with tools like Spreadsheet Compare and the Inquire add-in, demonstrate Microsoft’s commitment to providing comprehensive solutions for managing and ensuring the integrity of spreadsheet data, from individual users to large organizations.

To gain a broader understanding of the tool, you can also explore the Overview of Spreadsheet Compare for additional insights and context.

By utilizing Microsoft Spreadsheet Compare, you can effectively Compare 2 Excel Spreadsheets, identify critical differences, and maintain data accuracy, contributing to more informed decision-making and improved data management practices.