Have you ever needed to know what changes were made to your Excel workbooks, especially when multiple people are involved in editing? Microsoft Spreadsheet Compare is a powerful tool designed to answer the question “What’s different between these two Excel files?” by identifying and highlighting the exact changes.
Important: Spreadsheet Compare is included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Make sure you have one of these versions to use this feature.
Here’s a step-by-step guide on how to compare Excel files using Spreadsheet Compare:
-
Launch Spreadsheet Compare. Find “Spreadsheet Compare” in your Windows Start menu and open the application.
-
Select Comparison Options. In the bottom-left corner of the Spreadsheet Compare window, you’ll see options to customize your comparison. You can choose to include formulas, cell formatting, macros, and more. For a comprehensive comparison, simply click Select All.
-
Initiate File Comparison. Go to the Home tab in the Spreadsheet Compare ribbon and click on Compare Files.
-
Choose the Older Version. In the Compare Files dialog box, locate the Compare row. Click the browse icon to select the earlier version of your Excel workbook. You can choose files from your computer, network drives, or even enter a web address if your files are stored online.
-
Choose the Newer Version. In the same Compare Files dialog box, in the To row, click the browse icon to select the more recent version of the workbook you want to compare against the older one.
Note: Spreadsheet Compare allows you to compare two files even if they have the same name, as long as they are saved in different folders.
-
Run the Comparison. Click OK to start the comparison process.
Note: If you encounter an “Unable to open workbook” message, it’s likely that one of the workbooks 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.
Once the comparison is complete, the results are displayed in a clear, two-pane grid. The older workbook (“Compare” file) is shown on the left, and the newer workbook (“To” file) is on the right. A detailed pane below the grids provides a summary of the changes. Differences are visually highlighted with distinct colors, each representing a specific type of change.
Understanding the Comparison Results
- Side-by-Side Worksheet Comparison: Spreadsheet Compare compares worksheets in each file, starting from the leftmost sheet in both workbooks. Even hidden worksheets are included in the comparison and displayed.
- Cell Content Visibility: If cell contents are truncated due to narrow columns, simply click Resize Cells to Fit to automatically adjust column widths for better readability.
- Color-Coded Differences: Changes are highlighted using cell fill colors or text font colors. For instance, cells containing “entered values” (cells with direct input, not formulas) are marked with a green fill color in the grid and a green font in the results list below. The legend in the lower-left pane clearly explains what each color signifies.
Leveraging Comparison Results Further
Spreadsheet Compare offers several options to work with your comparison results:
- Export to Excel: To save the comparison results for record-keeping or further analysis, you can export them to an Excel file. Click Home > Export Results. This creates an easy-to-read Excel report of the differences.
- Copy to Clipboard: For sharing or incorporating results into other documents, use Home > Copy Results to Clipboard. This allows you to paste the comparison details into applications like Microsoft Word or email.
- Show Workbook Colors: To view the worksheets with their original formatting from Excel, click Home > Show Workbook Colors. This provides a high-fidelity visual representation of each worksheet, as close as possible to how they appear in Excel.
Why Compare Excel Workbooks? Practical Applications
Comparing Excel files with Spreadsheet Compare is beneficial in various scenarios:
- Audit Trails and Compliance: For organizations facing audits, Spreadsheet Compare provides a crucial audit trail for critical workbooks. It helps track changes over time (monthly or yearly), enabling proactive identification and correction of errors before audits.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code within Excel files. The VBA comparison results are presented in a side-by-side window, making it easy to pinpoint code modifications.
- Collaborative Workbook Management: When teams collaborate on Excel workbooks, this tool becomes invaluable for understanding who changed what and when, ensuring version control and clarity.
In conclusion, Microsoft Spreadsheet Compare is an essential tool for anyone working with Excel files, especially in collaborative or audit-sensitive environments. It simplifies the process of comparing Excel files, tracking changes, and maintaining data integrity.
