Working with Excel often involves managing multiple versions of spreadsheets or similar datasets across different files. Whether you’re tracking changes, auditing data, or ensuring consistency, the ability to Compare Two Sheets In Excel is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to highlight the discrepancies between Excel workbooks, making it easier to pinpoint changes and potential issues.
Important: Microsoft Spreadsheet Compare is a specialized tool included in 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 feature.
Launching Spreadsheet Compare
To begin, you’ll need to open the Spreadsheet Compare application.
On your Windows Start menu, simply type Spreadsheet Compare. The option should appear, allowing you to launch the program directly. Click on Spreadsheet Compare to open it.
Alongside Spreadsheet Compare, Microsoft offers a similar tool for Access databases called Microsoft Database Compare, which is also part of the Office Professional Plus suite and Microsoft 365 Apps for enterprise.
Step-by-Step Guide: Comparing Two Excel Sheets
Spreadsheet Compare simplifies the process of contrasting two Excel sheets to identify differences. Follow these steps to effectively compare two sheets in Excel:
-
Initiate the comparison by clicking Home > Compare Files.
This action will open the Compare Files dialog box, which is your starting point for selecting the sheets you want to analyze.
-
Specify the earlier version of your Excel workbook by clicking the blue folder icon next to the Compare field. This will allow you to browse your files.
Navigate to the location of the original Excel file you wish to use for comparison. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Next, designate the revised or newer workbook by clicking the green folder icon beside the To field. Locate and select the Excel workbook you want to compare against the earlier version, and then click OK.
Tip: You can easily compare two files even if they share the same name, as long as they are saved in different folders. This is particularly useful for version control.
-
Customize your comparison by choosing the elements you want to examine in the left pane. You can check or uncheck options such as Formulas, Macros, and Cell Format to tailor the report to your specific needs. If you want a comprehensive comparison, simply click Select All.
-
Execute the comparison process by clicking OK. Spreadsheet Compare will then analyze the selected workbooks based on your chosen options.
If a message “Unable to open workbook” appears, it typically indicates that one or both of the workbooks are password-protected. Click OK and enter the password for the respective workbook when prompted. For further information on password handling within Spreadsheet Compare, refer to how passwords and Spreadsheet Compare work together.
Upon completion, the comparison results are displayed in a clear, two-pane grid. The left pane represents the “Compare” file (usually the older version), while the right pane shows the “To” file (typically the newer one). A detailed pane below the grids provides specific information about the detected changes. Differences are visually highlighted with distinct colors, each representing a specific type of modification.
Understanding the Comparison Results
Interpreting the results is straightforward. Spreadsheet Compare presents a side-by-side view of the worksheets from each file, aligning corresponding sheets for easy comparison. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons located on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and will be displayed in the results, ensuring no changes are missed.
Differences are highlighted using cell fill colors and text font colors to quickly draw your attention to modifications. For example, cells containing “entered values” (cells with manually inputted data rather than formulas) are marked with a green fill color in the side-by-side grid and a green font in the results list pane. The legend in the lower-left pane clearly explains the meaning of each color code.
Consider the example illustrated above. In the earlier version, the results for Q4 were preliminary. The updated workbook provides finalized figures in column E for Q4.
In the comparison output, cells E2:E5 in both versions are filled with green, indicating a change in an entered value. Consequently, the calculated values in the YTD column have also changed. Cells F2:F4 and E6:F6 are highlighted with a blue-green fill, signifying a change in calculated values.
Furthermore, cell F5 also shows a change in the calculated result. However, the underlying cause is more significant: the original formula in the older version was incorrect, summing only B5:D5 and omitting the Q4 value. In the updated workbook, the formula in F5 was corrected to =SUM(B5:E5). Spreadsheet Compare not only highlights data changes but also formula modifications, which is essential for data integrity.
If cell contents are truncated due to column width, simply click Resize Cells to Fit to automatically adjust column widths for optimal readability.
Enhance Excel with the 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. Activating this add-in expands your analytical capabilities within Excel itself. From the Inquire tab, you can analyze workbook structures, visualize relationships between cells, worksheets, and even external workbooks, and streamline your spreadsheets by removing excessive formatting.
While Spreadsheet Compare is ideal for side-by-side workbook comparisons, the Inquire add-in offers a suite of tools directly within Excel for deeper workbook analysis. If you have two workbooks open in Excel and need to quickly compare them, you can access Spreadsheet Compare features through the “Compare Files” command available within the Inquire add-in.
If the Inquire tab is not visible in your Excel ribbon, you may need to enable it. Refer to Turn on the Inquire add-in for instructions. To explore the full potential of the Inquire add-in tools, see What you can do with Spreadsheet Inquire.
Taking Excel Management Further
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, consider implementing Microsoft’s comprehensive management solutions. Microsoft Audit and Control Management Server offers robust change management features tailored for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and in-depth analysis capabilities. These server solutions are designed to mitigate risks associated with end-user developed tools in Excel and Access, offering enhanced control and oversight.
Further explore the capabilities of Spreadsheet Compare with the Overview of Spreadsheet Compare.
