Comparing two Excel spreadsheets to identify differences can be a time-consuming and error-prone manual task. Whether you’re auditing financial data, tracking changes in project plans, or merging datasets, pinpointing discrepancies is crucial for accuracy and efficiency. Microsoft Spreadsheet Compare, a powerful tool available with certain Microsoft Office suites, simplifies this process by generating detailed reports highlighting the variations between workbooks. This guide will walk you through how to effectively use Spreadsheet Compare to quickly and accurately compare two Excel files for differences.
Getting Started with Spreadsheet Compare
Before diving into comparisons, it’s essential to ensure you have access to Spreadsheet Compare. This utility is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you’re using one of these versions, you should have Spreadsheet Compare readily available.
Opening Spreadsheet Compare
To launch the application, the process is straightforward:
- Access the Start Menu: Click the Windows “Start” button, typically located in the bottom-left corner of your screen.
- Locate Spreadsheet Compare: Look for “Spreadsheet Compare” in your list of applications. If you don’t immediately see it, start typing “Spreadsheet Compare.” This will filter your application list, making it easier to find.
- Open the Application: Click on “Spreadsheet Compare” to launch the program.
Upon opening, you’ll notice a clean and intuitive interface designed for comparing spreadsheet files. Alongside Spreadsheet Compare, users with similar Office versions will also find Microsoft Database Compare, a companion tool for Access databases.
Step-by-Step Guide to Comparing Excel Workbooks
Once Spreadsheet Compare is open, you can initiate the comparison process with these simple steps:
-
Initiate File Comparison: Navigate to the “Home” tab in the Spreadsheet Compare ribbon and click on “Compare Files.” This action will open the “Compare Files” dialog box.
-
Select the Workbooks:
- Choose the “Compare” File (Older Version): Click the blue folder icon located next to the “Compare” box. This will open a file explorer window. Browse to and select the earlier version of your Excel workbook that you wish to use as the basis for comparison. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
- Choose the “To” File (Newer Version): Click the green folder icon next to the “To” box. Similarly, browse to and select the more recent version of the workbook that you want to compare against the earlier version. Then, click “OK” to confirm your selection in the file explorer window.
Tip: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders.
-
Customize Comparison Options: In the left-hand pane of the “Compare Files” dialog box, you’ll find a list of options that determine what aspects of the workbooks will be compared. You can select or deselect options such as “Formulas,” “Macros,” “Cell Format,” and others to tailor the comparison to your specific needs. For a comprehensive comparison, you can simply click “Select All.”
-
Run the Comparison: After selecting your files and comparison options, click “OK” to initiate the comparison process. Spreadsheet Compare will then analyze the two workbooks based on your chosen settings.
Handling Password-Protected Workbooks:
If either of the workbooks you’re trying to compare is password-protected, you might encounter an “Unable to open workbook” message. If this occurs, click “OK” on the message prompt. Spreadsheet Compare will then ask you to enter the password for the protected workbook. For more detailed information on password handling within Spreadsheet Compare, refer to Microsoft’s documentation on managing passwords for file analysis and comparison.
Interpreting Spreadsheet Compare Results
Once the comparison is complete, Spreadsheet Compare presents the results in a user-friendly, two-pane grid. The left pane displays the “Compare” file (older version), and the right pane shows the “To” file (newer version). A detailed results pane appears below the grids, offering a legend and specific details about the identified differences.
Understanding the Visual Output:
-
Side-by-Side Worksheet Comparison: Each worksheet in the compared files is analyzed against its counterpart in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located below the grid panes. Importantly, even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to visually highlight the types of differences found. Changes are indicated by cell fill colors or text font colors, depending on the nature of the modification. For instance, cells containing “entered values” (cells with manually inputted data rather than formulas) are typically marked with a green fill color in the side-by-side grid and a green font in the results list. The legend in the lower-left pane clearly explains the meaning of each color code.
Example Scenario:
Consider a scenario where you’re comparing two versions of a sales report. In the earlier version, the Q4 sales figures were preliminary. The updated version contains the finalized Q4 numbers in column E.
In the comparison results:
- Cells E2:E5 in both versions might be highlighted with a green fill, signifying that entered values have been changed.
- Consequently, the calculated results in the “Year-to-Date” (YTD) column (e.g., cells F2:F4 and E6:F6) might show a blue-green fill. This color indicates that calculated values have changed due to the updated input values.
- Cell F5 might also show a change, but upon closer inspection, you might find that in the earlier version, the formula was incorrect (=SUM(B5:D5)), omitting Q4 sales. The updated version corrected the formula to =SUM(B5:E5). This highlights not only data changes but also formula corrections.
Improving Result Visibility:
If cell contents are truncated due to column width, click “Resize Cells to Fit.” This option automatically adjusts column widths to ensure all cell data is visible, enhancing readability of the comparison results.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the “Inquire” add-in, providing a suite of analytical tools within Excel itself. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon.
From the “Inquire” tab, you can perform various workbook analyses, including:
- Workbook Analysis: Gain insights into workbook structure, formulas, errors, and hidden information.
- Relationship Exploration: Visualize relationships between cells, worksheets, and even links to other workbooks.
- Excess Formatting Cleanup: Optimize workbook performance by removing unnecessary cell formatting.
While Spreadsheet Compare is ideal for side-by-side file comparisons, the Inquire add-in offers deeper analysis and workbook management capabilities directly within Excel. To compare two open workbooks using Spreadsheet Compare from within Excel, you can use the “Compare Files” command found within the Inquire add-in.
If the “Inquire” tab isn’t visible in your Excel ribbon, you may need to activate the add-in. Instructions for enabling it can be found in Microsoft’s guide on turning on the Inquire add-in. For a comprehensive overview of the tools available in the Inquire add-in, refer to Microsoft’s documentation on using Spreadsheet Inquire to compare workbooks.
Taking Excel Management Further
For organizations managing critical Excel workbooks and Access databases, Microsoft offers advanced management tools. Microsoft Audit and Control Management Server provides robust change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features to help mitigate risks associated with end-user developed tools in Excel and Access. These server solutions provide enterprise-level control and oversight for spreadsheet and database management.
Further Resources:
For a broader overview of Spreadsheet Compare, you can consult Microsoft’s Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare and Excel’s Inquire add-in, you can significantly enhance your ability to manage, audit, and ensure the accuracy of your Excel spreadsheets, saving time and reducing the risk of errors in critical data analysis and reporting.
