Comparing Two Workbooks In Excel is a common task for professionals who need to track changes, audit data, or merge information from different sources. Microsoft Spreadsheet Compare, a powerful tool available with certain Office versions, simplifies this process by highlighting the differences between two Excel files. This guide will walk you through how to effectively use Spreadsheet Compare to analyze and understand the variations between your workbooks.
Understanding Microsoft Spreadsheet Compare
Spreadsheet Compare is a utility designed to pinpoint discrepancies within and between Excel workbooks. It’s particularly useful when you have multiple versions of a file or need to compare data sets for inconsistencies. This tool is not just about identifying changes; it’s about enhancing data accuracy and efficiency in your workflow.
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.
Launching Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward. Follow these steps to open the application:
- From the Start Menu: Click the Start button and look for Spreadsheet Compare in your applications list.
- Search: If you don’t see it immediately, simply type “Spreadsheet Compare” after clicking the Start button. The option should appear in the search results. Click on it to launch.
Upon opening, you’ll notice its user-friendly interface, ready to help you delve into comparing your Excel files. Alongside Spreadsheet Compare, Microsoft offers Database Compare for Access databases, which is also part of the Office Professional Plus suite.
Step-by-Step Guide to Comparing Excel Workbooks
Let’s get into the core function: comparing two Excel workbooks. Here’s a detailed, step-by-step process:
-
Initiate File Comparison: In Spreadsheet Compare, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the Original Workbook: Locate the “Compare” box, and click the blue folder icon next to it. This prompts a browse window to open. Navigate to and select the earlier version of your Excel workbook you want to use as the base for comparison. You can select files from your local computer, network locations, or even enter a web address if your files are stored online.
-
Choose the Revised Workbook: Next, click the green folder icon adjacent to the “To” box. Browse to and select the more recent or revised version of the workbook that you want to compare against the original. Click OK after selecting the file. Remember, you can compare files even if they have the same name but are saved in different folders.
-
Customize Comparison Options: In the left pane of the Compare Files dialog, you’ll see a list of options. These allow you to specify what aspects of the workbooks you want to compare. You can choose to compare Formulas, Macros, Cell Formats, and more. For a comprehensive comparison, you can simply check Select All.
-
Execute the Comparison: After selecting your desired options, click OK to start the comparison process. Spreadsheet Compare will then analyze both workbooks based on your selected criteria.
Password Protected Workbooks: If either of your 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 needs the password to access and compare the file contents.
Interpreting Comparison Results
Once the comparison is complete, the results are displayed in a clear, side-by-side grid format.
-
Side-by-Side View: The left pane displays the “Compare” workbook (typically the older version), and the right pane shows the “To” workbook (usually the newer one). Below these grids, a details pane provides specific information about the detected changes.
-
Color-Coded Differences: Changes are highlighted with distinct colors, making it easy to visually identify the types of modifications. The color coding helps differentiate between various changes, such as formula modifications, value changes, or format alterations. A legend in the lower-left pane clearly explains what each color represents.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, Spreadsheet Compare allows you to navigate through each sheet using the forward and back buttons on the horizontal scroll bar, ensuring every part of your workbook is reviewed. Importantly, even hidden worksheets are included in the comparison and results.
-
Example Scenario: Imagine you’re comparing quarterly sales reports. If the initial version had preliminary Q4 figures, and the updated version contains finalized Q4 data, Spreadsheet Compare will highlight these value changes. For instance, cells with revised entered values might be marked with a green fill, while cells with updated calculated values could have a blue-green fill. This visual differentiation quickly draws your attention to key data modifications and formula updates.
-
Cell Content Visibility: If cell contents are truncated due to column width, simply click Resize Cells to Fit. This feature automatically adjusts column widths to ensure you can view all cell data without difficulty.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in, which offers further analytical capabilities. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon.
From the Inquire tab, you can:
- Analyze Workbooks: Gain insights into workbook structure, formulas, and potential errors.
- Visualize Relationships: See connections between cells, worksheets, and even links to other workbooks.
- Clean Formatting: Remove excessive or inconsistent formatting to improve workbook performance and clarity.
While Spreadsheet Compare is excellent for direct file comparison, the Inquire add-in provides a broader suite of tools for workbook analysis and auditing within Excel itself. You can even launch Spreadsheet Compare directly from Excel using the Inquire tab to compare two currently open workbooks.
If you don’t see the Inquire tab in your Excel, you may need to activate it in Excel Options > Add-ins > Manage: COM Add-ins > Go > Inquire.
Next Steps for Advanced Workbook Management
For organizations managing critical Excel workbooks and Access databases, consider Microsoft’s comprehensive management tools. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features to help mitigate risks associated with user-developed tools in Excel and Access.
These server solutions offer a more enterprise-level approach to managing and securing important spreadsheet and database assets.
Further reading: Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare and the Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the accuracy of your Excel workbooks, leading to better data integrity and informed decision-making.
