For professionals working with data, Excel spreadsheets are indispensable tools. However, managing and tracking changes across multiple versions of these spreadsheets can become a complex task. Whether you are dealing with two separate workbooks or revisions of the same file, identifying the exact differences efficiently is crucial. Microsoft Spreadsheet Compare is a powerful utility designed to help you Compare Differences In Excel Spreadsheets and pinpoint discrepancies, formula changes, and more. This guide will walk you through how to effectively use Spreadsheet Compare to streamline your Excel file comparison process.
What is Spreadsheet Compare?
Spreadsheet Compare is a Microsoft tool specifically engineered to compare differences in Excel spreadsheets. It generates detailed reports highlighting the variances between two Excel workbooks. This is particularly useful for identifying potential issues such as manual data entries overriding formulas or broken calculations. It’s important to note that Spreadsheet Compare is not a standalone application available with every version of Excel. It’s included with specific Microsoft Office suites like Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Alongside Spreadsheet Compare, there is also Microsoft Database Compare for Access databases, available with the same Office Professional Plus versions or Microsoft 365 Apps for enterprise, offering similar comparison functionalities for database files.
Step-by-Step Guide to Compare Excel Files
Using Spreadsheet Compare is straightforward. Follow these steps to effectively compare your Excel workbooks:
Opening Spreadsheet Compare
To begin, you need to launch the Spreadsheet Compare application.
- Navigate to the Start menu in Windows.
- Look for Spreadsheet Compare. It might be listed directly.
- 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.
Selecting Files for Comparison
Once Spreadsheet Compare is open, you need to specify the Excel files you want to compare.
- Click on Home > Compare Files. This action will open the Compare Files dialog box.
- Locate the Compare box. Click the blue folder icon situated next to it. This will allow you to browse your file system to find the older version of your Excel workbook. You can select files from your local computer, network drives, or even by entering a web address if your workbooks are saved online.
-
Next, find the To box. Click the green folder icon next to it to browse for the more recent version of the workbook that you wish to compare against the older one. Click OK after selecting the file.
Tip: Spreadsheet Compare allows you to compare two files even if they have identical names, provided they are stored in different folders. This is particularly useful when dealing with version-controlled documents.
Choosing Comparison Options
Before running the comparison, you can customize what aspects of the workbooks Spreadsheet Compare should analyze.
- In the left pane of the Compare Files dialog box, you will see a list of options.
- Check or uncheck the boxes next to options like Formulas, Macros, and Cell Format to specify what elements you want to include in the comparison report.
- If you want to compare all aspects, simply check the Select All option.
Running the Comparison
With your files selected and options chosen, you are ready to initiate the comparison process.
-
Click OK in the Compare Files dialog box to start the comparison.
In some cases, you might encounter an “Unable to open workbook” message. This typically indicates that one or both of the Excel workbooks are password protected.
-
If you receive this message, click OK. You will then be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and analyze the contents of password-protected files. For more details on password handling, refer to how passwords and Spreadsheet Compare work together.
Once the comparison is complete, the results will be displayed in a split-screen grid, making it easy to visually identify the differences between the two Excel files.
Understanding the Comparison Results
The comparison results are presented in a user-friendly, side-by-side grid. The left pane displays the “Compare” file (usually the older version), and the right pane shows the “To” file (typically the newer one). A detailed pane below the grids provides specific information about the changes detected. Differences are highlighted using colors to categorize the type of change.
-
In the side-by-side grid, each worksheet from one file is compared against the corresponding worksheet in the other. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar.
Note: Spreadsheet Compare analyzes all worksheets, including those that are hidden, ensuring a comprehensive comparison.
-
The highlighting in the grid uses color-coding to indicate the nature of the differences. For instance, cells containing “entered values” (cells with manually typed data rather than formulas) are marked with a green fill color in the grid and a green font in the results list pane. A legend in the lower-left pane clearly explains what each color signifies.
Consider the example where an earlier version of a workbook contains preliminary data for Q4. The updated version includes the final figures for Q4 in column E. In the comparison results:
-
Cells E2:E5 in both versions are highlighted with a green fill, indicating that an entered value has been modified.
-
Consequently, the calculated values in the YTD column (F) have also changed. Cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying a change in calculated values.
-
Cell F5 also shows a change, but the underlying reason is more significant: the original formula in the older version was incorrect (=SUM(B5:D5), missing Q4). The updated workbook corrected the formula in F5 to =SUM(B5:E5).
-
If cell contents are truncated due to column width, click Resize Cells to Fit to automatically adjust column widths and display the complete cell information.
Exploring the Excel Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analytical capabilities. Once activated, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can perform workbook analysis, visualize relationships between cells and worksheets, examine links to other workbooks, and remove excessive formatting from worksheets to improve performance. For users who frequently need to compare workbooks directly within Excel, the Inquire add-in provides the “Compare Files” command, which essentially launches Spreadsheet Compare from within Excel.
If the Inquire tab is not visible in your Excel, you may need to enable it. Instructions for activation can be found at Turn on the Inquire add-in. To delve deeper into the functionalities of the Inquire add-in, see What you can do with Spreadsheet Inquire.
Next Steps
For organizations that rely heavily on “mission-critical” Excel workbooks and Access databases, implementing robust management tools is advisable. Microsoft offers solutions like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server. These tools provide advanced change management, inventory, and analysis features specifically designed for Excel and Access files. They aim to mitigate risks associated with end-user developed tools in these applications.
For a comprehensive overview of Spreadsheet Compare and its features, you can also refer to Overview of Spreadsheet Compare. By utilizing Spreadsheet Compare effectively, you can significantly enhance your ability to manage and control changes within your Excel spreadsheets.
