Comparing two Excel documents to identify differences can be a daunting task, especially when dealing with large spreadsheets. Whether you’re managing versions of financial reports, tracking changes in project data, or auditing for errors, manually going through each cell is time-consuming and prone to mistakes. Fortunately, Microsoft offers a built-in tool called Spreadsheet Compare, designed to efficiently highlight the discrepancies between two Excel workbooks. This guide will walk you through how to use Spreadsheet Compare to quickly pinpoint changes, ensuring accuracy and saving valuable time.
Opening Spreadsheet Compare
Microsoft Spreadsheet Compare is a powerful utility included with specific versions of Microsoft Office. To access it, the first step is to locate and open the application.
-
Accessing from the Start Menu: Click on the Start button in Windows. In the Start Menu, look for Spreadsheet Compare. It might be listed directly, or you may need to scroll through your applications to find it.
-
Using Search: If you don’t immediately see Spreadsheet Compare, simply start typing “Spreadsheet Compare” directly in the Start Menu search bar. The application should appear as a search result. Click on Spreadsheet Compare to launch it.
It’s worth noting that Spreadsheet Compare is available with specific editions of Microsoft Office, including Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you are using a different version, you may need to check your Office suite inclusions or consider upgrading to gain access to this valuable tool.
Once opened, Spreadsheet Compare presents a straightforward interface ready to help you analyze your Excel files.
Step-by-Step Guide to Compare Excel Files
With Spreadsheet Compare open, you can easily initiate the comparison process. Follow these steps to compare two Excel workbooks for differences:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on the Compare Files button. This action will open the Compare Files dialog box, prompting you to select the Excel files you want to analyze.
-
Select the Older Workbook (Compare File): In the Compare Files dialog box, you’ll see two boxes: Compare and To. Click on the blue folder icon situated next to the Compare box. This will open a file explorer window. Browse to and select the location of the older version of your Excel workbook, which you want to use as the basis for comparison. You can choose files stored locally on your computer, on a network drive, or even input a web address if your files are hosted online.
-
Select the Newer Workbook (To File): Next, click on the green folder icon next to the To box. Again, a file explorer window will appear. This time, browse to and select the newer version of the Excel workbook that you want to compare against the older version. After selecting both files, click OK in the file selection dialog.
-
Choose Comparison Options: Before running the comparison, you have the option to specify what elements of the workbooks you want to compare. In the left pane of the Compare Files dialog, you’ll find a list of options such as Formulas, Macros, Cell Format, and more. You can check or uncheck these options to customize your comparison. For a comprehensive analysis, you can simply click Select All.
-
Run the Comparison: Once you have selected your files and chosen your comparison options, click the OK button to start the comparison process. Spreadsheet Compare will now analyze both Excel workbooks to identify and highlight any differences based on your selected criteria.
After clicking OK, Spreadsheet Compare will process the files. If either of the workbooks is password-protected, you might be prompted to enter the password. Enter the correct password to proceed with the comparison.
Understanding the Comparison Results
Once the comparison is complete, Spreadsheet Compare presents the results in an intuitive two-pane grid. The left pane displays the “Compare” file (older version), and the right pane shows the “To” file (newer version). A detailed pane below the grids provides a legend and further information about the detected changes.
-
Side-by-Side Worksheet Comparison: For workbooks with multiple worksheets, Spreadsheet Compare compares corresponding worksheets from each file side by side. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison and displayed in the results.
-
Color-Coded Highlighting: Differences are visually highlighted using distinct colors within the grid. The color coding helps quickly identify the type of change. For instance, cells containing “entered values” (values not derived from formulas) that have been modified are typically highlighted with a green fill color in the grid and a green font in the results list. A legend in the lower-left pane clearly explains the meaning of each color code.
In the example result shown, you can observe that cells with direct data entries that have changed are marked in green, while cells with formulas that have produced different results due to upstream changes are highlighted in blue-green. This visual differentiation makes it easy to understand both direct edits and their cascading effects on calculations within the spreadsheet.
- Cell Content Display: If cell content is too wide to be fully displayed within the grid, you can use the Resize Cells to Fit option to automatically adjust column widths for better readability.
Leveraging Excel’s Inquire Add-in
In addition to Spreadsheet Compare, Excel itself offers the Inquire add-in, which provides further analytical capabilities. While Spreadsheet Compare is a standalone application designed specifically for comparing files, the Inquire add-in integrates directly into Excel 2013 and later versions, offering tools for workbook analysis, relationship visualization, and even cleaning up excessive formatting.
If you have Excel workbooks open and wish to utilize Spreadsheet Compare, you can directly launch it from within Excel using the Compare Files command found within the Inquire add-in tab, provided the add-in is enabled.
To enable the Inquire add-in, if you don’t see the “Inquire” tab in your Excel ribbon, you will need to activate it through Excel’s options. Once enabled, the Inquire tab provides a suite of tools to deeply analyze and understand your workbooks.
Conclusion
Spreadsheet Compare is an invaluable tool for anyone who needs to Compare Two Excel Documents For Differences. Its ability to quickly and visually highlight discrepancies in data, formulas, and formatting saves significant time and reduces the risk of errors. By following the steps outlined in this guide, you can effectively utilize Spreadsheet Compare to maintain data integrity, manage workbook versions, and ensure the accuracy of your Excel-based work. For organizations managing critical Excel workbooks, considering Microsoft’s comprehensive spreadsheet management tools can further enhance control and reduce risks associated with end-user developed applications.
