Comparing two Excel spreadsheets for similarities and differences is crucial for various tasks, from identifying data discrepancies to tracking changes over time. Microsoft offers a powerful tool called Spreadsheet Compare, designed specifically for this purpose. This article will guide you through using Spreadsheet Compare to effectively analyze and compare your Excel files.
Using Spreadsheet Compare in Excel
Spreadsheet Compare is a dedicated application included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. It allows you to pinpoint differences in formulas, values, macros, and cell formatting between two Excel workbooks.
Launching Spreadsheet Compare
To open Spreadsheet Compare, simply search for it in the Windows Start Menu. You’ll also find a similar tool for Access databases called Microsoft Database Compare, also requiring the same Office versions.
Comparing Two Excel Files
-
Initiate Comparison: Open Spreadsheet Compare and click Home > Compare Files. This opens the Compare Files dialog box.
-
Select Files:
- Click the blue folder icon next to the Compare box to browse and select the older version of your workbook.
- Click the green folder icon next to the To box to select the newer version for comparison. You can even compare files from web addresses.
-
Choose Comparison Options: In the left pane, select the specific elements you want to compare: Formulas, Macros, Cell Format, or simply Select All.
-
Run Comparison: Click OK to initiate the comparison process. If a workbook is password-protected, you’ll be prompted to enter the password.
Understanding the Results
Spreadsheet Compare presents the comparison results in a two-pane grid. The left pane displays the “Compare” (older) file, and the right pane shows the “To” (newer) file. A detailed pane below highlights the specific differences.
- Color-Coded Differences: Changes are highlighted with different colors depending on the type of difference (e.g., entered value changes are highlighted in green, calculated value changes in blue-green). A legend in the lower-left pane explains the color coding.
- Worksheet Navigation: You can compare multiple worksheets within the workbooks using the forward and back buttons on the horizontal scroll bar. Even hidden worksheets are included in the comparison.
- Resizing Cells: If cell contents are not fully visible, click Resize Cells to Fit.
Excel’s Inquire Add-in: A Complementary Tool
Besides Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in. This add-in provides additional analysis capabilities, allowing you to examine cell relationships, workbook connections, and clean up excess formatting. You can activate the Inquire add-in through Excel’s options. The Inquire tab then allows you to access Spreadsheet Compare directly using the Compare Files command.
Conclusion
Spreadsheet Compare offers a robust solution for comparing Excel spreadsheets, enabling you to efficiently identify similarities and differences. Its intuitive interface and detailed reports make it an invaluable tool for data analysis, auditing, and version control. For organizations with complex spreadsheet management needs, Microsoft offers more advanced tools like Microsoft Audit and Control Management Server for comprehensive change management.
