It’s a common scenario: you share an Excel workbook, and when you get it back, you’re faced with the daunting question, “What exactly has changed?”. Manually going through each cell is simply not feasible. Thankfully, Microsoft Excel offers a built-in tool called Spreadsheet Compare, designed to pinpoint these modifications and present them clearly. This guide will walk you through using Spreadsheet Compare to effectively Compare Spreadsheets In Excel, ensuring you’re always on top of changes made to your important data.
Important Note: Spreadsheet Compare is a powerful utility included with specific Microsoft Office suites, namely 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 feature.
Step-by-Step Guide to Comparing Spreadsheets
Let’s break down the process of comparing spreadsheets in Excel using Spreadsheet Compare:
1. Launch Spreadsheet Compare
The first step is to open the Spreadsheet Compare application. This is typically found outside of Excel itself, within your Microsoft Office tools. Search for “Spreadsheet Compare” in your Windows search bar and open the application.
2. Specify Comparison Options
Once Spreadsheet Compare is open, look at the pane located at the bottom left. Here, you’ll find a list of options that determine what aspects of your workbooks will be compared. These options include:
- Formulas: Compares the formulas used in cells.
- Cell Formatting: Identifies changes in cell appearance like colors, fonts, and styles.
- Macros: Checks for differences in VBA macros.
- And more: Options to compare comments, worksheet names, etc.
You can choose specific options based on your needs or simply click “Select All” to compare every aspect of your spreadsheets.
3. Select the Workbooks for Comparison
Navigate to the “Home” tab within the Spreadsheet Compare ribbon and click on “Compare Files“. This action will open the “Compare Files” dialog box.
In this dialog, you’ll see two rows: “Compare” and “To“.
-
In the “Compare” row, use the Browse button to select the earlier version of your Excel workbook. This is often your original file. You can choose files from your computer, network locations, or even enter a web address if your workbooks are stored online.
-
In the “To” row, use the Browse button to select the later version of your workbook – the one you want to compare against the earlier version.
It’s important to note that you can compare two files with the same name as long as they are saved in different folders.
4. Run the Comparison
Once you have selected both the “Compare” and “To” files, click “OK” in the “Compare Files” dialog box. Spreadsheet Compare will then begin analyzing the two workbooks and identifying the differences based on the options you selected.
Password Protected Workbooks: If either of your workbooks is password protected, you might encounter an “Unable to open workbook” message. If this happens, click “OK” and you will be prompted to enter the password to proceed with the comparison.
Understanding the Comparison Results
After running the comparison, Spreadsheet Compare displays the results in a split-screen grid.
-
Side-by-Side Grid: The left pane shows the workbook you selected as “Compare” (the earlier version), and the right pane displays the “To” workbook (the later version). Each worksheet from both files is compared, starting from the leftmost sheet. Even hidden worksheets are included in the comparison.
-
Detailed Results Pane: Below the side-by-side grid, you’ll find a pane that provides more detail about the changes.
-
Visual Highlighting: Changes are visually highlighted using different colors. The color-coding helps you quickly identify the type of change. For example, cells containing entered values (not formulas) that have been modified are often highlighted with a green fill in the grid and a green font in the results list. A legend in the lower-left pane explains what each color signifies.
If cell content is truncated due to column width, click “Resize Cells to Fit” to expand the columns for better readability.
Additional Ways to Utilize Comparison Results
Spreadsheet Compare provides several options for working with the comparison results beyond just viewing them in the application:
-
Export to Excel: To save the comparison results in an easily readable Excel file, click “Home > Export Results“. This is useful for sharing the findings or for record-keeping.
-
Copy to Clipboard: For incorporating the results into other documents or programs (like Microsoft Word), use “Home > Copy Results to Clipboard“. This allows you to paste the comparison data 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 more visually accurate representation of the spreadsheets.
Why Compare Spreadsheets? Practical Applications
Comparing spreadsheets is not just about tracking changes; it has important real-world applications:
-
Audit Trails: For organizations undergoing audits, Spreadsheet Compare can be invaluable for creating a clear trail of changes in critical workbooks over time. This helps in identifying and rectifying errors proactively before audits.
-
VBA Code Comparison: Beyond worksheet content, Spreadsheet Compare can also identify differences in VBA (Visual Basic for Applications) code within workbooks. The VBA comparison results are displayed in a separate window, showing code changes side-by-side. This is crucial for maintaining and debugging Excel macros.
By leveraging Spreadsheet Compare, you can efficiently compare spreadsheets in Excel, maintain data integrity, track changes effectively, and ensure accuracy in your workbooks. This tool is a powerful asset for anyone working with collaborative Excel files or managing critical spreadsheet data.
