In today’s data-driven world, working with Excel files is a common practice across various industries. Often, you might find yourself needing to compare two Excel workbooks, perhaps different versions of the same report, to pinpoint changes, errors, or discrepancies. Whether you’re auditing financial data, tracking project updates, or ensuring data integrity, accurately comparing XLSX files is crucial. Microsoft Spreadsheet Compare is a powerful tool designed precisely for this purpose, allowing you to generate detailed reports on the differences it detects within your spreadsheets.
Important Note: It’s essential to remember that Spreadsheet Compare is not a standalone application available with every version of Microsoft Office. This utility comes bundled with specific editions: 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 Office, you may not have access to this tool.
Accessing Spreadsheet Compare
To begin using Spreadsheet Compare, the first step is to locate and open the application.
- Navigate to the Start menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t see it immediately, start typing “Spreadsheet Compare”.
- Click on the Spreadsheet Compare option when it appears to launch the application.
Alongside Spreadsheet Compare, users of Office Professional Plus and Microsoft 365 Apps for enterprise will also find Microsoft Database Compare, a similar tool designed for comparing Access databases.
Step-by-Step Guide to Compare XLSX Files
Once you have Spreadsheet Compare open, follow these steps to compare your Excel workbooks:
-
Initiate the comparison process by clicking Home > Compare Files. This action will open the Compare Files dialog box, which is the central hub for setting up your file comparison.
-
In the Compare Files dialog box, you’ll see two boxes labeled Compare and To. Next to the Compare box, click the blue folder icon. This prompts a file explorer window to open, allowing you to browse and select the earlier version of your Excel workbook (the file you want to use as the basis for comparison). Spreadsheet Compare isn’t limited to local files; you can also input a web address pointing to workbooks saved on websites or network locations.
-
Next, click the green folder icon located beside the To box. Again, a file explorer window will appear, this time for you to select the more recent version of the workbook – the one you wish to compare against the earlier version. After selecting both files, click OK in the file explorer window to return to the Compare Files dialog box.
Tip: It’s perfectly acceptable to compare two files that share the same name, provided they are stored in different folders. This is useful when you have versioned files saved in separate directories.
-
Before initiating the comparison, Spreadsheet Compare offers options to customize the comparison report. In the left pane of the Compare Files dialog box, you will find a list of aspects you can choose to include or exclude from the comparison. These options include Formulas, Macros, and Cell Format. You can selectively check or uncheck these options based on your specific comparison needs. If you want a comprehensive comparison encompassing all aspects, simply click Select All.
-
Finally, click OK to start the XLSX file comparison. Spreadsheet Compare will analyze the selected workbooks based on your chosen options and generate a report.
In scenarios where you encounter an “Unable to open workbook” message, it is likely that one or both of the Excel workbooks are password protected. Click OK on the message prompt and you will be asked to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, provided you have the necessary credentials.
Interpreting Comparison Results
After Spreadsheet Compare completes its analysis, the results are presented in a clear, two-pane grid layout. The workbook you designated as the “Compare” file (typically the older version) is displayed on the left pane, while the “To” file (usually the newer version) is on the right. A detailed information pane is positioned below these grids, offering further insights into the detected differences.
Changes between the two XLSX files are visually highlighted using distinct colors, each color corresponding to a specific type of modification.
-
Within the side-by-side grid view, each worksheet from the first file is compared against the corresponding worksheet in the second file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons situated on the horizontal scroll bar.
Note: Even if a worksheet is hidden in either of the original workbooks, Spreadsheet Compare will still include it in the comparison and display it in the results. Hidden worksheets are not excluded from the analysis.
-
Spreadsheet Compare uses both cell fill colors and text font colors to highlight differences, depending on the nature of the change. For instance, cells containing “entered values” (cells with manually typed data rather than formulas) are typically marked with a green fill color in the grid, and with a green font in the detailed results list. To understand the meaning of each color code, refer to the legend provided in the lower-left pane of the Spreadsheet Compare window. This legend serves as your key to interpreting the visual cues within the comparison report.
Consider the example illustrated above. In the earlier version of the workbook, the results for Quarter 4 (Q4) were preliminary. The updated version now incorporates the finalized figures for Q4 in column E.
In the comparison report, you’ll observe that cells E2 through E5 in both versions are filled with green. This green fill signifies that an entered value has been modified. Because these direct input values changed, the calculated values in the Year-to-Date (YTD) column were also affected. Consequently, cells F2 through F4 and E6 through F6 are highlighted with a blue-green fill, indicating a change in calculated values.
Furthermore, the calculated result in cell F5 has also changed. However, a more critical point is that in the older version, the formula in cell F5 was actually incorrect. It was only summing cells B5 through D5, inadvertently omitting the value for Q4. In the updated workbook, the formula in F5 has been corrected to =SUM(B5:E5). This example highlights how Spreadsheet Compare can not only pinpoint data changes but also help identify formula errors between different versions of your XLSX files.
- If you find that the cell contents are truncated or not fully visible due to narrow column widths in the comparison grid, Spreadsheet Compare provides a convenient Resize Cells to Fit option. Clicking this will automatically adjust the column widths to ensure that you can see the complete content of each cell, improving readability and analysis of the comparison results.
Leveraging Excel’s Inquire Add-in
Beyond the dedicated comparison capabilities of Spreadsheet Compare, Excel 2013 and later versions offer an “Inquire” add-in that provides a suite of analytical tools. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can perform in-depth workbook analysis, visualize relationships between cells, worksheets, and even links to external workbooks. The Inquire add-in also includes tools for cleaning up excessive formatting within a worksheet, which can be beneficial for improving workbook performance and reducing file size.
If you have two Excel workbooks currently open and wish to compare them, you can directly launch Spreadsheet Compare from within Excel by utilizing the “Compare Files” command located within the Inquire add-in tab. This provides a seamless workflow for initiating comparisons without leaving the Excel environment.
If you do not see the Inquire tab in your Excel ribbon, you may need to enable it manually. Detailed instructions on how to activate the Inquire add-in can be found in Microsoft’s documentation on “Turn on the Inquire add-in”. To delve deeper into the functionalities and features of the Inquire add-in, Microsoft provides a comprehensive resource titled “What you can do with Spreadsheet Inquire”.
Further Enhancements for Excel and Access Management
For organizations heavily reliant on “mission-critical” Excel workbooks and Access databases, Microsoft offers advanced management tools designed to enhance control and mitigate risks. Microsoft Audit and Control Management Server delivers robust change management capabilities specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis features, helping organizations gain better visibility and understanding of their user-developed Excel and Access tools. These server-based solutions are aimed at reducing risks associated with end-user computing and ensuring greater governance over critical data assets.
For a broader overview of Spreadsheet Compare and its place within Microsoft’s suite of management tools, you can refer to the “Overview of Spreadsheet Compare” resource.
