Comparing Excel workbooks is a common task for professionals who manage and analyze data. Whether you’re tracking changes, auditing for errors, or merging information, knowing how to effectively compare workbooks is crucial. Microsoft Spreadsheet Compare, a powerful tool available with certain Office versions, simplifies this process. This guide will walk you through how to use Spreadsheet Compare to identify differences between Excel files, enhance your data accuracy, and streamline your workflow.
Spreadsheet Compare is a utility designed to highlight the discrepancies between two Excel workbooks or different versions of the same workbook. It’s particularly useful for pinpointing manual entries versus calculated totals, identifying broken formulas, and tracking modifications over time. This tool is an invaluable asset for maintaining data integrity and ensuring accuracy in your spreadsheets.
Important Note: Microsoft Spreadsheet Compare is 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 access this feature.
Accessing Spreadsheet Compare
To start using Spreadsheet Compare, the first step is to locate and open the application.
-
From the Start Menu: Click the Start button and look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare”. The application option should appear in the search results. Select it to open.
This image shows the process of accessing the Spreadsheet Compare application by searching for it in the Windows Start Menu.
Alongside Spreadsheet Compare, Microsoft offers a similar tool for Access databases called Microsoft Database Compare. This companion program, which also requires Office Professional Plus versions or Microsoft 365 Apps for enterprise, provides comparable functionality for Access database files.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once you have Spreadsheet Compare open, you can easily begin comparing your Excel workbooks. Follow these steps to initiate a comparison:
-
Initiate Comparison: In the Spreadsheet Compare window, click Home > Compare Files. This action will open the Compare Files dialog box.
-
Select the Original Workbook: In the Compare Files dialog box, locate the Compare box. Click the blue folder icon next to it to browse your files. Navigate to and select the earlier version of your Excel workbook that you wish to use as the basis for comparison. You can select files from your local computer, network locations, or even enter a web address if your workbooks are stored online.
-
Select the Revised Workbook: Next, find the To box and click the green folder icon beside it. Browse to and select the workbook you want to compare against the earlier version. This is typically the more recent version of the file. Click OK after selecting the files.
This image illustrates the Compare Files dialog box where users can select the ‘Compare’ and ‘To’ workbooks for comparison.
Tip: Spreadsheet Compare allows you to compare two files with identical names, provided they are saved in different folders. This is particularly useful when comparing versions of the same file stored in different locations.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options to customize your comparison. You can choose to focus on specific elements such as Formulas, Macros, Cell Format, and more. Select the checkboxes next to the aspects you want to include in the comparison. For a comprehensive analysis, you can simply click Select All.
-
Run the Comparison: After selecting your workbooks and comparison options, click OK to start the comparison process. Spreadsheet Compare will analyze the files based on your chosen settings.
If you encounter an “Unable to open workbook” message, it’s likely that one of the workbooks is password-protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the contents of password-protected files.
Understanding the Comparison Results
Once the comparison is complete, Spreadsheet Compare displays the results in a clear, two-pane grid. The left pane shows the “Compare” workbook (typically the older version), and the right pane displays the “To” workbook (usually the newer version). A detailed pane below the grids provides a legend and further information about the differences found.
-
Side-by-Side Worksheet Comparison: Each worksheet in the compared workbooks is analyzed against its counterpart in the other workbook. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located above the grid.
Note: Even if a worksheet is hidden in the original workbook, Spreadsheet Compare will still include it in the comparison and display it in the results.
-
Color-Coded Differences: Differences between the workbooks are highlighted using distinct colors in both the side-by-side grid and the results list. The color coding helps you quickly identify the type of change. For instance, cells containing “entered values” (cells with manually typed data rather than formulas) are typically highlighted with a green fill in the grid and a green font in the results list. The legend in the lower-left pane of the Spreadsheet Compare window clearly explains what each color represents.
This image displays a comparison result with color highlights, showing changes between two versions of an Excel workbook, with a legend explaining the color codes.
Consider an example where an earlier version of a workbook contains preliminary quarterly data, and the updated version includes final figures. In the comparison results, cells where the quarterly figures have been updated (entered values changed) will be marked with a green fill. Consequently, if these changes affect calculated values, such as year-to-date totals, those cells will be highlighted with a different color, perhaps blue-green, indicating a change in calculated values.
In scenarios where formulas have been corrected or updated, Spreadsheet Compare also highlights these changes. For example, if a formula in the older version incorrectly summed a range of cells, and this formula is corrected in the newer version, the cell containing the corrected formula will be highlighted, emphasizing not just a change in value but also a formula modification.
-
Adjusting Cell Width: If the content within cells is wider than the cell display, making it difficult to read, you can click Resize Cells to Fit. This option automatically adjusts the column widths to ensure that you can view the entire cell content without difficulty.
Leveraging Excel’s Inquire Add-in
In addition to Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in, which offers further analytical capabilities. Once activated, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can access tools to analyze workbooks, visualize relationships between cells and worksheets, and even clean up excessive formatting within a worksheet.
For comparing two currently open Excel workbooks, you can conveniently launch Spreadsheet Compare directly from Excel using the “Compare Files” command within the Inquire add-in.
If the Inquire tab is not visible in your Excel ribbon, you may need to enable it manually. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Excel help or online. To explore the full range of tools available in the Inquire add-in, search for “What you can do with Spreadsheet Inquire”.
Advanced Solutions for Excel Management
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers comprehensive inventory and analysis features. These server solutions are aimed at helping businesses mitigate risks associated with end-user developed tools in Excel and Access, ensuring better control, compliance, and data governance.
For a broader overview of Spreadsheet Compare and its features, you can refer to the Overview of Spreadsheet Compare provided by Microsoft.
By using Microsoft Spreadsheet Compare, you can efficiently and accurately Compare Excel Workbooks, ensuring data integrity and improving the reliability of your spreadsheet-based tasks. Whether you are tracking changes, auditing data, or ensuring consistency, Spreadsheet Compare is a valuable tool in your Excel toolkit.
