Comparing two Excel spreadsheets to identify differences can be a daunting task, especially when dealing with large datasets or complex workbooks. Whether you’re auditing financial records, tracking changes in project data, or simply ensuring data consistency, knowing how to efficiently compare Excel files is a crucial skill. Microsoft Spreadsheet Compare offers a robust solution, allowing you to pinpoint discrepancies and understand modifications quickly. This guide will walk you through the process of using Spreadsheet Compare to effectively analyze differences between two Excel spreadsheets.
Understanding Spreadsheet Compare
Spreadsheet Compare is a powerful tool designed by Microsoft to help you identify differences between two Excel workbooks or different versions of the same workbook. It goes beyond simple visual inspection by providing a detailed report on various changes, from formula modifications to formatting alterations. This tool is particularly valuable for professionals who need to maintain data integrity, troubleshoot errors, or track changes in collaborative projects.
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 Spreadsheet Compare.
Step-by-Step Guide: How to Compare Excel Spreadsheets
Let’s dive into the practical steps of using Spreadsheet Compare to analyze your Excel files.
Opening Spreadsheet Compare
First, you need to launch the Spreadsheet Compare application.
-
Navigate to the Start Menu on your Windows system.
-
Look for Spreadsheet Compare in your applications list. If you don’t see it immediately, start typing “Spreadsheet Compare,” and it should appear as a search suggestion.
-
Click on Spreadsheet Compare to open the application.
Alternatively, you can search for “Spreadsheet Compare” directly in the Start Menu search bar.
Selecting Files for Comparison
Once Spreadsheet Compare is open, you need to specify the two Excel files you want to compare.
-
Click on the Home tab in the Spreadsheet Compare window.
-
Select Compare Files. This action will open the Compare Files dialog box.
-
In the Compare box, click the blue folder icon. This allows you to browse and select the earlier version or the first Excel file you want to compare. You can choose files from your local computer, network drives, or even enter a web address if your files are stored online.
-
Next, in the To box, click the green folder icon. Browse and select the second Excel file, which is the version you want to compare against the first one. Click OK after selecting the file.
Tip: Spreadsheet Compare allows you to compare two files with the same name as long as they are saved in different folders. This is useful when you have versioned files.
Choosing Comparison Options
Before running the comparison, you can customize what aspects of the spreadsheets you want to analyze.
-
In the left pane of the Compare Files dialog box, you’ll see a list of options. These options determine what types of differences Spreadsheet Compare will report.
-
You can check or uncheck options such as Formulas, Macros, Cell Format, Worksheet Name, and more. To compare everything, simply click Select All.
-
Once you have selected your desired 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. Click OK and enter the password for the respective workbook when prompted.
Understanding Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a clear, side-by-side grid layout.
-
The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version).
-
Differences are highlighted with distinct colors, making it easy to spot changes. A legend in the lower-left pane explains the meaning of each color code.
For example, cells containing “entered values” (values that are not results of formulas) that have been modified are typically highlighted with a green fill. Cells with changed calculated values (formula results) might be highlighted with a blue-green fill. Different formatting changes, structural changes, or formula changes will also have their own color codes.
-
Navigate through different worksheets using the forward and back buttons on the horizontal scroll bar if your workbooks contain multiple sheets. Spreadsheet Compare analyzes all worksheets, even hidden ones.
-
If cell content is truncated due to column width, click Resize Cells to Fit to expand columns and view the full content.
By carefully examining the color-coded differences and referring to the legend, you can gain a comprehensive understanding of the changes between your two Excel spreadsheets.
Explore Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel itself offers a valuable add-in called Inquire. If you are working within Excel 2013 or later, you can enable the Inquire add-in to gain access to additional workbook analysis features.
The Inquire add-in provides tools to:
- Analyze Workbook: Understand the structure and complexity of your workbook.
- Workbook Relationship: Visualize connections between cells, worksheets, and even other workbooks.
- Clean Excess Cell Formatting: Reduce file size and improve performance by removing unnecessary formatting.
- Compare Files: Directly launch Spreadsheet Compare from within Excel using the “Compare Files” command under the Inquire tab.
To enable the Inquire add-in, follow the instructions in Turn on the Inquire add-in. Learn more about its capabilities in What you can do with Spreadsheet Inquire.
Advanced Solutions for Enterprise Environments
For organizations managing “mission-critical” Excel workbooks and Access databases, Microsoft offers advanced management tools:
- Microsoft Audit and Control Management Server: Provides robust change management features specifically designed for Excel and Access files, ensuring data governance and compliance.
- Microsoft Discovery and Risk Assessment Server: Offers inventory and analysis capabilities to identify risks associated with user-developed tools in Excel and Access, helping to strengthen data security and reliability.
These enterprise-level solutions provide a more comprehensive approach to managing and controlling spreadsheets and databases within a business environment.
Conclusion
Spreadsheet Compare is an indispensable tool for anyone who needs to effectively compare differences in two Excel spreadsheets. By following this guide, you can confidently use Spreadsheet Compare to identify changes, audit data, and maintain accuracy across your Excel workbooks. Whether you are a financial analyst, project manager, or data professional, mastering spreadsheet comparison techniques will significantly enhance your efficiency and data management capabilities.
Further Resources:
