Comparing two Excel spreadsheets can be a tedious task, especially when dealing with large datasets or complex formulas. Fortunately, Microsoft provides a powerful tool called Spreadsheet Compare to simplify this process. This guide will walk you through how to use Spreadsheet Compare to identify differences and potential issues between two Excel workbooks.
Launching Spreadsheet Compare
Spreadsheet Compare is included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. To open it:
- Click the Start button.
- Search for Spreadsheet Compare and select it from the results.
Comparing Excel Files Step-by-Step
Once Spreadsheet Compare is open, follow these steps to compare two Excel workbooks:
-
Select Files: In the Spreadsheet Compare window, click Home > Compare Files. A dialog box will appear.
-
Choose Files to Compare: Browse to the location of the first workbook (“Compare” – usually the older version) and the second workbook (“To” – usually the newer version). You can select files from your computer, network, or even a web address.
-
Select Comparison Options: In the left pane of the dialog box, choose which elements to compare (Formulas, Macros, Cell Format, etc.). You can select individual options or simply choose Select All.
-
Initiate Comparison: Click OK to begin the comparison process. If a workbook is password-protected, you’ll be prompted to enter the password.
-
Review Results: The comparison results will appear in a two-pane grid. The left pane displays the “Compare” file, and the right pane displays the “To” file. Differences are highlighted with color-coded cells and a detailed list in a pane below the grids.
Understanding the Results
Spreadsheet Compare uses a color-coding system to highlight differences:
- Green: Indicates a changed entered value (non-formula cell).
- Blue-Green: Indicates a changed calculated value.
- Other Colors: Represent other types of changes (e.g., formatting, macros). A legend in the lower-left pane explains each color’s meaning.
Additional Features: Excel’s Inquire Add-in
Excel also offers the Inquire add-in, which provides more in-depth analysis tools. You can activate this add-in to access features like workbook analysis, cell relationship diagrams, and formatting cleanup. The Inquire tab includes a Compare Files command for quick access to Spreadsheet Compare.
Conclusion
Spreadsheet Compare offers a powerful and efficient way to compare Excel documents. By leveraging its features and understanding the results, users can easily identify discrepancies, track changes, and ensure data accuracy across different versions of their spreadsheets. For organizations with complex spreadsheet management needs, Microsoft offers more advanced tools like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server.
