Comparing two Excel files to identify differences can be a time-consuming and error-prone task if done manually. Whether you’re tracking changes across workbook versions, auditing data entries, or ensuring formula consistency, pinpointing discrepancies is crucial for data integrity and accuracy. Microsoft Spreadsheet Compare is a powerful tool designed to streamline this process, offering a detailed report of the variations between two Excel workbooks. This article will guide you on how to effectively use Spreadsheet Compare to highlight and understand the differences between your Excel files.
Understanding Spreadsheet Compare
Spreadsheet Compare is a utility within Microsoft Office that’s specifically engineered to compare Excel workbooks. It generates a comprehensive report detailing various types of differences, from content changes and formula modifications to formatting discrepancies and macro variations. This tool is invaluable for anyone needing to meticulously review changes or audit Excel files.
Important Note: It’s crucial to note that Spreadsheet Compare is not included in all versions of Microsoft Office. It is available in:
- Office Professional Plus 2013
- Office Professional Plus 2016
- Office Professional Plus 2019
- Microsoft 365 Apps for enterprise
If you are using a different version of Office, you may not have access to this tool.
Launching Spreadsheet Compare
Accessing Spreadsheet Compare is straightforward. Here’s how to open it:
- Go to the Start Menu in Windows.
- Look for Spreadsheet Compare. You can scroll through your app list or simply start typing “Spreadsheet Compare” to search for it.
- Click on Spreadsheet Compare to launch the application.
Once opened, you’ll notice a clean interface ready to compare your Excel files. Alongside Spreadsheet Compare, you might also find Database Compare, a similar tool for Access databases, which is also part of the Office Professional Plus suite.
Step-by-Step Guide to Comparing Excel Workbooks
Let’s walk through the process of comparing two Excel workbooks using Spreadsheet Compare:
-
Initiate Comparison: In the Spreadsheet Compare window, navigate to the Home tab and click on Compare Files. This action will open the Compare Files dialog box.
-
Select Files:
- In the Compare box, click the blue folder icon. This prompts a browse window to open, allowing you to locate and select the earlier version of your Excel workbook. You can choose files from your local computer, network drives, or even input a web address if your workbooks are stored online.
- Next, click the green folder icon next to the To box. Browse to and select the more recent version of the Excel workbook that you want to compare against the earlier one. Click OK to confirm your selection.
Tip: Spreadsheet Compare allows you to compare two files even if they share the same name, provided they are saved in different folders. This is particularly useful when comparing versions of the same report saved in different project folders.
-
Choose Comparison Options: In the left-hand pane of the Compare Files dialog, you’ll find a list of options that determine what aspects of the workbooks will be compared. You can select or deselect options like Formulas, Macros, Cell Format, and others based on your comparison needs. To perform a comprehensive comparison, you can simply check Select All.
-
Run the Comparison: Click OK to initiate the comparison process. Spreadsheet Compare will analyze the selected workbooks based on your chosen options.
-
Password Protected Files: If either of the workbooks is password protected, you might encounter an “Unable to open workbook” message. If this occurs, click OK in the message box and you will be prompted to enter the password for the protected workbook. Spreadsheet Compare can handle password-protected files, ensuring you can still compare them securely.
Interpreting the Comparison Results
Once the comparison is complete, the results are displayed in a user-friendly two-pane grid.
-
Side-by-Side Grid: The left pane displays the “Compare” file (typically the older version), and the right pane shows the “To” file (usually the newer version). Each workbook’s worksheets are compared against their counterparts in the other file. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar. Importantly, even hidden worksheets are included in the comparison and results.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences found. Cells with changes are marked with fill colors or font colors depending on the nature of the alteration. For instance, cells containing directly entered values (not formulas) that have been modified are often highlighted with a green fill in the grid and a green font in the detailed results list. A legend in the lower-left pane clearly explains what each color signifies.
Consider an example where a quarterly sales report is updated. If the initial version had preliminary Q4 figures, and the updated version contains finalized Q4 numbers, Spreadsheet Compare will highlight these changes. In the comparison results, cells with updated “entered values” for Q4 will likely have a green fill, indicating a change in a non-formula cell. Consequently, any cells with formulas that depend on these changed values (like year-to-date totals) will also be highlighted, perhaps with a blue-green fill, signifying a change in a calculated value.
In more complex scenarios, formula corrections are also easily spotted. If a formula was incorrect in the earlier version (e.g., missing a range in a SUM formula) and has been fixed in the later version, Spreadsheet Compare will not only highlight the change in the calculated result but also indicate the formula modification itself.
-
Cell Content Visibility: If some cells are too narrow to fully display their contents in the comparison grid, you can click Resize Cells to Fit. This option adjusts the column widths to ensure you can read all cell contents without difficulty.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions offer the Inquire add-in, which provides additional analytical capabilities. Once enabled, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can access tools to:
- Analyze Workbook: Gain insights into workbook structure, formula relationships, and potential errors.
- Show Workbook Relationships: Visualize the connections between cells, worksheets, and even links to external workbooks.
- Clean Excess Cell Formatting: Reduce file size and improve performance by removing unnecessary formatting.
If you have Excel workbooks open and need to quickly compare them using Spreadsheet Compare, you can also access the “Compare Files” command directly from the Inquire tab in Excel, if the add-in is active.
If you don’t see the Inquire tab, you may need to activate it in Excel’s options. Instructions on how to do this can be found in Microsoft’s documentation on Turn on the Inquire add-in. To explore the full range of tools available in the Inquire add-in, refer to What you can do with Spreadsheet Inquire.
Conclusion and Next Steps
Spreadsheet Compare is an indispensable tool for anyone working with Excel workbooks who needs to track changes, ensure accuracy, or audit data. By quickly and clearly highlighting differences between files, it saves significant time and reduces the risk of errors associated with manual comparison.
For organizations that rely heavily on Excel and Access for critical operations, consider exploring Microsoft’s broader suite of management tools. Microsoft Audit and Control Management Server and Microsoft Discovery and Risk Assessment Server offer advanced change management, inventory, and analysis features specifically designed to mitigate risks associated with user-developed tools in Excel and Access.
To get a broader overview of Spreadsheet Compare and its capabilities, you can also refer to the Overview of Spreadsheet Compare provided by Microsoft.
By using Spreadsheet Compare effectively, you can maintain better control over your Excel data, ensure data integrity, and streamline your workflow when dealing with multiple versions of workbooks.
