In today’s data-driven world, working with multiple Excel documents or versions of the same document is a common scenario. Whether you’re tracking changes, auditing data, or ensuring consistency, the ability to effectively compare two Excel files is crucial. Microsoft Spreadsheet Compare, a powerful tool available with specific Office versions, allows you to pinpoint differences and potential issues within your workbooks. This guide will walk you through how to use Spreadsheet Compare to analyze and understand the variations between your Excel documents.
Opening Spreadsheet Compare
First, it’s important to note that Spreadsheet Compare is not included in all versions of Microsoft Office. It’s specifically part of Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you have one of these versions, accessing Spreadsheet Compare is straightforward.
From your Windows Start menu, simply type Spreadsheet Compare. You should see the application option appear in the search results. Click on Spreadsheet Compare to launch the program.
This image shows how to locate and open the Spreadsheet Compare application from the Windows Start Menu by searching for “Spreadsheet Compare”.
It’s worth mentioning that Microsoft also offers a similar tool for Access databases called Microsoft Database Compare, which is available with the same Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once you have Spreadsheet Compare open, you can begin comparing your Excel files. Here’s a detailed step-by-step process:
-
Initiate the 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 ‘Compare’ Workbook (Older Version): In the Compare Files dialog, you’ll see a Compare box. Click on the blue folder icon situated next to this box. This will open a file explorer window, allowing you to browse and locate the earlier version of your Excel workbook. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Select the ‘To’ Workbook (Newer Version): Next, locate the To box in the dialog. Click on the green folder icon next to it. Again, a file explorer window will appear, enabling you to choose the workbook you want to compare against the earlier version. After selecting the desired workbook, click OK.
Tip: Spreadsheet Compare allows you to compare two files with the same name, as long as they are saved in different folders. This is particularly useful when comparing different versions of the same document saved in separate directories.
-
Choose Comparison Options: On the left side of the Compare Files dialog, you’ll find a pane with various options. These options let you customize the comparison report by selecting specific elements to analyze. You can choose to compare Formulas, Macros, Cell Formats, and more. For a comprehensive comparison, you can simply check the Select All option.
-
Run the Comparison: After selecting your workbooks and comparison options, click OK to initiate the comparison process.
If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, and you can find more information about password management within Spreadsheet Compare in Microsoft’s documentation.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid within Spreadsheet Compare. The workbook you selected as the “Compare” file (typically the older version) is shown on the left pane, while the “To” file (usually the newer version) is displayed on the right. A detailed pane below the grids provides further information about the differences.
Changes and discrepancies between the two workbooks are highlighted with distinct colors, making it easy to visually identify variations. The color-coding system is intuitive and helps categorize the types of changes detected.
This image illustrates the comparison results in Spreadsheet Compare, highlighting different types of changes between two Excel workbook versions using color-coded cells.
Here’s a breakdown of how to interpret the results:
-
Worksheet Navigation: If your workbooks contain multiple worksheets, Spreadsheet Compare compares corresponding worksheets in each file. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar located above the grids. Importantly, even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: The color-coding is key to understanding the type of changes. For instance, cells containing “entered values” (cells with manually inputted data, not formulas) that have been modified are typically highlighted with a green fill color in the grid and a green font in the results list pane. The lower-left pane of Spreadsheet Compare includes a legend that clearly explains the meaning of each color code.
-
Example Interpretation: Consider an example where you’re comparing quarterly sales data. If the Q4 figures were updated in the newer version of the workbook, cells in the “entered values” category related to Q4 would be highlighted in green. Furthermore, if formulas relying on these updated values have changed, the cells containing those formulas might be highlighted with a different color, such as blue-green, indicating a change in calculated values. This helps you quickly understand not only what data has changed but also how those changes have propagated through formulas in your workbook.
-
Formula Corrections: Spreadsheet Compare is also valuable for identifying formula errors. In the example above, if a formula was initially incorrect in the older version (e.g., missing a range in a SUM function), and it was corrected in the newer version, Spreadsheet Compare would highlight this change. This is immensely helpful for ensuring the accuracy and integrity of your calculations.
-
Adjusting Cell Width: If cell contents are truncated due to narrow column widths, Spreadsheet Compare provides a convenient Resize Cells to Fit option. This ensures you can view the complete content of each cell within the comparison grid.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides a suite of powerful analysis tools. Once enabled, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can perform workbook analysis, visualize relationships between cells and worksheets, compare workbooks directly within Excel, and even clean up excessive formatting within a worksheet.
While Spreadsheet Compare is a dedicated application for detailed file comparison, the Inquire add-in offers complementary analysis features directly within Excel. If you frequently need to analyze and compare Excel workbooks, exploring the Inquire add-in is highly recommended. Instructions on how to activate the Inquire add-in can be found in Microsoft’s documentation.
Next Steps for Advanced Excel Management
For organizations dealing with “mission-critical” Excel workbooks and Access databases, Microsoft offers advanced management tools like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These server-based solutions provide robust change management, inventory, and analysis capabilities specifically designed to mitigate risks associated with user-developed tools in Excel and Access, particularly in enterprise environments.
To gain a broader understanding of Spreadsheet Compare and its capabilities, refer to the Overview of Spreadsheet Compare provided by Microsoft.
By mastering Spreadsheet Compare and exploring related tools like the Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the accuracy of your Excel data, leading to improved data integrity and decision-making.
