When collaborating on Excel workbooks, it’s crucial to keep track of modifications and understand who made what changes. Imagine opening a shared workbook and wondering, “What has been altered?” and “Who made these edits?”. Microsoft Spreadsheet Compare is a powerful tool designed to answer these very questions, allowing you to pinpoint changes and visualize them clearly. This guide provides a comprehensive walkthrough on how to use Excel File Compare to effectively track and analyze differences between Excel files.
Important Note: It’s essential to be aware that Spreadsheet Compare is not available in all versions of Microsoft Office. This tool is included with 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 explore alternative methods for comparing Excel files.
Step-by-Step Guide to Comparing Excel Files
Let’s delve into the process of using Spreadsheet Compare to identify differences between Excel files:
-
Launch Spreadsheet Compare: The first step is to open the Spreadsheet Compare application. You can usually find it by searching for “Spreadsheet Compare” in your Windows search bar.
-
Select Comparison Options: Once Spreadsheet Compare is open, you will see a pane in the lower-left corner. Here, you can customize your comparison by choosing specific elements to include. These options include:
- Formulas: Compares the formulas used in cells.
- Cell Formatting: Identifies changes in cell appearance like fonts, colors, and styles.
- Macros: Checks for differences in VBA macros within the workbooks.
- Comments: Compares comments added to cells.
For a comprehensive comparison, you can simply click Select All to include all these elements in your analysis.
-
Initiate File Comparison: Navigate to the Home tab on the Spreadsheet Compare ribbon and click on Compare Files. This action will open the Compare Files dialog box, prompting you to select the files you wish to compare.
-
Choose the “Compare” File (Earlier Version): In the Compare Files dialog box, locate the Compare row. Click the browse button to select 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 workbooks are saved online. This “Compare” file will serve as the baseline for comparison.
Alt text: Browse button in Excel File Compare dialog box, used to select the original Excel file for comparison.
-
Choose the “To” File (Later Version): Next, in the Compare Files dialog box, go to the To row. Click the browse button to select the more recent version of the workbook that you want to compare against the earlier version.
Alt text: Select file button in Excel File Compare, used to choose the modified Excel file for comparison against the original.
Important Note: You can compare two files even if they have the same name, as long as they are saved in different folders. This is particularly useful when you have versioned copies of your Excel files.
-
Execute the Comparison: Once you have selected both the “Compare” and “To” files, click OK in the Compare Files dialog box to initiate the Excel file comparison process.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it’s likely that one or both of your workbooks are password protected. Click OK on the message prompt and you will be asked to enter the password for the protected workbook. Spreadsheet Compare can work with password-protected files, but you will need to provide the passwords to access their content.
Understanding the Excel File Compare Results
After running the comparison, the results are presented in a user-friendly two-pane grid.
-
Side-by-Side Grid: The left pane displays the “Compare” file (earlier version), and the right pane shows the “To” file (later version). Each worksheet from both files is compared side-by-side, starting from the leftmost worksheet in each workbook. Even hidden worksheets are included in the comparison and displayed.
-
Detailed Differences Pane: Below the two grids, you’ll find a details pane that provides a list of specific changes detected.
-
Color-Coded Highlighting: Changes are visually highlighted using different colors, making it easy to identify the type of modification. The legend in the lower-left pane clearly explains what each color represents. For instance, cells containing “entered values” (cells with direct input, not formulas) are typically highlighted with a green fill color in the grid and a green font in the results list.
If cell content is truncated due to narrow columns, simply click Resize Cells to Fit to automatically adjust column widths for better readability.
Alt text: Resize Cells to Fit button in Excel File Compare, used to adjust column widths to display full cell content.
Advanced Options for Working with Comparison Results
Spreadsheet Compare offers several options to further manage and utilize your comparison results:
-
Export Results to Excel: To save the comparison results for later review or sharing, you can export them to an easily readable Excel file. Click Home > Export Results. This creates a new Excel file summarizing the differences found.
-
Copy Results to Clipboard: If you need to incorporate the comparison results into another document or program like Microsoft Word, you can copy them to the clipboard. Click Home > Copy Results to Clipboard. This allows you to paste the results into other applications.
-
Show Workbook Colors: For a more visually accurate representation of the worksheets, including cell formatting as it appears in Excel, click Home > Show Workbook Colors. This option displays the worksheets with their original formatting, providing a high-fidelity view of the differences.
Why Use Excel File Compare? Practical Applications
Comparing Excel workbooks is beneficial in numerous scenarios:
-
Auditing and Compliance: For organizations undergoing audits, Spreadsheet Compare provides an essential audit trail for critical workbooks. It allows you to track changes over time (month-over-month or year-over-year), helping identify and rectify errors before auditors detect them, ensuring data integrity and compliance.
-
Collaborative Workbook Management: When multiple individuals collaborate on a shared Excel file, tracking changes becomes paramount. Spreadsheet Compare helps teams understand who modified what, ensuring accountability and clarity in collaborative projects.
-
Version Control: Spreadsheet Compare is invaluable for version control, enabling you to compare different versions of a workbook to see exactly what modifications were made between revisions. This is crucial for managing document versions and reverting to previous states if necessary.
-
VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also compare VBA (Visual Basic for Applications) code within Excel workbooks. The results are displayed in a dedicated window, showing side-by-side differences in the code, which is essential for developers and users who rely on macros.
In conclusion, Excel File Compare is a robust and indispensable tool for anyone who needs to effectively track, analyze, and manage changes in Excel files. Whether for auditing, collaboration, or version control, mastering this tool can significantly enhance your efficiency and accuracy when working with spreadsheets.
