Working with spreadsheets often involves managing multiple versions of the same file or comparing data across different workbooks. Identifying discrepancies, errors, or intentional changes between these spreadsheets can be a time-consuming and error-prone manual task. Fortunately, Microsoft provides a built-in tool called Spreadsheet Compare, designed to streamline this process and highlight the differences between two Excel files efficiently. This guide will walk you through how to use Spreadsheet Compare to effectively compare two spreadsheets and pinpoint exactly what has changed.
Getting Started with Spreadsheet Compare
Spreadsheet Compare is a powerful utility included with specific versions of Microsoft Office. It’s important to note that this tool is available if you have Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. If you have one of these versions, you can access Spreadsheet Compare directly from your computer’s Start menu.
To open Spreadsheet Compare, simply navigate to your Start menu and look for Spreadsheet Compare. If you don’t immediately see it, start typing “Spreadsheet Compare,” and the option should appear for you to select.
Alongside Spreadsheet Compare, Microsoft also offers a similar tool for Access databases called Microsoft Database Compare. While this article focuses on spreadsheets, it’s worth knowing that a comparable tool exists for Access users within the same Office Professional Plus or Microsoft 365 Apps for enterprise suites.
Step-by-Step Guide: Comparing Excel Workbooks
Once you have Spreadsheet Compare open, comparing two Excel workbooks is a straightforward process. Follow these steps to initiate a comparison:
-
Begin by clicking on Home > Compare Files. This action will open the Compare Files dialog box, which is your control center for selecting the spreadsheets you wish to compare.
-
In the Compare Files dialog box, you’ll see a Compare box. Click the blue folder icon located next to this box. This will allow you to browse your computer or network to find and select the earlier version of the Excel workbook you want to analyze. Spreadsheet Compare is versatile; you can choose files stored locally, on a network drive, or even enter a web address if your workbooks are saved on a website.
-
Next, locate the To box in the dialog. Click the green folder icon beside it. Browse to and select the workbook that you want to compare against the earlier version you just chose. After selecting the second workbook, click OK in the file selection dialog.
Tip: Spreadsheet Compare allows you to compare two files even if they have the exact same name, as long as they are saved in different folders. This is particularly useful when you have versions of a file stored in separate directories.
-
Before running the comparison, take a look at the left pane of the Compare Files dialog box. Here, you can customize the comparison report by selecting what types of differences you want Spreadsheet Compare to focus on. You can check or uncheck options such as Formulas, Macros, and Cell Format. If you want a comprehensive comparison of everything, simply click Select All.
-
Finally, click OK to initiate the comparison. Spreadsheet Compare will then analyze the two workbooks based on your selected options and generate a detailed report of the differences.
If you encounter an “Unable to open workbook” message, it’s likely that one or both of the workbooks are password protected. Click OK on the message, and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, allowing you to compare them as long as you provide the correct password.
Understanding Comparison Results
Once Spreadsheet Compare has finished analyzing the two workbooks, the results are presented in an easy-to-understand two-pane grid. The workbook on the left side of the grid represents the “Compare” file (typically the older version), and the workbook on the right is the “To” file (usually the newer version). Below these grids, a details pane provides further information about the detected changes.
Changes within the spreadsheets are highlighted using color-coding, with each color representing a specific type of difference.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, Spreadsheet Compare will compare them sheet by sheet. You can navigate between worksheet comparisons by using the forward and back buttons located on the horizontal scroll bar within the grid.
Note: Even if a worksheet is hidden in one or both of the original workbooks, Spreadsheet Compare will still include it in the comparison and display it in the results.
-
Color-Coded Differences: Differences are visually distinguished by cell fill colors or text font colors, depending on the nature of the change. For instance, cells containing “entered values” (cells that are not formulas) that have been modified are typically highlighted with a green fill color in the side-by-side grid. In the details pane below, these changes might be indicated with green font. The lower-left pane of the Spreadsheet Compare window includes a legend that clearly explains what each color code signifies.
Consider the example of comparing quarterly sales data. If an earlier version of a workbook contained preliminary Q4 figures, and a later version has the finalized numbers, Spreadsheet Compare would clearly highlight these changes. In the example results shown, cells E2:E5 in both versions are filled with green, indicating that entered values have changed. Because these values were updated, the calculated year-to-date (YTD) results in column F also changed. Cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying that calculated values have been modified.
Furthermore, cell F5 also shows a change. However, in this case, the original formula in the earlier version was incorrect (=SUM(B5:D5), mistakenly omitting Q4 data). When the workbook was updated, the formula in F5 was corrected to =SUM(B5:E5). Spreadsheet Compare not only identifies data changes but can also highlight formula corrections and discrepancies.
-
Resizing Cells: If you find that some cells are too narrow to fully display their contents in the comparison grid, Spreadsheet Compare offers a helpful feature. Simply click Resize Cells to Fit, and the column widths will automatically adjust to ensure that you can read all cell contents without difficulty.
Excel’s Inquire Add-in
In addition to the dedicated Spreadsheet Compare tool, Excel 2013 and later versions include a powerful add-in called Inquire. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon, providing a range of analytical capabilities. From this tab, you can analyze workbook structures, visualize relationships between cells, worksheets, and even external workbooks, and perform tasks like cleaning up excessive cell formatting within a worksheet.
If you frequently need to compare workbooks directly from within Excel, the Inquire add-in offers a convenient way to access Spreadsheet Compare features. With two Excel workbooks open, you can initiate a comparison using the “Compare Files” command found within the Inquire tab.
If you do not see the Inquire tab in your Excel ribbon, you may need to activate it. You can find instructions on how to do this by searching for “Turn on the Inquire add-in”. To explore the full range of tools available in the Inquire add-in, you can refer to the resource “What you can do with Spreadsheet Inquire”.
Next Steps
For organizations that rely heavily on “mission-critical” Excel workbooks or Access databases, Microsoft provides enterprise-level management tools to further enhance control and reduce risks. Microsoft Audit and Control Management Server offers robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis capabilities, helping organizations to better manage and understand the risks associated with user-developed tools in Excel and Access environments.
To gain a broader understanding of Spreadsheet Compare and its place within Microsoft’s suite of data management tools, you can refer to the “Overview of Spreadsheet Compare” for a comprehensive overview.
