In today’s data-driven world, working with Excel spreadsheets is a common practice across various industries. Often, you might find yourself needing to compare two Excel workbooks or different versions of the same file to track changes, identify discrepancies, or ensure data integrity. Microsoft Spreadsheet Compare, a powerful tool available with specific Microsoft Office suites, is designed precisely for this purpose. This guide will walk you through how to effectively use Spreadsheet Compare to analyze differences between Excel files and streamline your data comparison process.
Understanding Spreadsheet Compare
Spreadsheet Compare is a utility within Microsoft Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, and Microsoft 365 Apps for enterprise. This tool allows you to generate detailed reports highlighting the distinctions between two Excel workbooks. It’s invaluable for tasks like auditing changes, pinpointing formula errors, or verifying manually entered data against calculated values.
Note: It’s important to ensure you have one of the compatible Microsoft Office versions to access Spreadsheet Compare.
Accessing Spreadsheet Compare
Opening Spreadsheet Compare is straightforward.
- From the Start Menu: Navigate to your Windows Start menu and locate “Spreadsheet Compare”. You can quickly find it by typing “Spreadsheet Compare” directly in the search bar.
- Launch the Application: Click on the “Spreadsheet Compare” application to open it.
Alongside Spreadsheet Compare, you may also find “Microsoft Database Compare” for Access databases, which is part of the same suite of tools for professional users.
Step-by-Step Guide to Comparing Two Excel Workbooks
Let’s delve into the process of comparing two Excel files using Spreadsheet Compare.
-
Initiate File Comparison: Once Spreadsheet Compare is open, click on the “Home” tab, and then select “Compare Files.” This action will launch the “Compare Files” dialog box.
-
Select the Baseline File: In the “Compare Files” dialog, you’ll see a “Compare” box. Click the blue folder icon adjacent to this box. This prompts you to browse and select the earlier version of your Excel workbook, which will serve as the basis for comparison. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Choose the File to Compare To: Next, locate the “To” box and click the green folder icon next to it. Browse to and select the Excel workbook you wish to compare against the earlier version. Click “OK” to confirm your selection.
Tip: You can effectively compare two files even if they share the same name, provided they are saved in different folders.
-
Customize Comparison Options: The left pane of the “Compare Files” dialog presents various options to refine your comparison. You can choose specific elements to focus on, such as “Formulas,” “Macros,” or “Cell Format,” by checking or unchecking the corresponding boxes. For a comprehensive comparison, you can simply click “Select All.”
-
Execute the Comparison: Finally, click “OK” to initiate the Excel file comparison. Spreadsheet Compare will then analyze the selected files based on your chosen options.
-
Handling Password-Protected Workbooks: If a workbook is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click “OK” and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can handle password-protected files, allowing you to proceed with the comparison after authentication.
Interpreting the Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
- Side-by-Side View: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). A details pane below the grids provides further information about the identified differences.
- Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. It’s important to note that even hidden worksheets are included in the comparison and displayed in the results.
- Color-Coded Differences: Spreadsheet Compare uses color highlighting to visually represent the types of differences between the files. Cell fill colors and text font colors are employed to indicate changes. For instance, cells containing “entered values” (non-formula cells) that have been modified are often highlighted with a green fill in the side-by-side grid and a green font in the results list pane. A legend in the lower-left pane clearly explains the meaning of each color code.
In the example above, the green fill in cells E2:E5 signifies changed entered values in column E for Quarter 4 (Q4). Consequently, the calculated “Year-to-Date” (YTD) results in column F, cells F2:F4 and E6:F6, are highlighted in blue-green, indicating changes in calculated values due to the updated Q4 figures. Furthermore, cell F5 demonstrates a formula correction in the newer version, now correctly summing B5:E5 instead of the previous incorrect range.
- Resize for Clarity: If cell contents are truncated due to narrow column widths, click “Resize Cells to Fit” to automatically adjust column widths for better readability.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the “Inquire” add-in. Activating this add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you gain access to tools for workbook analysis, visualizing relationships between cells and worksheets, and streamlining worksheet formatting. While Spreadsheet Compare is a standalone application, the Inquire add-in provides complementary features directly within Excel. Notably, you can launch Spreadsheet Compare directly from Excel using the “Compare Files” command within the Inquire add-in if you have two workbooks open.
If the Inquire tab is not visible in your Excel, you can easily enable it through Excel’s options.
Conclusion: Enhance Your Excel Workflow with Spreadsheet Compare
Microsoft Spreadsheet Compare is a valuable asset for anyone working extensively with Excel files. By providing a clear and detailed comparison of workbooks, it helps ensure data accuracy, simplifies auditing processes, and aids in identifying and rectifying errors efficiently. Whether you are tracking financial data, managing project plans, or maintaining critical spreadsheets, mastering Spreadsheet Compare can significantly enhance your workflow and data management practices. For organizations relying on Excel and Access for crucial operations, exploring Microsoft’s comprehensive management tools like Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server can further strengthen data governance and risk mitigation.
