Comparing files in Excel, especially when dealing with multiple versions or workbooks, can be a daunting task. Manually identifying differences is time-consuming and prone to errors. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare, designed to efficiently highlight discrepancies between Excel files. This guide will walk you through everything you need to know about using Spreadsheet Compare to effectively analyze and compare your Excel workbooks.
What is Spreadsheet Compare?
Spreadsheet Compare is a utility from Microsoft designed to compare different versions of Excel workbooks or even entirely separate workbooks. It generates a detailed report highlighting a range of differences, from simple content changes to more complex issues like formula discrepancies or formatting variations. This tool is invaluable for anyone needing to audit changes, troubleshoot errors, or ensure data consistency across multiple Excel files.
Important Note: Spreadsheet Compare is not a standalone application available with all versions of Excel. It is included with specific Microsoft Office suites, namely:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you are unsure whether you have access to Spreadsheet Compare, you can check by following the steps in the next section to open it. If you cannot find it, it’s likely not included in your Office version.
Accessing Spreadsheet Compare
Opening Spreadsheet Compare is straightforward. Here’s how to launch it:
- From the Start Menu: Click on the Start button in Windows.
- Find Spreadsheet Compare: Look for “Spreadsheet Compare” in your list of applications. If you don’t see it immediately, start typing “Spreadsheet Compare”.
- Launch the Application: Select “Spreadsheet Compare” from the search results or application list to open the program.
Alongside Spreadsheet Compare, Microsoft also provides Database Compare for Access databases. This companion tool, Microsoft Database Compare, shares similar functionality for comparing Access database files and is also included in the same Office Professional Plus and Microsoft 365 Apps for enterprise suites.
Step-by-Step Guide: Comparing Two Excel Files
Once you have Spreadsheet Compare open, comparing two Excel workbooks is a simple process:
-
Initiate File Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select the Older Workbook (Compare File): Locate the Compare box in the dialog. Click on the blue folder icon situated next to this box. This will open a file explorer window, allowing you to browse your computer or network to find the earlier version of your Excel workbook. You can also paste a web address directly if your files are stored online.
-
Select the Newer Workbook (To File): Next, find the To box. Click on the green folder icon beside it. Use the file explorer window to locate the more recent version of the Excel workbook 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 the same name, as long as they are saved in different folders. This is particularly useful when comparing versions of the same file saved in different locations.
-
Choose Comparison Options: In the left-hand pane of the Compare Files dialog, you will see a list of options. These options let you specify what aspects of the workbooks you want to compare. You can choose to compare:
- Formulas: Detects changes in formulas between the workbooks.
- Macros (VBA Code): Highlights differences in VBA macros.
- Cell Format: Identifies changes in cell formatting, such as fonts, colors, and styles.
- And more: There are other options available, allowing for granular control over the comparison.
To compare all aspects, simply check the Select All box. Choose the options relevant to your comparison needs and click OK to begin the comparison process.
-
Password Protected Files: If either of the workbooks is password-protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK in the message box. You will then be prompted to enter the password for the protected workbook. Spreadsheet Compare needs the password to access and compare the file contents. You can find more information about password handling in Spreadsheet Compare in how passwords and Spreadsheet Compare work together.
-
Review the Comparison Results: Once the comparison is complete, the results are displayed in a split-screen grid.
- Side-by-Side View: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version).
- Detailed Differences Pane: Below the two grids, a details pane provides a summary of the differences found, categorized and color-coded for easy understanding.
- Color-Coded Highlights: Changes are visually highlighted within the grids using different colors, each representing a specific type of modification.
Understanding the Comparison Results
Interpreting the results in Spreadsheet Compare is key to effectively utilizing the tool. Here’s a breakdown of what you’ll see:
-
Worksheet Navigation: If your workbooks contain multiple worksheets, Spreadsheet Compare compares corresponding sheets from each file. You can navigate through the worksheets using the forward and back buttons on the horizontal scroll bar located above the grid.
Note: Even hidden worksheets are included in the comparison and will be displayed in the results.
-
Color-Coding for Difference Types: Spreadsheet Compare uses color-coding to categorize and highlight the types of differences it detects. The specific colors and their meanings are explained in the legend located in the lower-left pane of the results window. Common color indicators include:
- Green Fill: Often indicates “entered values” that have been changed. These are cells containing manually input data rather than formulas.
- Green Font: Also associated with entered values, and might be used in the results list pane.
- Blue-Green Fill: Typically signifies changes in “calculated values.” This means that formula results have changed between the two versions.
- Other Colors: Different colors may be used to indicate other types of changes, such as formatting differences, structural changes, or formula modifications. Always refer to the legend for a complete understanding of the color codes.
Example Interpretation: Consider a scenario where you are comparing quarterly sales reports. If the Q4 figures were preliminary in the older version and finalized in the newer version, Spreadsheet Compare would highlight these changes. In the image example, cells E2:E5 (representing Q4 values) in both versions have a green fill, indicating that entered values have changed. Consequently, the year-to-date (YTD) totals in column F, which are calculated values, also show changes (cells F2:F4 and E6:F6 with blue-green fill).
Furthermore, the example highlights a formula correction. Cell F5 in the older version had an incorrect formula (=SUM(B5:D5)), omitting Q4. The updated version corrects this to =SUM(B5:E5). This demonstrates Spreadsheet Compare’s ability to not only show data changes but also identify formula discrepancies.
- Resize Cells for Better Viewing: If cell contents are truncated due to narrow column widths, click Resize Cells to Fit. This option automatically adjusts column widths to ensure you can view the entire content of each cell without difficulty.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides further analytical capabilities. Activating the Inquire add-in adds an “Inquire” tab to your Excel ribbon.
From the Inquire tab, you can:
- Analyze Workbooks: Gain insights into workbook structure, complexity, and potential errors.
- Explore Relationships: Visualize 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 separate application for file comparison, the Inquire add-in provides in-Excel tools for workbook analysis and auditing. Notably, within Excel, if you have two workbooks open, you can initiate Spreadsheet Compare directly through the Inquire add-in using the Compare Files command.
If you do not see the Inquire tab in your Excel ribbon, you may need to enable it. Refer to Turn on the Inquire add-in for instructions. To delve deeper into the features of the Inquire add-in, consult What you can do with Spreadsheet Inquire.
Next Steps: Advanced Management Tools
For organizations dealing with critical Excel workbooks or Access databases, Microsoft offers more advanced management solutions. Microsoft Audit and Control Management Server provides robust change management features specifically designed for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server offers inventory and analysis features to help mitigate risks associated with user-developed tools in Excel and Access environments.
Conclusion
Spreadsheet Compare is an essential tool for anyone working with Excel files and needing to track changes, identify discrepancies, or ensure data integrity. By efficiently comparing workbooks and highlighting differences, it saves significant time and reduces the risk of errors associated with manual comparisons. Whether you are auditing financial data, managing project spreadsheets, or simply keeping track of document revisions, mastering Spreadsheet Compare will significantly enhance your Excel workflow. For further information, see Overview of Spreadsheet Compare.
