It’s a common scenario: you’re working with Excel, and you have two versions of the same spreadsheet. Perhaps a colleague made edits, or you’re tracking changes over time. The big question is, “What exactly has changed?”. Manually going line by line is time-consuming and prone to errors. Fortunately, Microsoft provides a powerful tool called Spreadsheet Compare to quickly and efficiently Excel How To Compare Two Spreadsheets and pinpoint the discrepancies.
Important Note: Before we dive in, it’s crucial to know that Spreadsheet Compare is not available in all versions of Excel. It’s included with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. If you have one of these versions, you’re in luck!
Getting Started with Spreadsheet Compare
Let’s walk through how to use this handy tool to effectively excel how to compare two spreadsheets.
-
Open Spreadsheet Compare: The first step is to launch the application. You might find it in your Start Menu under Microsoft Office Tools, or by searching for “Spreadsheet Compare”.
-
Choose Comparison Options: In the bottom-left corner of the Spreadsheet Compare window, you’ll see a pane with various options. Here, you can select what aspects of the workbooks you want to compare. This includes:
- Formulas: To check for changes in calculations.
- Cell Formatting: To see if fonts, colors, or styles have been altered.
- Macros: To identify any modifications in VBA code.
- Comments: To track changes in notes added to cells.
- Worksheet Names: To see if worksheets have been renamed.
You can customize your comparison by selecting specific options or simply click Select All for a comprehensive analysis.
-
Select Files to Compare: On the Home tab in Spreadsheet Compare, click Compare Files. This opens the Compare Files dialog box.
-
Choose the “Compare” File (Older Version): In the Compare row, click the browse button to locate and select the earlier version of your Excel workbook. You can browse files on your computer, network drives, or even enter a web address if your files are stored online.
-
Choose the “To” File (Newer Version): In the To row, click the browse button and select the more recent version of the workbook you want to compare against the earlier one.
Tip: You can compare two files with the same name as long as they are saved in different folders. This is useful when you have versioned files.
-
Run the Comparison: Click OK in the Compare Files dialog box to initiate the comparison.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it likely means one or both of your workbooks are password protected. Click OK and you’ll be prompted to enter the password(s). Spreadsheet Compare can handle password-protected files, making it versatile for various situations.
Understanding the Comparison Results
Once the comparison is complete, Spreadsheet Compare presents the findings in a clear, two-pane grid. The older “Compare” file is displayed on the left, and the newer “To” file is on the right. Below the grids, a details pane provides further information.
Changes are visually highlighted with colors, making it easy to spot differences at a glance.
- Side-by-Side Worksheet Comparison: For each file, Spreadsheet Compare compares worksheets starting from the leftmost sheet. Even hidden worksheets are included in the comparison, ensuring no changes are missed.
- Resizing Cells for Content: If cell content is truncated due to narrow columns, simply click Resize Cells to Fit to expand column widths and view the full content.
- Color-Coded Differences: Differences are highlighted using fill colors or font colors, depending on the type of change. For instance, cells containing “entered values” (not formulas) are often marked with a green fill in the grid and a green font in the results list. The legend in the lower-left pane clearly explains what each color represents, so you can quickly understand the nature of each change.
Exporting and Further Analyzing Comparison Results
Spreadsheet Compare offers options to save or further analyze the comparison results outside of the application itself.
- Export to Excel: To save the comparison results in an easily readable Excel file, click Home > Export Results. This creates a new Excel file summarizing the differences.
- Copy to Clipboard: For integration with other applications like Microsoft Word, click Home > Copy Results to Clipboard. This allows you to paste the comparison data into another document.
- Show Workbook Colors: To view the worksheets with their original cell formatting as they appear in Excel, click Home > Show Workbook Colors. This provides a high-fidelity visual representation of the spreadsheets.
Why is Comparing Spreadsheets Important?
Knowing excel how to compare two spreadsheets is valuable in various real-world scenarios:
- Auditing and Compliance: For organizations undergoing audits, Spreadsheet Compare can provide an audit trail of critical workbooks, showing changes made over time. This helps in identifying and correcting errors proactively before audits.
- Version Control and Collaboration: When multiple people collaborate on spreadsheets, or when tracking changes across different versions, Spreadsheet Compare becomes essential for understanding modifications and ensuring data integrity.
- VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also compare VBA (Visual Basic for Applications) code within workbooks. The results are displayed side-by-side, highlighting differences in the code, which is crucial for developers and those managing complex Excel solutions.
In conclusion, Spreadsheet Compare is a powerful tool for anyone needing to excel how to compare two spreadsheets. It saves time, reduces errors, and provides a clear, visual representation of changes, making it an indispensable asset for Excel users in various professional contexts.
