Comparing Excel worksheets is a common task for professionals who manage and analyze data. Whether you’re tracking changes, auditing data entries, or ensuring consistency across reports, identifying differences between Excel files is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to help you efficiently compare Excel workbooks and pinpoint discrepancies. This guide will walk you through how to use Spreadsheet Compare to effectively Compare Excel Worksheets and understand the results.
What is Spreadsheet Compare?
Spreadsheet Compare is a Microsoft tool specifically designed to compare two Excel workbook files or different versions of the same workbook. It generates a detailed report highlighting the differences it detects, such as changes in formulas, values, formatting, and even macros. This is invaluable for tasks like auditing spreadsheets for errors, verifying data integrity, or understanding modifications made between different versions of a file.
Important Note: Spreadsheet Compare is not included in all versions of Microsoft Office. It is available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are unsure whether you have access, follow the steps below to check if it’s installed.
Alongside Spreadsheet Compare, Microsoft also offers Microsoft Database Compare for Access databases, which is available with the same Office Professional Plus versions or Microsoft 365 Apps for enterprise.
How to Open Spreadsheet Compare
Opening Spreadsheet Compare is straightforward. Here’s how to access it:
- Access via Start Menu: Click the Start button on your Windows desktop.
- 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” application should appear in the search results. Click on it to open the program.
Once opened, Spreadsheet Compare provides a simple interface to begin comparing your Excel worksheets.
Step-by-Step Guide: Comparing Two Excel Workbooks
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare:
-
Initiate 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 (Compare): In the Compare Files dialog box, you’ll see a Compare box. Click the blue folder icon located next to it. This will open a file explorer window, allowing you to browse and select the earlier version or the first Excel workbook you wish to compare. You can select files stored locally on your computer, on a network drive, or even input a web address pointing to online workbooks.
-
Select the Revised Workbook (To): Next, locate the To box and click the green folder icon beside it. Browse to and select the second Excel workbook that you want to compare against the first one. Click OK to confirm your selection.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, provided they are saved in different folders. This is useful when comparing versions of the same report saved in different locations.
-
Choose Comparison Options: Before running the comparison, you can specify what elements of the workbooks you want to analyze. In the left pane of the Compare Files dialog box, you’ll find options such as Formulas, Macros, and Cell Format. Check the boxes next to the elements you want to include in the comparison. If you want to compare everything, 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 selected options and generate a report of the differences.
Handling Password-Protected Workbooks: If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK on the message 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 protected files.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid within Spreadsheet Compare. The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). A details pane appears below the grids, providing a summary of the changes.
-
Side-by-Side Worksheet Comparison: Each worksheet in the compared workbooks is analyzed and displayed side-by-side. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Importantly, even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences found. Cells with changes are marked with fill colors or text font colors, depending on the nature of the modification. For instance, cells containing “entered values” (values not derived from formulas) that have been changed are typically highlighted with a green fill color in the side-by-side grid and a green font in the results list pane. A legend in the lower-left pane clearly explains what each color represents.
Example Interpretation: In the example result shown, if cells E2:E5 in both versions are filled with green, it indicates that entered values in these cells have been modified. Consequently, if the calculated results in the YTD column (cells F2:F4 and E6:F6) are marked with a blue-green fill, it means the calculated values have changed as a result of the updated entered values.
Furthermore, the example highlights a corrected formula in cell F5 of the newer version. The earlier version had an incorrect formula (=SUM(B5:D5)), which was updated to correctly sum all relevant cells (=SUM(B5:E5)). This demonstrates how Spreadsheet Compare can help identify not only data changes but also formula errors and corrections.
- Resize Cells for Better Visibility: If cell contents are truncated due to column width, you can click Resize Cells to Fit. This feature automatically adjusts column widths to ensure that you can view the complete content of each cell, making it easier to review the comparison results.
Leveraging Excel’s Inquire Add-in
For users working within Excel 2013 and later versions, the Inquire add-in offers a complementary set of tools for workbook analysis. While Spreadsheet Compare is a standalone application, the Inquire add-in integrates directly into Excel, providing an “Inquire” tab on the ribbon.
The Inquire add-in includes features for:
- Workbook Analysis: Gaining insights into workbook structure, formulas, and potential issues.
- Relationship Visualization: Mapping relationships between cells, worksheets, and even external workbooks to understand data dependencies.
- Excess Formatting Cleanup: Removing unnecessary formatting to reduce file size and improve workbook performance.
Notably, if you have two Excel workbooks already open and wish to compare them using Spreadsheet Compare’s engine, you can do so directly from Excel through the Inquire add-in by using the Compare Files command within the Inquire tab.
If you don’t see the Inquire tab in your Excel, you may need to activate it. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Microsoft Office support documentation. To explore the full capabilities of the Inquire add-in, refer to resources detailing “What you can do with Spreadsheet Inquire”.
Conclusion
Spreadsheet Compare is an indispensable tool for anyone who needs to effectively compare Excel worksheets. Its ability to quickly and accurately identify differences in data, formulas, and formatting makes it invaluable for data validation, change tracking, and ensuring the integrity of your spreadsheets. By using Spreadsheet Compare regularly, you can maintain better control over your Excel data, reduce errors, and improve the reliability of your reports and analyses. For organizations managing critical Excel workbooks, consider exploring Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server for enhanced change management and risk mitigation.
Further Reading:
