Manually comparing two spreadsheets in Excel can be a tedious and error-prone task. If you’ve ever wondered “What exactly changed in this updated spreadsheet?” or needed to track modifications across different versions of your Excel workbooks, you’re not alone. Fortunately, there are effective methods to Compare Two Spreadsheets In Excel and pinpoint the differences quickly and accurately. This guide will walk you through using Microsoft Spreadsheet Compare and discuss other approaches to streamline your spreadsheet comparison process.
Method 1: Using Microsoft Spreadsheet Compare (for specific Office versions)
Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose. However, it’s important to note that Spreadsheet Compare is only available in specific versions of Microsoft Office, namely: 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, here’s how to use it to compare two spreadsheets in Excel:
-
Open Spreadsheet Compare. You can usually find it by searching for “Spreadsheet Compare” in your Windows search bar.
-
Select Comparison Options. In the lower-left pane of the Spreadsheet Compare window, you’ll find a set of options to customize your comparison. These options allow you to specify what aspects of the workbooks you want to compare. Consider selecting options like Formulas, Cell Formatting, Macros, and more, depending on your needs. For a comprehensive comparison, you can simply click Select All.
-
Initiate File Comparison. On the Home tab of the Spreadsheet Compare ribbon, click on the Compare Files button. This action will open the Compare Files dialog box.
-
Choose Files for Comparison. In the Compare Files dialog box, you need to specify the two Excel files you want to compare.
- In the Compare row, browse to select the earlier version of your Excel workbook. You can navigate your local files, network locations, or even enter a web address if your workbooks are saved online.
- In the To row, browse to select the later version of your workbook – the one you want to compare against the earlier version.
Note: You can effectively compare two files even if they have the same name, as long as they are saved in different folders.
-
Run the Comparison. Once you have selected both the “Compare” and “To” files, click OK to start the comparison process.
Note: If you encounter an “Unable to open workbook” message, it’s likely that one or both of the workbooks are password protected. Click OK in the message box and you will be prompted to enter the password to proceed with the comparison.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, two-pane grid.
- The workbook you selected as the “Compare” file is displayed on the left pane.
- The workbook selected as the “To” file is shown on the right pane.
- A details pane below the grids provides a summary of the changes detected.
Spreadsheet Compare uses color highlighting to visually represent different types of changes between the two spreadsheets.
-
Worksheet Comparison: For each file, Spreadsheet Compare compares worksheets starting from the leftmost sheet. Even hidden worksheets are included in the comparison and displayed in Spreadsheet Compare.
-
Cell Content Display: If cell contents are truncated due to narrow columns, you can click Resize Cells to Fit to expand the columns and view the full content.
-
Color-Coded Differences: Different colors are used to highlight the type of change. For example, cells containing “entered values” (non-formula cells) are typically highlighted with a green fill color in the side-by-side grid and with a green font in the results list. The legend in the lower-left pane clearly explains what each color represents.
Other Ways to Work with Comparison Results
Spreadsheet Compare offers options to further utilize and share your comparison results:
- Export Results to Excel: To save the comparison results for easier review or sharing, click Home > Export Results. This will create an Excel file summarizing the differences.
- Copy Results to Clipboard: If you need to incorporate the comparison results into another document, such as a Microsoft Word report, click Home > Copy Results to Clipboard. This allows you to paste the results into other applications.
- Show Workbook Colors: To view the worksheets with their original formatting from Excel, click Home > Show Workbook Colors. This provides a high-fidelity view of each worksheet, closely resembling how they appear in Excel.
Why Compare Spreadsheets? Real-World Scenarios
Comparing spreadsheets is not just about identifying changes; it’s crucial for maintaining data integrity, ensuring accuracy, and improving workflow in various scenarios:
- Audit Trails and Compliance: For organizations undergoing audits, tracking changes in critical workbooks over time (monthly or yearly) is essential. Spreadsheet comparison provides a clear audit trail, helping identify and rectify errors before audits.
- Error Detection and Correction: When multiple individuals collaborate on spreadsheets, discrepancies and errors can easily creep in. Comparing versions helps quickly pinpoint these inconsistencies for prompt correction.
- Version Control and Collaboration: In collaborative environments, comparing spreadsheets ensures everyone is working with the latest and correct information. It helps manage different versions and merges changes effectively.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code within Excel workbooks. This is invaluable for developers tracking changes in macros and custom functions.
Conclusion
Comparing two spreadsheets in Excel is vital for accuracy, collaboration, and compliance. While Microsoft Spreadsheet Compare offers a dedicated and feature-rich solution for users with specific Office versions, understanding its functionality and exploring alternative methods ensures you can effectively manage and track changes in your Excel workbooks, regardless of your Office setup. By leveraging these tools and techniques, you can streamline your workflow, enhance data accuracy, and maintain control over your critical spreadsheet data.