Need to identify changes between two versions of an Excel workbook? Microsoft Spreadsheet Compare pinpoints differences, highlighting them for easy review. This guide outlines how to leverage this powerful tool for efficient spreadsheet comparison.
Using Spreadsheet Compare for Excel File Comparison
Spreadsheet Compare, available in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise, simplifies the process of comparing two Excel files. Here’s a step-by-step walkthrough:
Initiating the Comparison
-
Launch Spreadsheet Compare. Open the application from your Microsoft Office suite.
-
Select Comparison Options: In the lower-left pane, choose specific elements for comparison (formulas, formatting, macros) or select “Select All” for a comprehensive analysis.
-
Choose Files to Compare: On the “Home” tab, click “Compare Files.” In the dialog box, browse and select the older version of your workbook in the “Compare” row. You can select files from your computer, network, or even enter a web address for online workbooks.
-
Select the Comparison File: In the “To” row, browse and select the newer version of the workbook you want to compare against the older version. Note: Files with the same name can be compared if they are saved in different locations.
-
Execute the Comparison: Click “OK” to begin the comparison process. If a workbook is password protected, enter the password when prompted.
Interpreting the Results
The comparison results are displayed in a two-pane grid. The left pane shows the “Compare” file (older version), and the right pane displays the “To” file (newer version). A detailed list of changes appears below the grids. Color-coded highlights indicate the type of change.
Understanding the Color-Coded Differences
Spreadsheet Compare uses specific colors to highlight different types of changes:
-
Green: Indicates entered values (non-formula cells) that are different. This highlight appears as a green fill in the grid and green font in the results list.
-
Other Colors: The lower-left pane provides a legend explaining the meaning of each color, allowing for clear identification of various modifications.
Navigating and Utilizing Comparison Results
Spreadsheet Compare offers various features to enhance analysis and reporting:
Adjusting Cell Width
If cell contents are not fully visible, click “Resize Cells to Fit” to adjust the column width.
Exporting and Copying Results
-
Export to Excel: Export the comparison results to a user-friendly Excel file by clicking “Home > Export Results.”
-
Copy to Clipboard: Copy the results to the clipboard (“Home > Copy Results to Clipboard”) for pasting into other applications like Microsoft Word.
Viewing Workbook Colors
To view the original cell formatting, click “Home > Show Workbook Colors.” This provides a more visually accurate representation of the changes within the context of the original formatting.
Advanced Applications of Spreadsheet Compare
Beyond simple comparisons, Spreadsheet Compare offers valuable functionalities for:
Audit Trails
Create an audit trail for critical workbooks by tracking changes over time (monthly or yearly). This helps in identifying and rectifying errors before audits.
VBA Code Comparison
Compare differences in VBA (Visual Basic for Applications) code between two workbooks. Results are displayed side-by-side for detailed analysis.
By leveraging the features of Spreadsheet Compare, users can efficiently identify, analyze, and manage changes within Excel workbooks, ensuring data accuracy and facilitating collaborative workflows.
