Comparing Excel sheets for differences is a common task for professionals who work with data, financial analysis, or project management. Whether you’re tracking changes between versions, auditing data entries, or ensuring formula consistency, identifying discrepancies in your spreadsheets is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to streamline this process, allowing you to efficiently pinpoint variations between two Excel workbooks.
Note: It’s important to remember that Spreadsheet Compare is exclusively available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Office, this tool might not be accessible.
Accessing Spreadsheet Compare
Opening Spreadsheet Compare is straightforward.
- Via the Start Menu: Navigate to your Windows Start menu.
- Search for Spreadsheet Compare: If you don’t immediately see “Spreadsheet Compare” listed, simply begin typing “Spreadsheet Compare”. The application option should appear in the search results.
- Select and Launch: Click on “Spreadsheet Compare” to launch the application.
Alongside Spreadsheet Compare, Microsoft offers a similar tool for Access databases, named Microsoft Database Compare. This companion program, designed for comparing Access databases, also requires Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide: Comparing Two Excel Workbooks
Spreadsheet Compare simplifies the process of comparing two Excel workbooks. Follow these steps to effectively identify differences:
-
Initiate File Comparison: Within Spreadsheet Compare, click Home > Compare Files. This action will open the Compare Files dialog box.
-
Select the Original Workbook: In the Compare box, click the blue folder icon. This prompts a browse window to open, allowing you to locate and select the earlier version of your Excel workbook. You can choose files stored locally, on a network drive, or even input a web address for workbooks saved online.
-
Choose the Revised Workbook: Next, click the green folder icon situated next to the To box. Browse to and select the more recent version of the workbook you intend to compare against the original. Click OK to confirm your selection.
Tip: Spreadsheet Compare allows you to compare two files that share the same name, provided they are saved in different folders. This is particularly useful for version control.
-
Customize Comparison Options: In the left-hand pane of the Compare Files dialog box, you’ll find a list of options. These options determine which aspects of the workbooks will be included in the comparison report. You can select or deselect options such as Formulas, Macros, and Cell Format to tailor the comparison to your specific needs. Alternatively, click Select All to include all aspects in the comparison.
-
Execute the Comparison: Click OK to initiate the comparison process. Spreadsheet Compare will analyze the selected workbooks based on your chosen options.
Handling Password-Protected Workbooks: If you encounter an “Unable to open workbook” message, it likely indicates that one or both of your workbooks are password protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. For more detailed information on managing passwords within Spreadsheet Compare, consult the Microsoft support documentation on how passwords and Spreadsheet Compare work together.
Interpreting the Comparison Results
Once the comparison is complete, the results are displayed in an intuitive two-pane grid. The left pane presents the “Compare” workbook (typically the older version), while the right pane displays the “To” workbook (usually the newer one). A detailed pane below the grids provides specific information about the identified differences.
Spreadsheet Compare uses a color-coding system to highlight changes, making it easy to quickly grasp the types of modifications made.
-
Worksheet Navigation: The side-by-side grid displays corresponding worksheets from each file for comparison. If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons located on the horizontal scroll bar.
Note: Hidden worksheets are also included in the comparison and will be displayed in the results.
-
Color-Coded Differences: Differences are visually represented using cell fill colors and text font colors, each indicating a specific type of change. For instance, cells containing “entered values” (cells with manually inputted data rather than formulas) are highlighted with a green fill color in the grid and a green font in the results list pane. A legend in the lower-left pane clearly explains the meaning of each color code.
Example Scenario: Imagine you are comparing quarterly sales reports. In an earlier version, the Q4 results were preliminary. The updated workbook contains the final Q4 figures in column E. The comparison results would highlight cells E2:E5 in both versions with a green fill, signifying a change in entered values. Consequently, the calculated “Year-to-Date” (YTD) totals in column F, which depend on these values, would also be highlighted (e.g., cells F2:F4 and E6:F6) with a blue-green fill, indicating a change in calculated values.
Furthermore, cell F5 might show a change not only in value but also in the underlying formula. If the original formula in F5 was incorrect (e.g., =SUM(B5:D5) omitting Q4), and the updated version corrects it to =SUM(B5:E5), Spreadsheet Compare will flag this formula correction as a significant difference.
- Adjusting Cell Width: If cell content is truncated due to narrow column widths, simply click Resize Cells to Fit to automatically adjust column widths for optimal readability.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the “Inquire” add-in, which provides a dedicated “Inquire” tab in the Excel ribbon upon activation. This add-in expands your analytical capabilities within Excel itself. The Inquire tab enables you to analyze workbook structures, visualize relationships between cells, worksheets, and even external workbooks, and perform tasks like cleaning up excessive cell formatting within a worksheet. While Spreadsheet Compare is a standalone application for comparing files, the Inquire add-in offers integrated analysis tools within Excel.
If the Inquire tab is not visible in your Excel ribbon, you may need to activate it. Refer to Microsoft’s guide on Turn on the Inquire add-in for step-by-step instructions. To delve deeper into the functionalities of the Inquire add-in, explore the resources on What you can do with Spreadsheet Inquire.
Advanced Solutions for Enterprise Environments
For organizations managing mission-critical Excel workbooks and Access databases, Microsoft provides robust enterprise-level solutions. Microsoft Audit and Control Management Server offers advanced change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides comprehensive inventory and analysis capabilities. These tools collectively aim to mitigate risks associated with end-user developed tools in Excel and Access, offering enhanced control and oversight in corporate environments.
For a broader overview of Spreadsheet Compare and its capabilities, you can also consult Microsoft’s Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare, you can significantly enhance your efficiency in identifying and understanding differences between Excel sheets, ensuring data accuracy and informed decision-making.
