Comparing two Excel worksheets to identify differences can be a common necessity for professionals who manage and analyze data. Whether you are tracking changes in financial reports, sales data, or project plans, pinpointing discrepancies quickly and accurately is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to streamline this process, enabling you to efficiently compare two Excel workbooks or different versions of the same workbook and highlight any variations. This guide will walk you through how to use Spreadsheet Compare to effectively Excel Compare Two Worksheets For Differences.
Spreadsheet Compare is an invaluable utility for identifying potential issues like manually entered totals instead of calculated ones, or broken formulas that might compromise data integrity.
Important Note: Before proceeding, ensure that you have access to Spreadsheet Compare. This tool is included 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 of Excel, this tool might not be available to you.
Getting Started with Spreadsheet Compare
To begin using Spreadsheet Compare and excel compare two worksheets for differences, you first need to open the application.
-
Access Spreadsheet Compare: On your Windows Start screen, simply type Spreadsheet Compare. The option should appear in the search results, allowing you to select and open the application directly.
If you don’t immediately see the Spreadsheet Compare option, typing the full name will ensure it appears.
Alongside Spreadsheet Compare, you might also notice Microsoft Database Compare, a similar tool designed for Access databases. It’s worth noting that Database Compare also requires Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Compare Two Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to excel compare two worksheets for differences:
-
Initiate File Comparison: Click on Home > Compare Files. This action will open the Compare Files dialog box, which is your starting point for selecting the workbooks you wish to compare.
-
Select the Earlier Workbook Version: In the Compare Files dialog box, locate the Compare box. Click on the blue folder icon situated next to it. This will allow you to browse your file system to find and select the earlier version of your Excel workbook. You’re not just limited to local files; you can also input a web address if your workbooks are saved on a website.
-
Select the Workbook to Compare To: Next, click the green folder icon next to the To box. Browse to and select the workbook that you want to compare against the earlier version. Once you have selected both files, click OK.
Tip: Spreadsheet Compare gives you the flexibility to compare two files even if they share the same name, provided they are saved in different folders. This is particularly useful when comparing versions of the same report saved in different project folders.
-
Customize Comparison Options: Before running the comparison, you can specify what aspects of the workbooks you want to examine for differences. In the left pane of the Compare Files dialog, you’ll see a list of options such as Formulas, Macros, and Cell Format. Check or uncheck these options to tailor the comparison to your needs. If you wish to compare everything, simply click Select All.
-
Run the Comparison: Finally, click OK to initiate the comparison process. Spreadsheet Compare will analyze the selected workbooks based on your chosen options.
If a message appears stating “Unable to open workbook,” it typically indicates that one or both of the workbooks are password protected. 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 contents.
Once the comparison is complete, 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 one). A detailed pane below the grids provides specifics about the detected differences. Changes are highlighted with distinct colors, each representing a specific type of modification.
Understanding the Comparison Results
Navigating and interpreting the comparison results is straightforward, allowing you to quickly grasp the changes between your Excel worksheets.
-
Side-by-Side Worksheet Grid: In the comparison grid, each worksheet from the compared files is displayed side-by-side. If your workbooks contain multiple worksheets, you can easily navigate through them using the forward and back buttons on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and will be displayed in the results, ensuring no part of your workbook is overlooked.
-
Color-Coded Differences: Differences are visually highlighted within the grid using cell fill colors and text font colors. The color coding is intuitive; for example, cells containing “entered values” (cells without formulas) that have been modified are highlighted with a green fill color in the grid and a green font in the results list pane. A legend, usually located in the lower-left pane, clearly explains what each color signifies, making it easy to understand the nature of each change.
Consider an example where a quarterly report workbook has been updated with final figures. If the initial version had preliminary data for Q4, and the updated version contains final numbers, Spreadsheet Compare will highlight these changes. In the results, cells with updated entered values (like final sales figures) will likely have a green fill. Consequently, if these updated values affect calculated fields, those cells with changed calculated values (like year-to-date totals) will be highlighted with a different color, such as blue-green. This color differentiation helps you quickly distinguish between direct data entry changes and changes resulting from formula updates.
In cases where formulas have been corrected or improved, Spreadsheet Compare also highlights these changes. For instance, if a formula was initially summing an incorrect range of cells and was later corrected to include all relevant cells, the cell containing the corrected formula will be flagged, emphasizing not just value changes but also structural formula improvements.
-
Resize Cells for Better Visibility: If cell contents appear truncated due to column width, simply click Resize Cells to Fit. This option automatically adjusts the column widths to ensure you can view the complete content of each cell, making it easier to review the comparison results.
Utilizing Excel’s Inquire Add-in
For users of Excel 2013 and later, there’s an additional tool within Excel itself that complements Spreadsheet Compare: the Inquire add-in. Once activated, the Inquire add-in adds an “Inquire” tab to your Excel ribbon, providing a range of analytical tools.
From the Inquire tab, you can perform workbook analysis, visualize relationships between cells, worksheets, and even across different workbooks. It also offers a useful feature to clean up excessive cell formatting within a worksheet, which can improve workbook performance and clarity.
To use Spreadsheet Compare directly from Excel, with two workbooks open, you can use the Compare Files command typically found within the Inquire add-in, if enabled.
If you do not see the Inquire tab in your Excel ribbon, you may need to activate it. Instructions for enabling it can be found at Turn on the Inquire add-in. To delve deeper into the capabilities of the Inquire add-in, explore What you can do with Spreadsheet Inquire.
Next Steps for Advanced Excel Management
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, consider implementing Microsoft’s comprehensive spreadsheet and database management tools. Microsoft Audit and Control Management Server offers robust change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis capabilities, both designed to mitigate risks associated with end-user developed tools in Excel and Access.
Further information on Spreadsheet Compare can be found in the Overview of Spreadsheet Compare.
By using Spreadsheet Compare, you can efficiently excel compare two worksheets for differences, ensuring data accuracy and integrity, which is essential for informed decision-making and effective data management.
