Comparing two Excel sheets to identify differences can be a crucial task for anyone working with data, from financial analysts to project managers. Whether you’re tracking changes across versions, auditing data entries, or ensuring consistency, pinpointing discrepancies quickly and accurately is essential. Microsoft Spreadsheet Compare is a powerful tool designed exactly for this purpose. This guide will walk you through how to use this utility to effectively compare two Excel files and highlight the differences, ensuring data integrity and saving you valuable time.
What is Spreadsheet Compare?
Spreadsheet Compare is a Microsoft tool designed to compare two Excel workbooks or different versions of the same workbook, and generate a report of the differences it finds. It is particularly useful for identifying potential problems, such as discrepancies in manually entered totals versus calculated ones, or broken formulas that might have crept in during updates.
Important Note: It’s worth noting that Spreadsheet Compare is not available in all versions of Microsoft Office. It’s 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 or home version of Office, you may not have access to this tool.
Step-by-Step Guide to Comparing Excel Sheets
Here’s how you can use Spreadsheet Compare to effectively analyze and highlight differences between two Excel workbooks:
-
Open Spreadsheet Compare:
- Navigate to your Start menu in Windows and look for Spreadsheet Compare.
- If you don’t see it immediately, simply type “Spreadsheet Compare” and select the application from the search results.
-
Select Files for Comparison:
- Once Spreadsheet Compare is open, click on Home > Compare Files. This will open the Compare Files dialog box.
- You’ll see two boxes: Compare and To.
- Click the blue folder icon next to the Compare box. Browse to and select the older or first version of your Excel workbook. You can select files from your computer, a network location, or even by entering a web address if your files are saved online.
- Next, click the green folder icon next to the To box. Browse to and select the second workbook you want to compare against the first one. Click OK.
Tip: You can easily compare two files even if they have the same name, as long as they are saved in different folders.
-
Choose Comparison Options:
- In the left pane of the Compare Files dialog box, you’ll find a list of options that determine what aspects of the workbooks will be compared. These options include Formulas, Macros, and Cell Format.
- Select the options that are relevant to your comparison. For a comprehensive comparison, you can simply check Select All.
-
Run the Comparison:
- After selecting your files and comparison options, click OK to start the comparison process.
If you encounter an “Unable to open workbook” message, it’s likely that one of your workbooks is password protected. Click OK and you will be prompted to enter the password for the protected workbook.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
-
Side-by-Side Grid: The left pane displays the workbook you selected as the “Compare” file (typically the older version), and the right pane shows the “To” file (typically the newer version). Each worksheet within the workbooks is compared side-by-side. If your workbooks have multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and shown in the results. -
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight the types of differences found between the sheets. The specific colors indicate the nature of the change, such as changes in values, formulas, or formatting. For example, cells containing values that have been manually entered (not formulas) and have changed are typically highlighted with a green fill color in the grid and a green font in the results list.
-
Legend Pane: The lower-left pane of the Spreadsheet Compare window acts as a legend, explaining what each color code represents. This is crucial for quickly interpreting the comparison results.
- Resize Cells: If the cell content is not fully visible due to column width, you can click Resize Cells to Fit to automatically adjust column widths for better readability.
Excel’s Inquire Add-in: An Alternative
For users working directly within Excel 2013 and later versions, there’s another powerful feature: the Inquire add-in. Activating this add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you gain access to tools that allow you to analyze workbooks, visualize relationships between cells and worksheets, and even clean up excessive formatting.
While Spreadsheet Compare is a standalone application for comparing files, the Inquire add-in provides analytical tools directly within Excel. If you need to compare two currently open workbooks in Excel, you can still launch Spreadsheet Compare via the “Compare Files” command found within the Inquire add-in, if enabled.
If you do not see the Inquire tab in your Excel, you may need to activate it. You can find instructions on how to Turn on the Inquire add-in. To understand more about the capabilities of this add-in, explore What you can do with Spreadsheet Inquire.
Conclusion
Spreadsheet Compare is an invaluable tool for anyone needing to meticulously compare Excel sheets and highlight differences. Whether you are auditing financial data, tracking project changes, or ensuring data accuracy across different versions of a workbook, this utility offers a robust and efficient solution. By clearly visualizing discrepancies, Spreadsheet Compare helps users maintain data integrity, troubleshoot issues, and ultimately work with greater confidence in their Excel data. For organizations relying heavily on Excel for critical operations, leveraging tools like Spreadsheet Compare and considering Microsoft’s broader management tools can significantly mitigate risks associated with end-user developed spreadsheets.
