Comparing documents in Microsoft Word is a common task, but what about Excel spreadsheets? Can you compare two Excel files to identify differences? The answer is yes, with Microsoft Spreadsheet Compare. This powerful tool helps pinpoint changes, from simple data entries to complex formula modifications.
Using Spreadsheet Compare in Excel
Spreadsheet Compare is a valuable feature available in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. It allows you to compare two versions of an Excel workbook and highlight the differences. Here’s how to use it:
-
Launch Spreadsheet Compare: Open the application from your Microsoft Office suite.
-
Select Comparison Options: In the lower-left pane, choose the elements you want to compare, such as formulas, cell formatting, macros or simply “Select All.”
-
Initiate the Comparison: On the “Home” tab, click “Compare Files.”
-
Choose Files to Compare: In the “Compare Files” dialog box, browse to select the older version of your workbook in the “Compare” row. You can use files stored locally, on a network, or even from a website by entering the web address. In the “To” row, browse to select the newer version you want to compare it against. Note that files with the same name can be compared as long as they are in different folders.
-
Run the Comparison: Click “OK” to begin the comparison process. If a workbook is password protected, you’ll need to enter the password to proceed.
Understanding the Comparison Results
The results are displayed in a two-pane grid. The left pane shows the “Compare” file (older version), and the right pane shows the “To” file (newer version). A detailed list of changes appears in a pane below the grids. Color-coding highlights different types of changes.
- Worksheet Comparison: Each worksheet in one file is compared to its corresponding worksheet in the other file. Even hidden worksheets are included in the comparison.
- Cell Content Display: If cell contents are not fully visible, click “Resize Cells to Fit” to adjust the column width.
- Color-Coded Differences: Changes are highlighted with different colors for cell fill or text font, indicating the type of difference. For instance, entered values (non-formula cells) are highlighted with green. A legend in the lower-left pane explains the meaning of each color.
Working with Comparison Results
You can further analyze, save, or share your comparison results:
- Exporting Results: Export the results to a user-friendly Excel file by clicking “Home > Export Results.”
- Copying Results: Copy the results to the clipboard to paste them into other applications like Microsoft Word using “Home > Copy Results to Clipboard.”
- Viewing Workbook Colors: To see the original cell formatting, click “Home > Show Workbook Colors.”
Additional Uses of Spreadsheet Compare
Beyond simple comparisons, Spreadsheet Compare offers more advanced functionalities:
- Auditing and Tracking Changes: Maintain an audit trail of critical workbooks by tracking changes over time, helping identify and correct errors.
- Comparing VBA Code: Compare differences in Visual Basic for Applications (VBA) code between two workbooks. The results are displayed side-by-side in a separate window.
In conclusion, while Excel doesn’t directly compare documents in the same way as Word’s “Compare” feature, Spreadsheet Compare provides a robust solution for comparing Excel workbooks, identifying changes, and managing revisions effectively.
