Imagine you’re working with multiple versions of Excel workbooks, or perhaps different spreadsheets altogether, and you need to pinpoint the exact changes made or discrepancies between them. Whether it’s identifying manual entries instead of formulas, spotting broken calculations, or simply tracking modifications, manually comparing Excel sheets can be time-consuming and prone to errors. Fortunately, Microsoft provides a built-in tool called Spreadsheet Compare, designed to generate detailed reports on the differences and potential issues it detects between two Excel files.
Important Note: Microsoft Spreadsheet Compare is a powerful tool, but it’s exclusively available with specific Microsoft Office suites: Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to utilize this functionality.
Getting Started with Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward if you have a compatible Office version.
Opening Spreadsheet Compare
-
From the Start Menu: Navigate to your Windows Start menu.
-
Locate Spreadsheet Compare: Look for Spreadsheet Compare in your applications list. If you don’t see it immediately, simply start typing “Spreadsheet Compare”. The option should appear for you to select.
Alongside Spreadsheet Compare, Microsoft also offers a similar tool for Access databases, called Microsoft Database Compare. Both tools are designed for users of Office Professional Plus editions or Microsoft 365 Apps for enterprise, providing robust comparison capabilities for different Microsoft Office file types.
Step-by-Step Guide to Compare Two Excel Workbooks
Once you have Spreadsheet Compare open, comparing your Excel files is a simple process:
-
Initiate File Comparison: Click on Home > Compare Files. This action will open the Compare Files dialog box, which is your control center for selecting the workbooks you want to analyze.
-
Select the Original Workbook (Compare File): In the Compare Files dialog, locate the Compare box. Click the blue folder icon situated next to it. This will open a file browser window, allowing you to navigate to the location of the older or original version of your Excel workbook. You can select files stored locally on your computer, within a network, or even specify a web address if your workbooks are saved on a website.
-
Choose the Revised Workbook (To File): Next, find the To box in the dialog. Click the green folder icon beside it. Again, a file browser will appear. This time, browse to and select the more recent version of the workbook that you want to compare against the original. Click OK once you’ve selected the file.
Tip: It’s perfectly acceptable to compare two files that have the same name, as long as they are saved in different folders. This is particularly useful when you have version-controlled files.
-
Customize Comparison Options: Look at the left pane of the Compare Files dialog. Here, you can fine-tune what aspects of the workbooks Spreadsheet Compare will analyze. You’ll see a list of options such as Formulas, Macros, Cell Format, and others. Check or uncheck these boxes to specify what elements you want to include in the comparison report. If you want a comprehensive comparison, simply click Select All.
-
Run the Comparison: Once you’ve selected your files and chosen your comparison options, click OK to initiate the comparison process. Spreadsheet Compare will now analyze the two workbooks based on your settings.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message during the comparison, it likely indicates that one or both of your workbooks are password protected. Click OK on the message. You will then be prompted to enter the password for the protected workbook. For more information on how passwords are handled in Spreadsheet Compare, refer to Microsoft’s guide on managing passwords for file analysis and comparison.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare presents the findings in a clear, two-pane grid layout.
-
Side-by-Side Grid View: The left pane of the grid displays the workbook you selected as the “Compare” file (typically the older version). The right pane shows the “To” file (usually the newer version). Each pane represents the workbooks being compared side by side, making it easy to visually identify differences. If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons located on the horizontal scroll bar at the bottom of the grid.
Note: Even if a worksheet is hidden within the Excel file, Spreadsheet Compare will still include it in the comparison and display it in the results. Hidden sheets are not excluded from the analysis.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences it finds between the workbooks. Changes are visually represented by cell fill colors or text font colors, depending on the nature of the modification. For example, cells containing “entered values” (cells with manually typed data, not formulas) are often highlighted with a green fill color in the side-by-side grid. In the detailed results list pane (typically located below the grids), these changes might also be indicated with a green font. A legend explaining the color codes is usually provided in the lower-left pane of the Spreadsheet Compare window.
Example Interpretation: Consider a scenario where you are comparing quarterly sales reports. In an earlier version of a workbook, the Q4 results might have been preliminary estimates. The updated version contains the finalized Q4 figures in column E. In the comparison results:
* Cells E2:E5 in both workbook versions might have a green fill, indicating that an entered value has been changed. This signifies the updated Q4 sales figures.
* Consequently, calculated results in the "Year-to-Date" (YTD) column (e.g., column F) may also change. Cells F2:F4 and E6:F6 could have a blue-green fill, signifying that a calculated value has changed as a result of the updated input values.
* In cell F5, the calculated result might have changed, but more importantly, Spreadsheet Compare might reveal that the formula in the earlier version was incorrect (e.g., it only summed B5:D5, omitting Q4 data in column E). The updated workbook might have corrected formula in F5 to `=SUM(B5:E5)`. Spreadsheet Compare would highlight this formula correction as a significant difference.- Resize Cells for Better Viewing: If some cells are too narrow to fully display their contents in the comparison grid, you can click Resize Cells to Fit. This option adjusts column widths to ensure you can read all cell data clearly within the comparison view.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer another valuable tool: the Inquire add-in. Activating the Inquire add-in in Excel adds an “Inquire” tab to your Excel ribbon, providing a range of analytical capabilities. From the Inquire tab, you can:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even connections to external workbooks.
- Clean Excess Formatting: Identify and remove unnecessary formatting that can bloat file size and potentially cause compatibility issues.
While Spreadsheet Compare is a standalone application for direct file comparison, the Inquire add-in enhances Excel itself with analysis and auditing features. Notably, if you have two workbooks open directly in Excel, you can still launch Spreadsheet Compare via the “Compare Files” command within the Inquire add-in (if activated).
If you don’t see the Inquire tab in your Excel ribbon, you may need to enable it. See Microsoft’s guide on how to turn on the Inquire add-in. To explore the full potential of the Inquire add-in’s tools, refer to Microsoft’s documentation on what you can do with Spreadsheet Inquire.
Advanced Solutions for Critical Spreadsheets
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, Microsoft offers more comprehensive management solutions. Microsoft Audit and Control Management Server provides advanced change management and auditing features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server delivers inventory and analysis capabilities, helping organizations to manage and mitigate risks associated with user-developed tools in Excel and Access environments.
Next Steps
Comparing Excel worksheets and highlighting differences is crucial for accuracy and version control. Microsoft Spreadsheet Compare provides an efficient and user-friendly way to achieve this. Explore this tool to streamline your workflow and ensure the integrity of your Excel data. For a broader overview of Spreadsheet Compare, you can also consult Microsoft’s overview documentation.
