Comparing Excel worksheets for differences is a common task for professionals who manage and analyze data. Whether you’re tracking changes in budgets, sales figures, or project plans, identifying discrepancies between versions of your spreadsheets is crucial for accuracy and informed decision-making. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose, allowing you to efficiently pinpoint changes and potential issues within your Excel workbooks.
What is Microsoft Spreadsheet Compare?
Microsoft Spreadsheet Compare is a utility designed to highlight the differences between two Excel workbooks or different versions of the same workbook. It generates a detailed report that outlines changes in content, formulas, formatting, and more. This tool is invaluable for auditing spreadsheets, ensuring data integrity, and understanding modifications made over time.
Important Note: Spreadsheet Compare is not a standalone application included in all versions of Microsoft Office or Microsoft 365. It is specifically 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 methods or consider upgrading to a version that includes this feature.
Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, included in the same Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Step-by-Step Guide to Comparing Excel Worksheets
Using Spreadsheet Compare is a straightforward process. Follow these steps to effectively compare your Excel worksheets and identify differences:
Opening Spreadsheet Compare
- Access via Start Menu: The easiest way to open Spreadsheet Compare is through the Windows Start Menu. Click on the Start button, and look for Spreadsheet Compare in your list of applications.
- Search Function: If you don’t immediately see it, simply start typing “Spreadsheet Compare” after clicking the Start button. The application should appear as a search result. Click on it to launch.
Selecting Files for Comparison
-
Initiate File Comparison: Once Spreadsheet Compare is open, click on Home > Compare Files. This action will open the Compare Files dialog box.
-
Choose the “Compare” File (Older Version): In the Compare Files dialog box, locate the Compare box. Click the blue folder icon situated to the right of this box. This will open a file explorer window, allowing you to browse your computer or network for the earlier version of your Excel workbook that you wish to compare. You can also directly enter a web address if your workbooks are stored online.
-
Choose the “To” File (Newer Version): Next, find the To box in the Compare Files dialog. Click the green folder icon next to it. Again, a file explorer window will open, allowing you to select the more recent version of the workbook you want to compare against the earlier version. Click OK after selecting the file.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders. This is especially useful when comparing versions of the same file saved in different locations.
Choosing Comparison Options
Before running the comparison, you can customize the report to focus on specific elements. In the left pane of the Compare Files dialog box, you’ll find a list of options.
- Select Comparison Aspects: Check or uncheck the boxes next to options like Formulas, Macros, and Cell Format to specify what aspects of the workbooks should be included in the comparison.
- “Select All” Option: If you want to compare all aspects of the workbooks, simply click Select All.
Running the Comparison
-
Execute Comparison: Once you have selected your files and chosen your comparison options, click OK in the Compare Files dialog box 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 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
After running the comparison, Spreadsheet Compare presents the results in a user-friendly, two-pane grid layout.
Two-Pane Grid Layout
- Side-by-Side View: The comparison results are displayed in a side-by-side grid. The workbook on the left pane represents the “Compare” file (typically the older version), and the workbook on the right pane is the “To” file (typically the newer version).
- Worksheet Navigation: If your workbooks contain multiple worksheets, each worksheet from the “Compare” file is compared to the corresponding worksheet in the “To” file. You can navigate through different worksheet comparisons by using the forward and back buttons located on the horizontal scroll bar.
- Hidden Worksheets: Importantly, even if a worksheet is hidden in either of the workbooks, Spreadsheet Compare will still include it in the comparison and display its differences in the results.
- Details Pane: Below the two grids, you will find a details pane that provides a summary and legend for the comparison results.
Color-Coded Differences
Spreadsheet Compare uses a color-coding system to highlight the types of differences it detects:
- Visual Cues: Differences are visually represented in the side-by-side grid using cell fill colors or text font colors. The specific color indicates the type of change.
- “Entered Values” (Green Fill): Cells that contain manually entered values (i.e., not formulas) and have been changed are typically formatted with a green fill color in the grid view. In the details pane, these changes might be indicated with a green font.
- “Calculated Values” (Blue-Green Fill): Cells containing formulas where the calculated result has changed are often highlighted with a blue-green fill color. This signifies that a formula’s output has been affected by changes elsewhere in the workbook.
- Legend: The lower-left pane of the Spreadsheet Compare window acts as a legend, explaining what each color code represents. Refer to this legend to quickly understand the types of changes highlighted in the comparison.
Example Scenario: Imagine you are comparing two versions of a sales report workbook. In an earlier version, the Q4 sales figures were preliminary. The latest version contains the finalized Q4 numbers. Spreadsheet Compare would highlight the cells containing the updated Q4 sales figures (e.g., cells E2:E5) with a green fill, indicating changed “entered values.” Furthermore, if the “Year-to-Date” (YTD) column (e.g., column F) contains formulas that sum quarterly sales, the cells in the YTD column (e.g., F2:F4, E6:F6) that are affected by the updated Q4 figures would be highlighted with a blue-green fill, indicating a change in “calculated values.”
Resizing Cells
If you encounter cells that are too narrow to fully display their contents in the comparison grid, Spreadsheet Compare offers a convenient solution. Simply click Resize Cells to Fit to automatically adjust the column widths to ensure all cell content is visible.
Excel’s Inquire Add-in: An Alternative Tool
For users working within Excel 2013 and later versions, the Inquire add-in provides another set of tools for workbook analysis and comparison. While Spreadsheet Compare is a standalone application, the Inquire add-in integrates directly into Excel, adding an “Inquire” tab to the Excel ribbon.
From the Inquire tab, you can access features to:
- Analyze Workbook: Gain insights into workbook structure, formulas, and potential errors.
- Show Relationships: Visualize dependencies and relationships between cells, worksheets, and even different workbooks.
- Clean Excess Formatting: Reduce file size and improve performance by removing unnecessary formatting.
Notably, the Inquire add-in also includes a Compare Files command that essentially launches Spreadsheet Compare from within Excel. This offers a convenient way to access Spreadsheet Compare if you are already working in Excel and need to compare open workbooks.
If you do not see the Inquire tab in your Excel ribbon, you may need to activate it. Refer to instructions on how to Turn on the Inquire add-in. To explore the full capabilities of the Inquire add-in, see What you can do with Spreadsheet Inquire.
Advanced Solutions for Enterprise Environments
Organizations that rely heavily on “mission-critical” Excel workbooks and Access databases should consider Microsoft’s enterprise-level management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server offer robust change management, inventory, and analysis features specifically designed to mitigate risks associated with end-user developed tools in Excel and Access.
For a broader overview of Spreadsheet Compare and related tools, see Overview of Spreadsheet Compare.
Conclusion
Microsoft Spreadsheet Compare is an indispensable tool for anyone who needs to effectively compare Excel worksheets and identify differences. By providing a clear, color-coded report of changes, it simplifies the process of auditing spreadsheets, ensuring data accuracy, and understanding workbook modifications. Whether you are tracking versions, troubleshooting errors, or ensuring data integrity, Spreadsheet Compare streamlines your workflow and enhances your ability to manage and analyze critical spreadsheet data.
