Comparing Excel Worksheets is a common task for professionals who work with data, financial analysis, or project management. Whether you need to identify changes between different versions of a workbook, audit data entries, or troubleshoot formula errors, knowing how to effectively compare Excel worksheets is crucial. Microsoft Spreadsheet Compare, a powerful tool available with certain versions of Microsoft Office, simplifies this process. This guide will walk you through how to use Spreadsheet Compare to efficiently analyze and highlight the differences between your Excel files.
What is Spreadsheet Compare and Why Use It?
Spreadsheet Compare is a utility designed by Microsoft to compare Excel workbooks and report on the differences it finds. It’s particularly useful when you need to:
- Identify changes between workbook versions: Track modifications made over time or by different collaborators.
- Audit data for errors: Locate manually entered totals instead of calculated ones or find broken formulas.
- Ensure data consistency: Verify that critical workbooks are aligned and accurate.
- Troubleshoot workbook issues: Pinpoint discrepancies causing unexpected results.
Important Note: Spreadsheet Compare is included with 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 the tool.
Step-by-Step Guide to Comparing Excel Worksheets
Let’s delve into the process of comparing two Excel workbooks using Spreadsheet Compare.
Opening Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application.
-
Accessing from the Start Menu: Click the Start button in Windows and look for Spreadsheet Compare. If you don’t see it immediately, start typing “Spreadsheet Compare,” and the option should appear in the search results. Select it to open.
It’s worth noting that alongside Spreadsheet Compare, Microsoft offers a similar tool for Access databases called Microsoft Database Compare. This program, designed for comparing Access databases, is also included in the same Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Selecting Files for Comparison
Once Spreadsheet Compare is open, you need to specify the Excel workbooks you want to compare.
-
Initiate File Comparison: Click Home > Compare Files. This action will open the Compare Files dialog box.
-
Choose the Original Workbook: In the Compare box, click the blue folder icon. This allows you to browse your file system to locate the earlier version or the first of the two workbooks you want to compare. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the Revised Workbook: Next, click the green folder icon next to the To box. Browse to and select the second workbook – typically the more recent version – that you want to compare against the first one. Click OK to confirm your selections.
Tip: You can compare two files even if they have the same name, as long as they are saved in different folders. This is useful when comparing versions saved in different directories.
Choosing Comparison Options
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare will analyze.
-
Select Comparison Criteria: In the left pane of the Compare Files dialog box, you’ll see a list of options such as Formulas, Macros, Cell Format, and more. 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 desired options, click OK to start the comparison process.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it likely means one or both of the workbooks are password-protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. For more information on password handling in Spreadsheet Compare, refer to Microsoft’s documentation on managing passwords for file analysis and comparison.
Understanding Comparison Results
Once the comparison is complete, Spreadsheet Compare displays the results in a clear, two-pane grid.
-
Side-by-Side View: The workbook you selected as “Compare” (typically the older version) is shown on the left pane, and the “To” workbook (typically the newer one) is on the right. A detailed pane below the grids provides further information about the differences.
-
Color-Coded Highlights: Changes are highlighted using different colors to indicate the type of modification. This visual representation makes it easy to quickly identify what has been altered between the two worksheets. A legend in the lower-left pane explains the color codes.
Interpreting Color Codes: For example, in the image above, cells with a green fill in both versions (like E2:E5) indicate “entered values” that have been changed. Cells with a blue-green fill (like F2:F4 and E6:F6) signify that “calculated values” have changed, often as a result of changes in input values or formulas.
In the example, cell F5 also shows a change. However, the underlying reason is more significant: the formula in the earlier version was incorrect (=SUM(B5:D5)), omitting Q4. The updated workbook corrected the formula to =SUM(B5:E5). Spreadsheet Compare highlights both value changes and formula corrections, making it a powerful audit tool.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through the comparison results for each sheet by using the forward and back buttons on the horizontal scroll bar within the grid.
Hidden Worksheets: Spreadsheet Compare includes hidden worksheets in the comparison, ensuring no part of your workbook is overlooked.
-
Resize Cells for Better Viewing: If cell contents are truncated due to column width, click Resize Cells to Fit to automatically adjust column widths for optimal readability.
Exploring Excel’s Inquire Add-in
For users working directly within Excel 2013 and later, the Inquire add-in offers another set of powerful analysis tools, including workbook comparison capabilities.
-
Inquire Tab in Excel: Once enabled, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can access features to analyze workbooks, visualize relationships between cells and worksheets, and even clean up excessive formatting.
-
Compare Files from Excel: If you have two workbooks already open in Excel, you can directly launch Spreadsheet Compare using the “Compare Files” command available within the Inquire add-in. This provides a convenient way to initiate comparisons without leaving Excel.
-
Enabling Inquire Add-in: If you don’t see the Inquire tab, you may need to activate the add-in. Instructions on how to do this can be found in Microsoft’s guide on turning on the Inquire add-in. To learn more about the full range of tools offered by the Inquire add-in, see What you can do with Spreadsheet Inquire.
Enterprise-Level Solutions for Excel Management
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers more robust management tools.
-
Microsoft Audit and Control Management Server: This server solution provides advanced change management features specifically designed for Excel and Access files. It helps maintain control and audit trails for essential spreadsheets and databases.
-
Microsoft Discovery and Risk Assessment Server: Complementing the Audit and Control Management Server, the Discovery and Risk Assessment Server offers inventory and analysis capabilities. It helps organizations understand and mitigate risks associated with user-developed tools in Excel and Access by providing comprehensive oversight and assessment features.
These enterprise solutions are designed to enhance governance and reduce risks associated with mission-critical spreadsheets and databases.
Conclusion
Comparing Excel worksheets is essential for data accuracy, version control, and error detection. Microsoft Spreadsheet Compare provides a dedicated and efficient way to perform these comparisons, highlighting differences clearly and comprehensively. Whether you are auditing financial data, tracking project changes, or ensuring data integrity, mastering Spreadsheet Compare will significantly enhance your ability to manage and analyze Excel workbooks effectively. For users within Excel, the Inquire add-in offers similar capabilities and additional analysis tools, further streamlining your workflow. By leveraging these tools, you can maintain data quality and accuracy in your Excel-based tasks.
