Comparing two Excel documents to identify differences can be a crucial task for anyone working with spreadsheets, whether you’re tracking changes, auditing data, or ensuring consistency. Microsoft Spreadsheet Compare is a powerful tool designed exactly for this purpose. This guide will walk you through how to effectively use Spreadsheet Compare to pinpoint discrepancies between Excel files and understand the comparison results.
Open Spreadsheet Compare
First, you need to launch the Spreadsheet Compare tool. It’s important to note that Spreadsheet Compare is included with specific Microsoft Office suites: Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise.
To open it:
- On the Start screen of your Windows system, simply type Spreadsheet Compare.
- You should see the Spreadsheet Compare option appear. Click on it to open the application.
Alongside Spreadsheet Compare, Microsoft also offers Database Compare for Access databases, available with the same Office Professional Plus versions or Microsoft 365 Apps for enterprise.
Step-by-Step Guide to Compare Two Excel Workbooks
Let’s delve into the process of comparing two Excel files for differences using Spreadsheet Compare:
-
Initiate Comparison: Within Spreadsheet Compare, navigate to the Home tab and click on Compare Files.
-
Select Files: The Compare Files dialog box will pop up, prompting you to choose the workbooks you want to compare.
-
Choose the Older Version: Click the blue folder icon located next to the Compare box. This is where you’ll browse to and select the earlier version of your Excel workbook. Spreadsheet Compare allows you to select files from your local computer, network locations, or even by entering a web address if your workbooks are stored online.
Selecting the Compare Files command in Spreadsheet Compare interface.
-
Choose the Newer Version: Next, click the green folder icon beside the To box. Browse to and select the more recent version of the workbook that you want to compare against the older one. Click OK to confirm your selection.
Tip: You can easily compare two Excel files that have the same name as long as they are saved in different folders. This is particularly useful when comparing versions of the same report or dataset.
-
Specify Comparison Options: In the left pane of the Compare Files dialog, you’ll see a list of options that allow you to customize the comparison. You can choose to focus on specific elements such as Formulas, Macros, Cell Format, and more. To compare everything, simply click Select All.
-
Run the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze the two workbooks based on your selected options.
Password Protected Workbooks: If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK and enter the password for the respective workbook when prompted. Spreadsheet Compare can handle password-protected files, ensuring you can still perform comparisons.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
-
Side-by-Side View: The workbook you selected as the “Compare” file (typically the older version) is shown on the left pane, and the “To” file (usually the newer one) is on the right pane.
-
Detailed Differences: A pane located below the two grids provides detailed information about the detected differences.
-
Color-Coded Highlights: Changes are visually highlighted using different colors, each representing a specific type of modification. This color-coding makes it easy to quickly identify the nature of the differences between the two Excel documents.
-
Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Spreadsheet Compare compares all worksheets, including hidden ones, and displays the results for each.
Example of Spreadsheet Compare results highlighting differences between two Excel workbook versions.
Interpreting Color Highlights:
- Green Fill Color (in side-by-side grid): Indicates cells with “entered values” (non-formula cells) that have been modified.
- Green Font (in results list): Also signifies changes in entered values.
- Blue-Green Fill: Highlights cells where “calculated values” (formula results) have changed. This could be due to changes in input values or modifications in formulas.
In the example result shown, you can see that the values in column E (Q4) have a green fill, indicating changed entered values. Consequently, the calculated results in the YTD column (F) also changed, marked with a blue-green fill. Furthermore, cell F5 highlights a formula correction in the newer version, demonstrating how Spreadsheet Compare can also reveal formula discrepancies.
Resize Cells for Better Visibility: If cell contents are truncated due to narrow columns, simply click Resize Cells to Fit to automatically adjust column widths and display all information clearly.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the “Inquire” add-in. Activating this add-in adds an “Inquire” tab to your Excel ribbon, providing a suite of powerful analysis tools.
The Inquire add-in allows you to:
- Analyze Workbooks: Gain insights into workbook structure, formula relationships, and potential errors.
- Visualize Relationships: See connections between cells, worksheets, and even external workbooks.
- Clean Excess Formatting: Reduce file size and improve performance by removing unnecessary formatting.
While Spreadsheet Compare is excellent for direct file comparison, the Inquire add-in offers broader analytical capabilities within Excel itself. If you have two workbooks open in Excel, you can also initiate Spreadsheet Compare directly from the Inquire tab using the “Compare Files” command, streamlining your workflow.
If you don’t see the Inquire tab, you may need to activate it in Excel’s options.
Taking the Next Step
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, consider exploring Microsoft’s comprehensive management tools:
- Microsoft Audit and Control Management Server: Provides robust change management and auditing features specifically designed for Excel and Access files.
- Microsoft Discovery and Risk Assessment Server: Offers inventory and analysis capabilities to help manage risks associated with user-developed tools in Excel and Access.
These server solutions complement Spreadsheet Compare by offering enterprise-level control and oversight for critical spreadsheet and database assets.
Further Resources:
By utilizing Spreadsheet Compare and the Inquire add-in, you can effectively compare Excel documents, identify differences, and maintain data integrity, improving accuracy and reducing errors in your spreadsheet-based tasks.
