Comparing two Excel workbooks to identify differences can be a time-consuming and error-prone manual task. Whether you’re dealing with different versions of the same report, trying to consolidate data, or auditing for errors, quickly pinpointing discrepancies is crucial. Microsoft Spreadsheet Compare, a tool available with specific Office Professional Plus and Microsoft 365 subscriptions, offers a robust solution to streamline this process. This guide will walk you through how to effectively use Spreadsheet Compare to analyze and understand the variations between two Excel workbooks.
Getting Started with Spreadsheet Compare
First, ensure you have access to Spreadsheet Compare. It’s included in 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, you can easily find the application.
Opening Spreadsheet Compare
- Navigate to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t see it immediately, simply start typing “Spreadsheet Compare.”
- Click on the Spreadsheet Compare application to launch it.
Once opened, you’ll be ready to initiate the comparison process. Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, which is part of the same suite of tools for professional data management.
Step-by-Step Guide to Comparing Excel Files
Spreadsheet Compare makes the process of comparing Excel workbooks straightforward. Follow these steps to analyze your files:
-
Initiate the Comparison: In the Spreadsheet Compare window, click on Home > Compare Files. This action will open the Compare Files dialog box.
-
Select the Workbooks:
- Choose the Original Workbook: Click the blue folder icon next to the Compare box. Browse to and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your files are stored online.
- Choose the Modified Workbook: Click the green folder icon next to the To box. Locate and select the workbook you want to compare against the original version. Click OK to confirm your selections.
Tip: You can effectively compare two files even if they share the same name, as long as they are saved in different folders. This is particularly useful when comparing versions saved at different times.
-
Customize Comparison Options: In the left-hand pane of the Compare Files dialog, you’ll see a list of options. Select the 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 click Select All.
-
Run the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze the two workbooks based on your chosen options.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, ensuring you can still analyze even sensitive data.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid.
-
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). A detailed pane below the grids provides further information about the detected changes.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons located on the horizontal scroll bar within the grid.
Note: Spreadsheet Compare analyzes all worksheets, including hidden ones, ensuring a complete comparison.
-
Color-Coded Differences: Differences between the workbooks are highlighted with distinct colors. The color coding helps you quickly identify the type of change. For instance, cells containing “entered values” (cells with manually input data rather than formulas) that have been modified are often highlighted with a green fill color in the grid and a green font in the results list. A legend in the lower-left pane clearly explains the meaning of each color code.
Consider this example: Imagine you’re comparing quarterly sales reports. The earlier version might have preliminary figures for Q4. The updated version contains final numbers. Spreadsheet Compare would highlight these changes:
-
Cells with updated sales figures (entered values) would show a green fill.
-
Cells with formulas that recalculate based on these updated figures (like year-to-date totals) would show a different color, perhaps blue-green, indicating a change in calculated value.
-
Crucially, if a formula itself was corrected in the newer version, Spreadsheet Compare would also highlight this, allowing you to catch and understand not just data changes but also structural improvements or corrections in your workbooks.
-
Adjusting Cell Width: If cell contents are truncated in the display, click Resize Cells to Fit. This will adjust the column widths to ensure you can view the complete content of each cell.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in. This add-in provides a range of analytical tools directly within Excel. To access it, you may need to activate it first.
Accessing Inquire in Excel
If you don’t see the “Inquire” tab in your Excel ribbon, follow these steps to enable it:
- Go to File > Options > Add-ins.
- In the “Manage” dropdown at the bottom, select “COM Add-ins” and click “Go…”.
- In the COM Add-ins dialog box, check the box next to “Inquire” and click “OK”.
Once enabled, the “Inquire” tab will appear in your Excel ribbon, offering features like workbook analysis, relationship visualization between cells and worksheets, and tools to clean up excessive formatting. While Spreadsheet Compare is a dedicated application for file comparison, the Inquire add-in provides complementary analytical capabilities within Excel itself. You can even launch Spreadsheet Compare directly from Excel using the “Compare Files” command within the Inquire tab if you have two workbooks open that you want to compare immediately.
Further Enhancing Excel Management
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides comprehensive change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features, helping to mitigate risks associated with end-user developed tools in Excel and Access environments.
To delve deeper into Spreadsheet Compare and its capabilities, refer to the Overview of Spreadsheet Compare.
By leveraging Spreadsheet Compare and the Inquire add-in, you can significantly enhance your ability to manage, audit, and understand your Excel workbooks, ensuring data integrity and accuracy.
