It’s a common scenario: you have two Excel workbooks, perhaps different versions of the same report, and you need to pinpoint the exact changes. Manually sifting through data to compare Excel files for differences can be time-consuming and prone to error. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare that simplifies this process, allowing you to efficiently compare Excel workbooks and identify discrepancies, formula changes, and more.
Important Note: Spreadsheet Compare is included with specific Microsoft Office suites, namely 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 access this powerful feature for comparing Excel workbooks.
Launching Spreadsheet Compare
To begin comparing Excel workbooks, you first need to open the Spreadsheet Compare tool. Here’s how:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare. You might find it directly listed.
- If you don’t see it immediately, simply start typing Spreadsheet Compare. The option should appear in the search results. Click on Spreadsheet Compare to launch the application.
Alt text: Accessing Spreadsheet Compare tool by searching in the Windows Start Menu.
Alongside Spreadsheet Compare, you may also notice Database Compare, a similar tool for Access databases. Both require the Office Professional Plus or Microsoft 365 Apps for enterprise editions.
Step-by-Step Guide to Compare Excel Files
Once Spreadsheet Compare is open, follow these steps to effectively compare two Excel workbooks and find differences:
-
Initiate Comparison: Click Home > Compare Files. This action will open the Compare Files dialog box, which is your starting point for selecting the Excel files you want to compare.
-
Select the Original Workbook: In the Compare Files dialog box, locate the Compare box. Click the blue folder icon situated next to it. This will open a browser window allowing you to navigate to the location of the earlier version or the first of the two Excel workbooks you intend to compare. You can select files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the Revised Workbook: Next, find the To box in the Compare Files dialog. Click the green folder icon next to it. Again, a browser window will appear, enabling you to select the second Excel workbook – typically the more recent version – that you want to compare against the first one. Click OK after selecting the file.
Pro Tip: You can easily compare two Excel files with identical names as long as they are saved in different folders. This is particularly useful when you have versioned files.
-
Customize Comparison Options: The left pane of the Compare Files dialog box presents various options to refine your comparison. You can choose what aspects of the workbooks you want to focus on. Options include:
- Formulas: Highlights changes in formulas between the two workbooks.
- Macros: Identifies differences in VBA macros.
- Cell Format: Shows variations in cell formatting, such as fonts, colors, and styles.
- Select All: Check this box to include all available comparison options in your report.
Select the options that are most relevant to your comparison needs.
-
Run the Comparison: After selecting your workbooks and choosing your comparison options, click OK to initiate the comparison process. Spreadsheet Compare will then analyze the two Excel files and generate a detailed report of the differences it finds.
Troubleshooting: If you encounter an “Unable to open workbook” message, it’s likely that one or both of the Excel workbooks are password-protected. Click OK in the message box and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, provided you have the necessary passwords. For more information on password handling, refer to how passwords and Spreadsheet Compare work together.
Interpreting the Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly, two-pane grid.
-
Side-by-Side View: The left pane displays the workbook you selected as “Compare” (typically the older version), and the right pane shows the “To” workbook (usually the newer one). Each workbook’s worksheets are compared against the corresponding worksheet in the other file. If your workbooks contain multiple worksheets, you can navigate between them using the forward and back buttons on the horizontal scroll bar.
Important Note: Even hidden worksheets are included in the comparison and will be displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses color highlighting to visually represent the types of differences found between the Excel workbooks. The specific colors indicate the nature of the change. For example, cells containing “entered values” (data that is not the result of a formula) that have been modified are typically highlighted with a green fill color in the grid and a green font in the detailed results list. A legend in the lower-left pane clearly explains the meaning of each color code.
Alt text: Example of Spreadsheet Compare results showing color-coded differences between two Excel workbook versions.
Example Interpretation: In the example result shown, the Q4 figures in an older version of a workbook were preliminary. The updated workbook contains final numbers in column E for Q4. The comparison highlights cells E2:E5 in both versions with a green fill, indicating that entered values have changed. Consequently, the calculated “Year-to-Date” (YTD) results in column F also changed. Cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying changes in calculated values.
Furthermore, cell F5’s calculated result also changed, but crucially, the original formula in the older version was incorrect (it only summed B5:D5, omitting Q4). In the updated workbook, the formula in F5 was corrected to =SUM(B5:E5). Spreadsheet Compare not only highlights changes in values but can also help identify formula errors corrected between versions.
- Resize for Readability: If cell contents are truncated due to column width, simply click Resize Cells to Fit to automatically adjust column widths for better readability of the compared data.
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. From the Inquire tab, you can:
- Analyze Workbooks: Gain insights into workbook structure, dependencies, and potential issues.
- Visualize Relationships: Explore connections between cells, worksheets, and even links to external workbooks.
- Clean Excess Formatting: Optimize workbook performance by removing unnecessary formatting.
While Spreadsheet Compare is a dedicated tool for comparing files externally, the Inquire add-in enhances your ability to analyze and understand Excel workbooks directly within Excel. You can even launch Spreadsheet Compare from within Excel using the “Compare Files” command if you have two workbooks open that you wish to compare.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. See Turn on the Inquire add-in for instructions. To delve deeper into the capabilities of the Inquire add-in, explore What you can do with Spreadsheet Inquire.
Taking it Further
For organizations managing critical Excel workbooks and Access databases, Microsoft offers robust management tools:
- Microsoft Audit and Control Management Server: Provides advanced change management features specifically for Excel and Access files, ensuring data integrity and control.
- Microsoft Discovery and Risk Assessment Server: Offers inventory and analysis capabilities to identify and mitigate risks associated with user-developed Excel and Access tools.
These server solutions complement Spreadsheet Compare and the Inquire add-in, providing a comprehensive approach to managing and auditing spreadsheet and database environments.
For a broader overview of Spreadsheet Compare, you can also consult Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare and the Inquire add-in, you can significantly streamline the process of comparing Excel workbooks for differences, ensuring accuracy, identifying potential issues, and maintaining control over your critical spreadsheet data.
