Comparing two Excel files, especially when dealing with crucial data or multiple versions, can be a daunting task. Whether you’re trying to identify changes, errors, or inconsistencies between workbooks, manually sifting through spreadsheets is time-consuming and prone to mistakes. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare, designed to streamline this process and highlight the discrepancies between your XLS files efficiently.
This guide will walk you through the process of using Spreadsheet Compare to effectively compare two Excel files. We’ll cover everything from opening the tool to understanding the results, ensuring you can confidently pinpoint the differences that matter.
Getting Started with Spreadsheet Compare
Before we dive into comparing files, it’s important to note that Spreadsheet Compare is not available in all versions of Microsoft Office. It’s included with:
- 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 should have access to Spreadsheet Compare. Here’s how to open it:
- Go to your Start Menu in Windows.
- Look for Spreadsheet Compare. You might find it directly listed.
- If you don’t see it, simply start typing “Spreadsheet Compare”. The option should appear in the search results. Click on it to open the application.
Alt Text: Locating and opening the Spreadsheet Compare application from the Windows Start Menu.
Once opened, you’ll notice a clean and straightforward interface ready for you to initiate your file comparison.
Step-by-Step: Comparing Two Excel Workbooks
Now that you have Spreadsheet Compare open, let’s proceed with comparing your two Excel files:
-
Initiate the Comparison: Click on the “Compare Files” button, usually located in the Home tab of the ribbon. This action will open the Compare Files dialog box.
-
Select the Files: In 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 of the two Excel files you want to compare. Browse to the file’s location on your computer or network and select it. You can even input a web address if your file is saved online.
- Next, click the green folder icon beside the “To” box. This is for selecting the later version or the second file you are comparing against the first one. Navigate to and select the desired Excel file. Click “OK” to confirm your selections.
Tip: You can compare two files even if they have the same name, as long as they are saved in different folders. This is particularly useful when comparing versions saved in different directories.
-
Choose Comparison Options: In the left pane of the Compare Files dialog, you’ll find a list of options that determine what aspects of the workbooks will be compared. You can choose to compare:
- Formulas: Highlights differences in formulas used in cells.
- Macros: Identifies changes in VBA macros.
- Cell Format: Shows variations in cell formatting like fonts, colors, and styles.
- And more: Other options include comparing comments, worksheet names, and more.
You can select specific options or simply click “Select All” to compare everything. Choose the options that are most relevant to your comparison needs.
-
Run the Comparison: After selecting your files and comparison options, click “OK” to start the comparison process.
If either of your workbooks is password protected, you might encounter an “Unable to open workbook” message. If this happens, click “OK” and you’ll be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file content.
Understanding the Comparison Results
Once the comparison is complete, Spreadsheet Compare presents the results in a clear, two-pane grid.
-
Side-by-Side View: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer one). Each worksheet from both files is compared and displayed side-by-side. If your workbooks have multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and results.
-
Color-Coded Differences: Spreadsheet Compare uses color-coding to highlight different types of changes. Cells with differences are marked with fill colors or font colors, making it easy to quickly spot modifications.
- For example, cells containing directly entered values (not formulas) that have been changed are often highlighted with a green fill color in the grid and a green font in the results list pane below.
- Cells with changed calculated values (due to formula changes or changes in input values) might be highlighted with a blue-green fill.
-
Legend Pane: The lower-left pane of the Spreadsheet Compare window provides a legend, explaining what each color code represents. Refer to this legend to understand the type of changes highlighted in the grid.
Alt Text: Screenshot of Spreadsheet Compare results highlighting differences in cell values and formulas between two Excel workbook versions.
In the example result shown above, you can see how different types of changes are visually distinguished, making it easy to analyze and understand the modifications between the two Excel files.
If cell contents are truncated due to column width, you can click “Resize Cells to Fit” to automatically adjust column widths and view the full content.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel also offers the Inquire add-in, which provides additional analytical capabilities. While Spreadsheet Compare is a standalone tool, the Inquire add-in integrates directly into Excel (Excel 2013 and later).
The Inquire add-in provides an “Inquire” tab in Excel’s ribbon, offering features like:
- Workbook Analysis: Comprehensive analysis of workbook structure, formulas, and potential errors.
- Relationship Exploration: Visualizing relationships between cells, worksheets, and even links to other workbooks.
- Excess Formatting Cleanup: Removing unnecessary formatting to reduce file size and improve performance.
While the Inquire add-in offers broader workbook analysis features, you can still use Spreadsheet Compare directly from Excel if you have two workbooks open that you wish to compare. Simply use the “Compare Files” command within Spreadsheet Compare, as described earlier.
If you don’t see the “Inquire” tab in your Excel, you might need to activate the add-in. You can find instructions on how to do this by searching for “Turn on the Inquire add-in” in Excel help or online.
Taking it Further
For organizations heavily reliant on Excel and Access for critical operations, Microsoft provides more robust management tools:
- Microsoft Audit and Control Management Server: Offers advanced change management features for Excel and Access files, providing better control and audit trails.
- Microsoft Discovery and Risk Assessment Server: Helps with inventory and analysis of Excel and Access files across an organization, aiding in risk assessment and management.
These server solutions are designed to minimize risks associated with user-developed tools in Excel and Access, especially in enterprise environments.
Conclusion
Spreadsheet Compare is a valuable tool for anyone who needs to effectively compare two Excel files. By following these steps, you can quickly identify differences in data, formulas, and formatting, saving time and ensuring accuracy when working with multiple versions of your spreadsheets. Whether you are auditing data, merging changes, or simply ensuring consistency, Spreadsheet Compare simplifies the process of comparing XLS files and helps you maintain data integrity.
