Comparing two Excel documents might seem daunting, especially when dealing with large datasets and complex formulas. Whether you’re trying to identify changes between versions, audit for errors, or consolidate data, manually going through each cell is inefficient and prone to mistakes. Thankfully, Microsoft provides a built-in tool called Spreadsheet Compare, designed to streamline this very task.
This guide will walk you through how to effectively use Spreadsheet Compare to meticulously Compare 2 Excel Documents. We’ll cover everything from accessing the tool to understanding the comparison results, ensuring you can confidently pinpoint differences and maintain data integrity.
Important Note: Spreadsheet Compare is a powerful utility, but it’s exclusively available with specific Microsoft Office suites: 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 utilize this feature.
Accessing Spreadsheet Compare
Unlike Excel itself, Spreadsheet Compare isn’t directly accessible from the Excel application. It’s a standalone tool that comes bundled with the specified Office Professional Plus or Microsoft 365 Apps for enterprise suites. Here’s how to open it:
- Via the Start Menu: Click on the Start button in Windows.
- Search for Spreadsheet Compare: Begin typing “Spreadsheet Compare”. As you type, the option should appear in the search results.
- Open the Application: Select Spreadsheet Compare from the search results to launch the application.
Once opened, you’ll notice a clean and straightforward interface, ready for you to initiate your Excel document comparison.
Step-by-Step Guide: Comparing Two Excel Workbooks
Spreadsheet Compare makes the process of comparing two Excel files incredibly user-friendly. Follow these steps to effectively analyze your documents:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the ‘Compare’ File (Older Version): Click the blue folder icon situated next to the Compare box. This prompts a browse window to open, allowing you to locate and select the earlier version of your Excel workbook. You can choose files from your local computer, network locations, or even input a web address if your workbooks are hosted online.
-
Select the ‘To’ File (Newer Version): Next, click the green folder icon adjacent to the To box. Browse to and select the more recent version of the Excel workbook you wish to compare against the older one. Click OK to confirm your selection.
Tip: Spreadsheet Compare is versatile; you can even compare two files with identical names as long as they reside in different folders. This is particularly useful when comparing versions saved in different project directories.
-
Choose Comparison Options: Before running the comparison, the left pane of the Compare Files dialog box presents various options to refine your analysis. You can select specific elements to compare, such as:
- Formulas: Compares the formulas used in cells.
- Macros: Checks for differences in VBA macros.
- Cell Format: Identifies changes in cell formatting (font, color, alignment, etc.).
- Values: Compares the numerical or text values within cells.
You can choose to check or uncheck these options based on your specific needs. For a comprehensive comparison, simply click Select All.
-
Execute the Comparison: Click OK to initiate the comparison process. Spreadsheet Compare will analyze both Excel documents based on your selected options.
Password Protected Workbooks: If either of your workbooks is password protected, you might encounter an “Unable to open workbook” message. Click OK in the message prompt and enter the password for the respective workbook when asked. Spreadsheet Compare is designed to handle password-protected files, ensuring you can still conduct your comparisons securely.
Deciphering the Comparison Results
Once the comparison is complete, Spreadsheet Compare presents the results in a clear, two-pane grid.
-
Side-by-Side Grid: The left pane displays the workbook you selected as “Compare” (typically the older version), while the right pane shows the “To” workbook (usually the newer version). Each workbook’s worksheets are compared 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. Notably, even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences found between the two Excel documents. Changes are indicated by cell fill colors or text font colors within the side-by-side grid.
-
Legend Pane: The lower-left pane serves as a legend, explaining the meaning of each color code. This is crucial for quickly understanding the nature of the changes detected. For instance, cells with “entered values” (cells containing manually typed data, not formulas) are often highlighted with a green fill color in the grid and a green font in the results list.
Example Scenario: Imagine you’re comparing quarterly sales reports. The earlier version (left pane) might have preliminary Q4 figures, while the newer version (right pane) contains finalized Q4 numbers. In the comparison results, cells in the Q4 columns with updated sales figures would likely be highlighted with a green fill, indicating changed entered values. Consequently, any formulas dependent on these values, such as year-to-date totals, would also show changes, possibly highlighted in a different color like blue-green, signifying altered calculated values.
- Resize Cells for Better Visibility: If cell contents are truncated due to narrow column widths, click Resize Cells to Fit. This option automatically adjusts column widths to ensure you can view the complete cell data.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides a suite of analytical tools directly within Excel. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can perform workbook analysis, visualize relationships between worksheets and cells, and even remove excessive formatting to optimize file performance.
While Spreadsheet Compare is excellent for side-by-side file comparisons, the Inquire add-in offers deeper workbook analysis capabilities within the Excel environment itself. If you have two Excel workbooks open and wish to use Spreadsheet Compare, you can conveniently launch it via the “Compare Files” command within the Inquire add-in tab in Excel.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it manually. Refer to Microsoft’s guide on how to turn on the Inquire add-in for step-by-step instructions. To delve deeper into the functionalities of the Inquire add-in, explore what you can do with Spreadsheet Inquire.
Next Steps for Robust Excel Management
For organizations heavily reliant on “mission-critical” Excel workbooks and Access databases, consider exploring Microsoft’s enterprise-level management tools. Microsoft Audit and Control Management Server offers advanced change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides comprehensive inventory and analysis capabilities. These tools collectively aim to mitigate risks associated with user-developed tools in Excel and Access environments, ensuring data governance and control.
Further reading on Spreadsheet Compare can be found in the Overview of Spreadsheet Compare.
By leveraging Spreadsheet Compare and understanding its features, you can efficiently compare 2 Excel documents, identify critical changes, and maintain accuracy across your spreadsheets. This tool significantly reduces manual effort, minimizes errors, and empowers you to effectively manage and audit your Excel data.
