Have you ever opened an Excel workbook and wondered who made changes and what those changes were? Microsoft Spreadsheet Compare is a tool designed to help you answer these questions by identifying and highlighting the differences between two Excel spreadsheets. This guide will walk you through how to effectively use Spreadsheet Compare to track changes in your Excel files.
Important: Spreadsheet Compare is included with 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 use this feature.
Step-by-Step Guide to Comparing Excel Spreadsheets
Here’s how you can easily compare two Excel spreadsheets using Spreadsheet Compare:
-
Launch Spreadsheet Compare.
-
Select Comparison Options. In the bottom-left corner, specify what elements you want to include in your comparison. You can choose from formulas, cell formatting, macros, and more. For a comprehensive comparison, simply click Select All.
-
Open Compare Files Dialog. On the Home tab, find and click Compare Files.
-
Choose the Earlier Version (Compare). In the Compare Files dialog box, locate the Compare row. Click Browse to select the older version of your Excel workbook. You can choose files from your computer, network locations, or even enter a web address if your workbooks are saved online.
Alt text: Browse button in Spreadsheet Compare dialog box, used to select the older version of the Excel file for comparison. -
Choose the Later Version (To). In the same Compare Files dialog box, go to the To row and click Browse to select the more recent version of the workbook you want to compare against the earlier one.
Alt text: To button in Spreadsheet Compare dialog box, used to select the newer version of the Excel file for comparison.Note: You can compare two files with identical names as long as they are stored in different folders.
-
Run the Comparison. Click OK to initiate the comparison process.
Note: If you encounter an “Unable to open workbook” message, it’s likely that the workbook is password protected. Click OK and enter the password when prompted. For more details on password handling, refer to how passwords and Spreadsheet Compare work together.
Understanding the Comparison Results
Once the comparison is complete, the results are displayed in a split-screen grid. The left pane shows the “Compare” file (earlier version), and the right pane displays the “To” file (later version). A detailed summary of the changes is provided in a pane below the grids. Changes are highlighted with different colors, each representing a specific type of modification.
Here’s what to expect when reviewing the results:
- Worksheet Comparison: Spreadsheet Compare compares worksheets side-by-side, starting from the leftmost worksheet in each workbook. Hidden worksheets are also included in the comparison.
- Resize Cells for Better Visibility: If cell contents are truncated due to column width, click Resize Cells to Fit to expand column widths and view the complete content.
Alt text: Resize Cells to Fit button in Spreadsheet Compare, enhancing readability of cell contents by adjusting column widths. - Color-Coded Differences: Differences are visually highlighted using cell fill colors or text font colors. For example, cells containing “entered values” (non-formula cells) are marked with a green fill in the grid and a green font in the results list. The legend in the lower-left pane explains the meaning of each color code.
Further Actions with Comparison Results
Spreadsheet Compare offers several options to manage and utilize your comparison results:
- Export to Excel: To save your comparison results for future reference or easier sharing, export them to an Excel file. Simply click Home > Export Results.
- Copy to Clipboard: If you need to incorporate the results into another document, such as a Microsoft Word report, use the Home > Copy Results to Clipboard option to copy and paste the comparison data.
- Show Workbook Colors: For a view that closely resembles the original Excel formatting, click Home > Show Workbook Colors. This option displays cell formatting as it appears in Excel, providing a high-fidelity visual comparison.
Why Compare Excel Workbooks? Practical Applications
Comparing Excel workbooks is not just about identifying changes; it’s a crucial practice for various scenarios:
- Audit Trails: For organizations undergoing audits, tracking changes in critical workbooks over time is essential. Spreadsheet Compare provides a clear audit trail, helping identify and rectify errors before they are flagged by auditors. This is vital for maintaining data integrity and compliance.
- Version Control: When multiple users collaborate on a workbook, or when you are managing different versions of a document, Spreadsheet Compare helps in pinpointing exactly what has been altered between versions. This ensures everyone is working with the correct and most updated information.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code. The tool presents VBA code comparisons in a side-by-side window, making it easy to review and understand changes in your macros and scripts.
By using Microsoft Spreadsheet Compare, you can efficiently track and understand changes in your Excel spreadsheets, ensuring accuracy, facilitating collaboration, and maintaining control over your data. This tool is invaluable for anyone who works with Excel and needs to keep track of workbook modifications.
