Comparing two Excel sheets to identify differences can be a crucial task for anyone working with data, from tracking changes in financial reports to ensuring data integrity across different versions of a project. Whether you’re dealing with slightly modified monthly budgets or completely different datasets, pinpointing discrepancies efficiently is key. Microsoft Excel offers a built-in tool called Spreadsheet Compare, designed to streamline this process, provided you have the right version of Microsoft Office. This guide will walk you through how to effectively use Spreadsheet Compare to highlight the variations between two Excel workbooks.
To begin, it’s important to know that Spreadsheet Compare is not available in all versions of Excel. It’s specifically 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 can easily access this powerful tool to compare your Excel files.
Opening Spreadsheet Compare
The first step is to launch the Spreadsheet Compare application. Here’s how you can do it:
- Go to your Start menu in Windows.
- Look for Spreadsheet Compare. You might find it directly listed.
- If you don’t see it immediately, start typing Spreadsheet Compare. The option should appear in the search results.
- Click on Spreadsheet Compare to open the application.
Once Spreadsheet Compare is open, you’re ready to start comparing your Excel workbooks. The interface is designed to be straightforward, making the comparison process as simple as possible.
Step-by-Step Guide to Comparing Excel Workbooks
Comparing two Excel files using Spreadsheet Compare involves a few simple steps:
-
Initiate the Comparison: In the Spreadsheet Compare window, click on Home > Compare Files. This will open the Compare Files dialog box.
-
Select the Files to Compare:
- In the Compare box, click the blue folder icon. This allows you to browse and select the older or original version of your Excel workbook. Navigate to the file location and select it. You can compare files stored on your computer, a network drive, or even by entering a web address if your workbooks are saved online.
- Next, click the green folder icon next to the To box. Browse to and select the more recent version or the second workbook you want to compare against the first one. Click OK after selecting both files.
- Tip: You can effectively 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 of the same report saved in different project folders.
-
Choose Comparison Options: In the left pane of the Compare Files dialog, you’ll see a list of options such as Formulas, Macros, Cell Format, and others. These options allow you to specify what aspects of the workbooks you want to compare. You can choose to check or uncheck these options based on your needs. For a comprehensive comparison, you can simply click Select All.
-
Run the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze both workbooks based on the options you selected.
-
Password Protected Files: If either of the workbooks is password protected, you might receive an “Unable to open workbook” message. If this happens, click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file contents.
Understanding the Comparison Results
After running the comparison, the results are displayed in a clear, side-by-side grid. The workbook you selected as “Compare” (typically the older one) is shown on the left, and the “To” workbook (usually the newer one) is on the right. Below the grids, a details pane provides specific information about the differences.
Changes are highlighted using colors to indicate the type of difference. Here’s a breakdown of what you might see:
-
Worksheet Navigation: If your workbooks contain multiple worksheets, they are compared sheet by sheet. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar within the comparison grid. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Differences: The grid uses color-coding to visually represent the types of changes found:
- Green Fill: Often indicates cells containing “entered values” (cells with manually typed data, not formulas) that have been changed between the two versions. In the results list pane, these changes might also be indicated with green font.
- Blue-Green Fill: Typically signifies “calculated values” (cells with formulas) where the result of the formula has changed. This could be due to changes in input values or modifications to the formula itself.
- Other Colors: Spreadsheet Compare uses a range of colors to denote different types of changes, such as formatting differences, structural changes, or formula modifications. The legend in the lower-left pane of the application clearly explains what each color represents.
For instance, if you see a green fill in specific cells, it means the direct input value in those cells has been altered. If you observe a blue-green fill in formula cells, it indicates that the calculated outcome has changed, potentially due to modifications in the input cells or the formula itself.
- Resize Cells: If the content within cells appears truncated due to column width, you can click Resize Cells to Fit. This option adjusts the column widths to ensure you can see the full content of each cell, making it easier to review the differences.
Exploring Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include an Inquire add-in that offers additional analytical capabilities. This add-in provides an “Inquire” tab in Excel, which includes features to:
- Analyze Workbooks: Gain insights into workbook structure, formula logic, and potential errors.
- Show Relationships: Visualize connections between cells, worksheets, and even links to other workbooks, helping to understand data flow and dependencies.
- Clean Excess Formatting: Reduce file size and improve performance by removing unnecessary formatting.
While Spreadsheet Compare is excellent for side-by-side file comparisons, the Inquire add-in offers deeper analysis tools within Excel itself. If you don’t see the Inquire tab in your Excel, you may need to activate it through Excel Options > Add-Ins > Manage: COM Add-ins > Go > check “Inquire” > OK.
Next Steps for Advanced Users
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, Microsoft offers more robust management tools. Microsoft Audit and Control Management Server provides advanced change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features aimed at mitigating risks associated with user-developed tools in Excel and Access environments.
These server solutions are designed for enterprise-level management and offer a significant step up in control and auditing capabilities for mission-critical spreadsheets and databases.
In conclusion, whether you are simply trying to track changes in your personal budgets or managing complex datasets in a business environment, understanding How To Compare Two Excel Sheets For Differences is a valuable skill. Microsoft Spreadsheet Compare provides an accessible and effective way to visualize and understand these changes, ensuring data accuracy and version control.
