Comparing two Excel sheets is a common task for professionals who work with data. Whether you’re auditing financial records, tracking changes in project data, or ensuring data consistency across different versions of a workbook, identifying discrepancies quickly and accurately is crucial. Microsoft Spreadsheet Compare is a powerful tool designed to help you do just that. This guide will walk you through how to use Spreadsheet Compare to effectively compare two Excel files and understand the results, ensuring data integrity and saving you valuable time.
What is Spreadsheet Compare?
Spreadsheet Compare is a utility specifically designed to highlight the differences between two Excel workbooks or different versions of the same workbook. It’s an invaluable tool for identifying changes, potential errors, and inconsistencies. It can pinpoint differences in formulas, values, macros, and even cell formatting. This allows users to quickly grasp what has been altered between two spreadsheets without manually sifting through rows and columns.
Important Note: Spreadsheet Compare is a feature included in specific Microsoft Office suites, namely 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 tool.
How to Open Spreadsheet Compare
Opening Spreadsheet Compare is straightforward. Here’s how you can access it:
- Via the Start Menu: Click on the Start button in Windows.
- Type to Search: Begin typing “Spreadsheet Compare”.
- Select the Application: The “Spreadsheet Compare” application should appear in the search results. Click on it to open.
If you don’t immediately see it, typing the full name will ensure it appears. Alongside Spreadsheet Compare, you might also notice “Microsoft Database Compare,” a similar tool for Access databases, which is also part of the Office Professional Plus suite.
Step-by-Step Guide to Compare Two Excel Workbooks
Once you have Spreadsheet Compare open, follow these steps to compare your Excel files:
-
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 Older Workbook (Compare File): In the Compare Files dialog box, locate the Compare field. Click on the blue folder icon situated next to this field. This will open a browser window allowing you to navigate to and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Select the Newer Workbook (To File): Next, find the To field in the Compare Files dialog box. Click on the green folder icon beside it. Browse to and select the more recent version of the Excel workbook you wish to compare against the earlier version. Click OK after selecting the file.
Tip: You can effectively compare two files even if they share the same name, provided they are saved in different folders. This is particularly useful when comparing versions saved in different project directories or date-stamped folders.
-
Choose Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options. These options allow you to specify what aspects of the workbooks you want to compare. You can choose to compare Formulas, Macros, Cell Format, and other elements. Select the checkboxes next to the elements you want to include in the comparison. For a comprehensive comparison, you can simply click Select All.
-
Run the Comparison: Once you have selected your files and comparison options, click OK to initiate the comparison process. Spreadsheet Compare will analyze the two workbooks based on your chosen settings.
-
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 and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, ensuring you can still compare sensitive data.
Understanding the Comparison Results
After running the comparison, the results are presented in a clear, side-by-side grid layout. The left pane displays the “Compare” workbook (typically the older version), and the right pane shows the “To” workbook (usually the newer one). A detailed pane below the grids provides specific information about the differences found.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, each worksheet is compared against its counterpart in the other file. You can navigate through the worksheets using the forward and back buttons located on the horizontal scroll bar within the grid view.
Note: Even if a worksheet is hidden in the Excel file, Spreadsheet Compare will still include it in the comparison and display it in the results. This ensures no part of your workbook is overlooked during the comparison.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences it detects. Changes are indicated by cell fill colors or text font colors. For instance, cells containing “entered values” (cells with manually typed data rather than formulas) that have been modified are often highlighted with a green fill color in the grid and a green font in the results list pane. A legend in the lower-left pane clearly explains what each color signifies.
In the example result shown, the values for Q4 in the earlier workbook were preliminary. The updated workbook includes the finalized figures in column E for Q4.
The green fill in cells E2:E5 in both versions indicates that entered values have been changed. Consequently, the calculated values in the “YTD” column (Year-to-Date) have also changed. These calculated cells, F2:F4 and E6:F6, are marked with a blue-green fill, signifying a change in calculated values.
Cell F5 also shows a change in the calculated result. However, the more critical change is that in the earlier version, the formula in F5 was incorrect (=SUM(B5:D5), missing Q4’s value). In the updated workbook, the formula in F5 has been corrected to =SUM(B5:E5). This example illustrates how Spreadsheet Compare not only highlights data changes but also formula corrections.
- Resize Cells for Better Viewing: If cell content is truncated due to narrow column widths, click on Resize Cells to Fit. This feature automatically adjusts column widths to ensure all cell content is visible, improving readability of the comparison results.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers an “Inquire” add-in, available in Excel 2013 and later versions. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon, providing a suite of analytical tools. From this tab, you can:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even links to other workbooks.
- Clean Excess Formatting: Remove unnecessary formatting that can bloat file size and slow down performance.
- Compare Files Directly from Excel: If you have two workbooks open in Excel, you can use the “Compare Files” command within the Inquire tab to launch Spreadsheet Compare directly, streamlining your workflow.
If you don’t 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” in Excel help. To learn more about the capabilities of this add-in, explore “What you can do with Spreadsheet Inquire” within Excel’s help resources.
Next Steps for Advanced Excel Management
For organizations dealing with mission-critical Excel workbooks and Access databases, consider exploring Microsoft’s advanced management tools. Microsoft Audit and Control Management Server offers robust change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis capabilities. These tools are designed to mitigate risks associated with end-user developed tools in Excel and Access, ensuring better control and governance over critical data assets.
Further information on Spreadsheet Compare and related tools can be found in the Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare and Excel’s Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the accuracy of your Excel data, leading to more informed decisions and reduced errors.
