Comparing two Excel files to identify differences can be a common need, whether you’re tracking changes in financial data, project plans, or any information managed in spreadsheets. Microsoft Spreadsheet Compare is a powerful tool designed to help you easily achieve this. This guide will walk you through how to use Spreadsheet Compare to effectively compare two Excel workbooks and pinpoint the discrepancies.
What is Spreadsheet Compare?
Microsoft Spreadsheet Compare is a utility specifically created to compare Excel files and highlight their differences. It’s particularly useful when you need to:
- Identify changes between different versions of the same workbook.
- Find discrepancies in data across similar spreadsheets.
- Audit workbooks for errors, such as broken formulas or inconsistencies.
Important Note: Spreadsheet Compare is not included in all versions of Microsoft Office. It’s available in:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you have one of these versions, you can leverage Spreadsheet Compare to streamline your Excel file comparison tasks.
How to Open Spreadsheet Compare
Opening Spreadsheet Compare is straightforward. Here’s how you can find it:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. You might find it under the Microsoft Office suite or listed alphabetically.
- If you don’t immediately see it, simply start typing “Spreadsheet Compare” directly in the Start Menu search bar.
- Click on the Spreadsheet Compare application to launch it.
Once opened, you’ll be ready to start comparing your Excel workbooks.
Step-by-Step Guide: Comparing Two Excel Workbooks
Let’s break down the process of comparing two Excel workbooks using Spreadsheet Compare into easy-to-follow steps:
Select Files to Compare
-
In Spreadsheet Compare, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
You’ll see two boxes: Compare and To. Click the blue folder icon next to the Compare box. This is where you’ll select the earlier version or the first file you want to compare. Browse to the location of your Excel workbook, select it, and click Open. You can select files from your computer, network locations, or even enter a web address if your files are stored online.
-
Next, click the green folder icon next to the To box. This is for selecting the later version or the second file you’re comparing against the first. Browse to the location, select the second workbook, and click OK.
Tip: You can compare two files even if they have the same name, as long as they are saved in different folders. This is useful when comparing versions saved in different project directories.
Choose Comparison Options
Before running the comparison, you can specify what aspects of the workbooks you want to examine for differences.
-
In the left pane of the Compare Files dialog box, you’ll see a list of options. These include:
- Formulas: Compares formulas in cells.
- Macros (VBA): Checks for differences in VBA code.
- Cell Format: Looks for variations in cell formatting (like fonts, colors, number formats).
- And more: Other options like comments, sheet names, etc., may be available depending on your version.
-
Check the boxes next to the options you want to include in your comparison. If you want to compare everything, you can simply click Select All.
Interpret the Results
-
After selecting your files and options, click OK in the Compare Files dialog box to start the comparison.
-
If either of your workbooks is password-protected, you might get an “Unable to open workbook” message. Click OK and enter the password for the respective workbook when prompted.
-
Spreadsheet Compare will then display the results in a two-pane grid.
- The left pane shows the workbook selected in the “Compare” box (typically the older version).
- The right pane displays the workbook selected in the “To” box (typically the newer version).
- Below the grids, a details pane provides a summary of the changes.
Understanding the Comparison Results
Spreadsheet Compare uses color-coding to highlight different types of changes between the two Excel files, making it easy to quickly grasp the modifications.
-
Worksheet Comparison: Each worksheet from the compared files is analyzed against its counterpart in the other file. If you have multiple worksheets, use the navigation buttons on the horizontal scroll bar to view them all. Even hidden worksheets are included in the comparison.
-
Color-Coded Differences: Differences are highlighted with distinct colors in the side-by-side grid. For instance:
- Green fill: Often indicates “entered values” that have changed (values not derived from formulas).
- Blue-green fill: Usually signifies “calculated values” that have changed, often because of changes in input values or formula modifications.
- Font color changes: Might indicate differences in formulas or other cell properties.
-
Legend Pane: The lower-left pane acts as a legend, explaining what each color code represents, allowing you to quickly understand the type of change highlighted.
For example, if you see a cell with a green fill, it means the value directly entered into that cell has been altered between the two versions. If you see a blue-green fill in a cell containing a formula, it indicates that the calculated result of that formula has changed.
If cell contents are truncated due to column width, you can click Resize Cells to Fit to expand the columns and view the full content.
Explore Excel’s Inquire Add-in
Besides Spreadsheet Compare, Excel itself offers an “Inquire” add-in, available in Excel 2013 and later, which provides additional analytical capabilities. To use it, you may need to activate it first.
If you have the Inquire add-in enabled (look for an “Inquire” tab in Excel’s ribbon), you can access features like:
- Workbook Analysis: Get a comprehensive overview of a workbook’s structure, formulas, and potential issues.
- Relationship Exploration: Visualize dependencies between cells, worksheets, and even external workbooks.
- Excess Formatting Cleanup: Remove unnecessary formatting that can increase file size and complexity.
Within Excel, if you have two workbooks open and want to initiate a comparison using Spreadsheet Compare, you can do so via the “Compare Files” command within the Inquire add-in tab.
If you don’t see the Inquire tab, you can enable it by going to File > Options > Add-Ins > Manage: COM Add-ins > Go and checking the “Inquire” box.
Next Steps
For organizations heavily reliant on Excel and Access for critical operations, consider exploring Microsoft’s broader suite of management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server offer advanced change management, inventory, and analysis features for Excel and Access files. These tools are designed to mitigate risks associated with end-user developed applications in these platforms.
To further explore the capabilities of Spreadsheet Compare, you can also refer to the Overview of Spreadsheet Compare for more detailed information.
By using Spreadsheet Compare, you can efficiently compare two Excel files, understand the differences, and maintain data accuracy and integrity, saving valuable time and reducing potential errors.
