Comparing two Excel files to identify differences can be crucial for various reasons, from tracking changes in financial reports to ensuring data integrity across versions. Microsoft Spreadsheet Compare is a powerful tool designed specifically for this purpose. This guide will walk you through how to effectively use Spreadsheet Compare to pinpoint discrepancies between two Excel workbooks, ensuring accuracy and efficiency in your data management.
Spreadsheet Compare is a valuable utility available with specific Microsoft Office suites, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you have one of these versions, you can leverage this tool to simplify the often tedious task of manual Excel file comparison.
Getting Started with Spreadsheet Compare
To begin, you need to open the Spreadsheet Compare application. Here’s how to access it:
-
From the Start Menu: Click on the Start button and look for Spreadsheet Compare in your applications list. If you don’t see it immediately, simply type “Spreadsheet Compare,” and it should appear as a search result. Select it to open.
It’s worth noting that alongside Spreadsheet Compare, there’s also Microsoft Database Compare for Access databases, which is part of the same suite of tools for professional data management.
Step-by-Step Guide to Comparing Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare your Excel files:
-
Initiate File Comparison: Click on 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 analyze.
-
Select the Original File: In the Compare box, click the blue folder icon. This will allow you to browse your file system to locate the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even enter a web address if your files are stored online.
-
Choose the Revised File: Next, click the green folder icon next to the To box. Navigate to and select the more recent version of the workbook you want to compare against the original. Click OK to confirm your selection.
Tip: You can easily 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.
-
Customize Comparison Options: Before running the comparison, you can specify what aspects of the workbooks you want to examine. In the left pane of the dialog box, you’ll find options such as Formulas, Macros, and Cell Format. Select or deselect these options based on your comparison needs. For a comprehensive comparison, you can simply click Select All.
-
Execute the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze the selected files based on your chosen options.
If you encounter an “Unable to open workbook” message, it’s likely that one of the Excel files is password protected. Click OK and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare is designed to work with password-protected files, allowing you to compare them as needed.
Deciphering the Comparison Results
After running the comparison, the results are displayed in a clear, two-pane grid. The left pane shows the “Compare” file (typically the older version), and the right pane displays the “To” file (usually the newer version). A detailed pane below the grids provides additional information about the detected differences.
-
Side-by-Side Worksheet Comparison: Each worksheet from both files is compared and presented side-by-side. If your workbooks contain 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 displayed in the results, ensuring no part of your workbook is overlooked.
-
Color-Coded Difference Highlighting: Differences are visually highlighted using distinct colors within the grid and the results list. The color coding helps you quickly identify the type of change. For instance, cells containing “entered values” (non-formula cells) that have been modified are typically highlighted with a green fill color in the side-by-side grid and a green font in the results pane. A legend in the lower-left pane clearly explains what each color represents, making it easy to interpret the findings.
Consider an example where a quarterly report has been updated with final figures. In the comparison results, you might see green highlighting in cells where the quarterly values were manually entered and changed. Consequently, calculated fields that depend on these values, such as year-to-date totals, might be highlighted in a different color, like blue-green, indicating that calculated values have changed due to the updated inputs.
In some cases, formula corrections might also be highlighted. For example, if a formula in the older version was incorrect and has been fixed in the newer version, Spreadsheet Compare will highlight this, helping you identify and rectify errors in your workbooks.
-
Adjusting Cell Width: If cell contents are not fully visible due to narrow columns, you can click Resize Cells to Fit. This feature automatically adjusts the column widths to ensure that you can view all cell contents without manually resizing columns.
Leveraging Excel’s Inquire Add-in
In addition to Spreadsheet Compare, Excel 2013 and later versions include the Inquire add-in, which offers a range of analytical capabilities. Once enabled, 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, and even remove excessive formatting from worksheets to improve performance.
While Spreadsheet Compare is excellent for direct file comparisons, the Inquire add-in provides tools for deeper workbook analysis within Excel itself. If you need to compare two open workbooks in Excel, you can conveniently launch Spreadsheet Compare directly from the Inquire tab using the Compare Files command.
If you don’t see the Inquire tab in your Excel, you may need to activate it. You can find instructions on how to Turn on the Inquire add-in. To explore the full potential of the Inquire add-in, refer to the guide on What you can do with Spreadsheet Inquire.
Taking the Next Steps in Excel File Management
For organizations that rely heavily on Excel workbooks and Access databases for critical operations, implementing robust management tools is essential. Consider exploring Microsoft Audit and Control Management Server, which offers advanced change management features for Excel and Access files. Complementary to this is Microsoft Discovery and Risk Assessment Server, designed for inventory and analysis, helping mitigate risks associated with user-developed tools in Excel and Access.
To gain a broader understanding of Spreadsheet Compare and its functionalities, you can also review the Overview of Spreadsheet Compare.
By utilizing Spreadsheet Compare and Excel’s Inquire add-in, you can significantly enhance your ability to compare Excel files for differences, ensuring data accuracy, version control, and overall efficiency in your spreadsheet management practices.
