Comparing Excel workbooks for differences in formulas and values is crucial for data accuracy and troubleshooting. Microsoft offers a powerful tool called Spreadsheet Compare to simplify this process. This guide will walk you through how to use Spreadsheet Compare to identify discrepancies between Excel files, focusing on formula comparison.
Using Spreadsheet Compare for Formula Comparison
Spreadsheet Compare, available in Office Professional Plus or Microsoft 365 Apps for enterprise, allows you to analyze two Excel workbooks and pinpoint variations in formulas, values, cell formatting, and more.
Launching Spreadsheet Compare
- Start Menu: Search for “Spreadsheet Compare” in the Windows Start menu.
- Direct Access: If you frequently use the tool, create a desktop shortcut for faster access.
Comparing Two Excel Files
-
Open Spreadsheet Compare: Launch the application.
-
Select Files: In the “Compare Files” dialog box, click the blue folder icon next to the “Compare” field and browse to the older version of your workbook. Repeat this process for the “To” field, selecting the newer version. You can even compare files stored online via a web address.
-
Comparison Options: Select “Formulas” in the left pane to focus on formula differences. You can also select other options like “Macros” or “Cell Format” for a comprehensive comparison. “Select All” compares everything.
-
Initiate Comparison: Click “OK” to start the comparison process. If a workbook is password-protected, enter the password when prompted.
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. A detailed pane below highlights the differences. Color-coded highlights indicate the type of change:
- Green Fill/Font: Changes in entered values (non-formula cells).
- Blue-Green Fill: Changes in calculated values.
For example, a green highlight in a cell containing a formula indicates a direct change to the formula itself. A blue-green highlight in a cell with a formula indicates that the formula is the same, but the result of the calculation has changed due to changes in other cells referenced by the formula. A legend in the lower-left pane clarifies the color meanings.
Tip: If cell contents are truncated, click “Resize Cells to Fit” for better visibility. Hidden worksheets are also included in the comparison.
Beyond Spreadsheet Compare: Excel’s Inquire Add-in
Excel also offers the Inquire add-in, providing additional analysis tools. This add-in allows you to examine cell relationships, workbook connections, and clean up excess formatting. You can also launch Spreadsheet Compare directly from the Inquire tab.
Conclusion
Spreadsheet Compare provides a robust solution for comparing Excel formulas and identifying potential errors or discrepancies between workbook versions. Leveraging this tool helps ensure data accuracy and facilitates efficient troubleshooting. For more complex scenarios, consider Microsoft’s Audit and Control Management Server for advanced change management features.
