It’s a common scenario: you share an Excel workbook, and changes are made. But who made them, and what exactly changed? Manually going through spreadsheets to spot differences can be time-consuming and prone to error. Fortunately, Microsoft Excel offers a built-in tool called Spreadsheet Compare, designed to streamline the process of Data Compare Excel files and highlight modifications efficiently.
Important: Spreadsheet Compare is a powerful feature included in Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. Ensure you have one of these versions to access this functionality for your excel data compare needs.
Step-by-Step Guide to Comparing Data in Excel
Let’s walk through how to use Spreadsheet Compare to effectively compare data excel workbooks:
-
Launch Spreadsheet Compare. Locate and open the Spreadsheet Compare application on your computer.
-
Select Comparison Options. In the lower-left corner, you’ll find a pane with various options. Choose what aspects of the workbooks you want to compare. This can include formulas, cell formatting, macros, and more. For a comprehensive data compare excel analysis, you can simply click Select All.
-
Initiate File Comparison. Navigate to the Home tab within Spreadsheet Compare and click on Compare Files.
-
Choose Files for Comparison. A Compare Files dialog box will appear.
-
In the Compare row, click Browse to select the earlier version of your Excel workbook. You can choose files from your local machine, network drives, or even enter a web address if your workbooks are stored online.
-
In the To row, click Browse to select the revised version of the workbook that you want to compare against the earlier one.
-
Note: You can effectively data compare excel files even if they have the same name but are saved in different folders.
-
-
Run the Comparison. Click OK to start the excel data compare process.
Note: If you encounter an “Unable to open workbook” message, it’s likely that one or both workbooks are password protected. Click OK and enter the password(s) when prompted. For more details on password handling, refer to how passwords and Spreadsheet Compare work together.
Understanding Excel Compare Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid.
- The left pane shows the workbook you selected as the “Compare” file (the earlier version).
- The right pane displays the “To” file (the revised version).
- A detailed pane below the grids provides specific information about the detected changes.
Changes are visually highlighted with different colors, making it easy to identify the type of modification.
- Spreadsheet Compare compares worksheets side-by-side, starting from the leftmost sheet in each workbook. Even hidden worksheets are included in the data compare excel analysis.
- If cell content is truncated due to narrow columns, click Resize Cells to Fit to expand column widths for better readability during your excel data compare.
- Color-coding is used to indicate the nature of the changes. For instance, cells containing “entered values” (non-formula cells) are typically highlighted with a green fill in the grid and a green font in the results list. The legend in the lower-left pane explains the color codes used in the data compare excel output.
Leveraging Excel Data Compare Results
Spreadsheet Compare offers several options to work with the comparison results beyond the visual display:
-
Export Results to Excel: For easier sharing or further analysis, export the comparison results to a user-friendly Excel file. Click Home > Export Results.
-
Copy Results to Clipboard: To incorporate the results into other documents or programs like Microsoft Word, click Home > Copy Results to Clipboard. This allows you to paste the data compare excel findings elsewhere.
-
Show Workbook Colors: To view the worksheets with their original cell formatting as they appear in Excel, click Home > Show Workbook Colors. This provides a high-fidelity representation of the compared workbooks.
Why Use Excel Data Compare?
Beyond simply tracking changes, data compare excel functionality with Spreadsheet Compare offers significant benefits in various scenarios:
-
Audit Trails: For organizations facing audits, Spreadsheet Compare is invaluable for creating audit trails of critical workbooks. It allows you to track changes month-over-month or year-over-year, helping identify and rectify errors proactively before audits.
-
Error Detection and Correction: By quickly highlighting discrepancies, Spreadsheet Compare aids in identifying and correcting errors introduced during workbook revisions. This is crucial for maintaining data integrity and accuracy.
-
VBA Code Comparison: Spreadsheet Compare isn’t limited to worksheet data; it can also compare data excel VBA (Visual Basic for Applications) code. The results are presented in a side-by-side window, enabling you to pinpoint differences in your macros and code modules.
In conclusion, Excel Spreadsheet Compare is a robust tool for anyone needing to effectively data compare excel workbooks, track changes, and maintain data accuracy. Its features are particularly useful for collaborative environments, audit preparation, and ensuring the integrity of important spreadsheets.
