Yes, Excel can compare two spreadsheets for differences. Microsoft offers a powerful tool called Spreadsheet Compare, designed specifically for this task. It helps identify discrepancies between two versions of an Excel workbook, highlighting changes in formulas, values, macros, and even cell formatting. This article will guide you on how to use Spreadsheet Compare effectively.
How to Use Spreadsheet Compare in Excel
Spreadsheet Compare is a separate application included with specific Microsoft Office suites, namely:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
Launching Spreadsheet Compare:
- Type “Spreadsheet Compare” in the Windows search bar and select the application.
- Alternatively, if you’re using a compatible Office version, you might find it within the Office suite applications.
Comparing Two Excel Files:
- In Spreadsheet Compare, click Home > Compare Files.
- Browse and select the older version of your workbook in the “Compare” dialog box. You can select files from your computer, network, or even provide a web address.
- Similarly, browse and select the newer version of your workbook in the “To” dialog box.
- Customize the comparison by selecting specific elements to compare, such as Formulas, Macros, Cell Format, or simply choose “Select All”.
- Click OK to initiate the comparison process.
Interpreting the Results:
Spreadsheet Compare presents the results in a two-pane grid, displaying the older version on the left and the newer version on the right.
- Each worksheet is compared individually, accessible through navigation buttons. Hidden worksheets are also included in the comparison.
- Differences are highlighted with color-coded cells and text. For instance, changed entered values are highlighted in green, while changes in calculated values are indicated by a blue-green fill. A legend at the bottom left clarifies the color coding.
- The lower pane provides detailed information about each difference.
- If cell contents are not fully visible, use the “Resize Cells to Fit” option.
Beyond Spreadsheet Compare: Excel’s Inquire Add-in
Excel also offers the “Inquire” add-in, providing additional analysis capabilities:
- Analyze workbook relationships between cells, worksheets, and external workbooks.
- Clean excess formatting from worksheets.
- Access Spreadsheet Compare directly through the “Compare Files” command within the Inquire tab.
To enable the Inquire add-in, go to Excel Options > Add-Ins > Manage Excel Add-ins > Go and check the “Inquire” box.
Conclusion
Spreadsheet Compare offers a comprehensive solution for comparing Excel spreadsheets, pinpointing differences down to the cell level. Its intuitive interface and clear visual highlighting make identifying discrepancies efficient and straightforward. Combined with the Inquire add-in’s analytical capabilities, Excel provides robust tools for managing and analyzing workbook changes. For organizations handling critical spreadsheet data, consider exploring Microsoft’s more advanced spreadsheet and database management tools for enhanced control and risk mitigation.
