Ever opened an Excel workbook and wondered what changes were made since you last saw it? Microsoft Excel’s Spreadsheet Compare tool is designed to help you pinpoint those modifications, highlighting exactly what’s different between two versions of the same workbook. This is invaluable for collaboration, auditing, and ensuring data accuracy.
Important Note: Spreadsheet Compare is included in 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 access this feature.
How to Use Excel Compare to Identify Changes
Let’s walk through the process of comparing Excel files using Spreadsheet Compare:
-
Launch Spreadsheet Compare. Find “Spreadsheet Compare” in your applications menu and open it.
-
Select Comparison Options. In the bottom-left corner, you’ll see options to customize your comparison. Choose what you want to analyze, such as formulas, cell formatting, links, or macros. For a comprehensive comparison, simply click Select All.
-
Choose Files to Compare. Navigate to the Home tab and click Compare Files.
-
Specify the “Compare” File (Older Version). In the Compare Files dialog box, locate the Compare row. Click the browse icon 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 files are stored online.
Alt text: Browse button in Spreadsheet Compare dialog box, used to select the older Excel file for comparison.
-
Specify the “To” File (Newer Version). 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.
Alt text: To button in Spreadsheet Compare dialog box, used to select the newer Excel file for comparison.
Note: You can compare two files with identical names as long as they are saved in different folders.
-
Run the Comparison. Click OK to initiate the file comparison process.
Password Protected Files: If you encounter an “Unable to open workbook” message, it likely means one or both workbooks are password-protected. Click OK and enter the password(s) when prompted. For more information on password handling, refer to how passwords and Spreadsheet Compare work together.
Understanding Excel Compare Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
-
Side-by-Side View: The left pane shows the “Compare” file (older version), and the right pane displays the “To” file (newer version). A detailed summary of changes appears in a pane below the grids.
-
Worksheet Comparison: Spreadsheet Compare analyzes worksheets from left to right in each workbook. Even hidden worksheets are included in the comparison and displayed.
-
Cell Content Visibility: If cell contents are truncated due to column width, click Resize Cells to Fit to expand columns and view all data.
Alt text: Resize Cells to Fit button in Spreadsheet Compare, used to adjust column widths for better readability of cell contents.
-
Color-Coded Differences: Changes are highlighted with distinct colors, making it easy to identify the type of modification. For instance, cells containing directly entered values (not formulas) are marked with a green fill in the grids and a green font in the results list. The legend in the lower-left pane clearly explains the color codes.
Further Actions with Comparison Results
Spreadsheet Compare offers options to work with your comparison results beyond the initial view:
-
Export to Excel: To save the comparison results for later review or sharing, click Home > Export Results. This creates an Excel file summarizing the differences in an easy-to-read format.
-
Copy to Clipboard: For integration with other applications like Microsoft Word or for custom analysis, use Home > Copy Results to Clipboard. This allows you to paste the comparison data into another program.
-
Show Workbook Colors: To view the worksheets with their original Excel formatting, including colors and styles, click Home > Show Workbook Colors. This provides a high-fidelity representation of each worksheet as it appears in Excel.
Why Use Excel Compare? Practical Applications
Excel Compare is more than just a tool for curiosity; it’s a valuable asset in various scenarios:
-
Audit Trails: For organizations undergoing audits, Spreadsheet Compare creates a clear trail of changes in critical workbooks over time. This helps identify and rectify errors proactively before audits.
-
Change Management: When collaborating on spreadsheets, it ensures everyone is aware of modifications, reducing confusion and improving version control.
-
VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also analyze differences in VBA (Visual Basic for Applications) code within workbooks. The VBA comparison results are presented in a separate window for side-by-side code review.
In conclusion, Excel Compare is a powerful and essential tool for anyone working with Excel workbooks, especially in collaborative or regulated environments. It simplifies the process of identifying and understanding changes, contributing to data integrity and efficient workflow.
