Comparing two Excel sheets to identify differences can be a common yet crucial task for professionals in various fields. Whether you are auditing financial data, tracking changes in project spreadsheets, or ensuring data consistency across different versions of a workbook, knowing how to effectively compare Excel sheets is an invaluable skill. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose, allowing you to generate detailed reports on the discrepancies and issues it detects.
Important Note: Before we proceed, it’s essential to understand that Spreadsheet Compare is not available in all versions of Microsoft Office. This tool is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a standard version of Office, you may need to consider upgrading or exploring alternative methods for comparing Excel sheets.
Accessing Spreadsheet Compare
To begin using Spreadsheet Compare, you first need to locate and open the application. Here’s how you can do it:
- Via the Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: If you don’t immediately see “Spreadsheet Compare” in your Start menu, simply start typing “Spreadsheet Compare”.
- Select the Application: The “Spreadsheet Compare” option should appear in the search results. Click on it to launch the application.
Alt text: Locating and launching Spreadsheet Compare application from the Windows Start Menu by searching for it.
Once opened, Spreadsheet Compare provides a user-friendly interface to facilitate your Excel sheet comparisons. Alongside Spreadsheet Compare, users of Microsoft Access might also find “Microsoft Database Compare,” a similar companion tool for comparing Access databases, available in the same Office Professional Plus and Microsoft 365 Apps for enterprise editions.
Step-by-Step Guide to Compare Two Excel Workbooks
Let’s walk through the process of comparing two Excel workbooks for differences using Spreadsheet Compare:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on 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 on the blue folder icon situated next to it. This will open a browse window, allowing you to select the earlier version of your Excel workbook. You can choose files from your local computer, network locations, or even enter a web address if your workbooks are stored online.
-
Choose the Revised Workbook: Next, find the To box and click on the green folder icon beside it. Browse to and select the workbook you want to compare against the original version. This is typically the newer or revised version of your Excel file. Click OK after selecting both workbooks.
Pro Tip: You can efficiently compare two files that have the same name as long as they are saved in different folders. This is particularly useful when working with version-controlled documents.
-
Customize Comparison Options: In the left pane of the Compare Files dialog, you’ll find a list of options that determine what aspects of the workbooks will be compared. You can check or uncheck options such as Formulas, Macros, Cell Format, and more. For a comprehensive comparison, you can simply click Select All. Choose the options relevant to your comparison needs.
-
Run the Comparison: Once you have selected your workbooks and chosen your comparison options, click OK to initiate the comparison process.
In scenarios where you encounter an “Unable to open workbook” message, it’s likely that one or both of the Excel workbooks are password protected. If this occurs, click OK on the message and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare is designed to work with password-protected files, ensuring you can still analyze and compare them.
Interpreting the Comparison Results
After running the comparison, the results are displayed in a clear, side-by-side grid within the Spreadsheet Compare window. The workbook selected in the “Compare” box (typically the older file) is shown on the left, and the workbook from the “To” box (usually the newer one) is on the right. A detailed pane beneath the grids provides further information about the detected differences.
Changes and discrepancies are visually highlighted using distinct colors, each representing a specific type of modification.
-
Worksheet Navigation: If your Excel workbooks contain multiple worksheets, each worksheet is compared against its counterpart in the other file. To navigate through the compared worksheets, use 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. -
Color-Coded Differences: Spreadsheet Compare uses color-coding to immediately draw your attention to the differences. Cell fill colors and text font colors are used to indicate the type of change. For example, cells containing “entered values” (cells with manually typed data rather than formulas) are often highlighted with a green fill color in the side-by-side grid and with green font in the results list pane. A legend in the lower-left pane clearly explains what each color signifies, allowing for quick interpretation of the comparison findings.
Alt text: Spreadsheet Compare displaying side-by-side comparison results of two Excel workbook versions, highlighting value and formula changes with color-coded cells.
Consider the example illustrated above. It shows a comparison where the Q4 results in an earlier workbook version were preliminary. The updated workbook incorporates the finalized figures for Q4 in column E.
In the comparison results, the cells E2:E5 in both versions are filled with green, indicating that entered values have been modified. Consequently, the calculated Year-to-Date (YTD) totals in column F have also changed. Cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying changes in calculated values.
Furthermore, cell F5 demonstrates another type of change. While the calculated result changed, the underlying issue was an incorrect formula in the earlier version. It was initially summing only B5:D5, omitting Q4. The updated workbook corrected the formula in F5 to =SUM(B5:E5). This highlights how Spreadsheet Compare can not only identify data changes but also formula discrepancies.
- Adjusting Cell Width: If cell content is truncated due to narrow column widths, simply click Resize Cells to Fit. This option automatically adjusts the column widths to ensure that all cell contents are fully visible, improving readability of the comparison results.
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. From this tab, you can perform workbook analysis, visualize relationships between cells, worksheets, and even external workbooks, and clean up excessive formatting within worksheets.
While Spreadsheet Compare is a dedicated application for comparing files externally, the Inquire add-in provides analytical capabilities directly within Excel. If you have two workbooks open in Excel that you wish to compare, you can conveniently launch Spreadsheet Compare using the “Compare Files” command found within the Inquire add-in, streamlining your workflow.
If you do not see the Inquire tab in your Excel ribbon, you may need to activate 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 further explore the functionalities of the Inquire add-in, search for “What you can do with Spreadsheet Inquire” for detailed information and guides.
Taking Further Steps
For organizations that heavily rely on “mission-critical” Excel workbooks and Access databases, it is worth considering Microsoft’s enterprise-level spreadsheet and database management tools. Solutions like Microsoft Audit and Control Management Server offer robust change management features specifically designed for Excel and Access files. Complementary tools such as Microsoft Discovery and Risk Assessment Server provide inventory and in-depth analysis capabilities. These enterprise solutions are geared towards mitigating risks associated with end-user developed tools in Excel and Access environments, offering enhanced control and oversight.
For a broader overview of Spreadsheet Compare and its capabilities, you can also explore resources like the “Overview of Spreadsheet Compare” available online. By mastering tools like Spreadsheet Compare, you can significantly enhance your efficiency and accuracy in managing and comparing Excel data.
