In today’s data-driven world, Microsoft Excel remains an indispensable tool for managing and analyzing information. Whether you’re tracking budgets, managing inventory, or analyzing sales figures, Excel spreadsheets are at the heart of many operations. Often, you might find yourself working with multiple versions of the same spreadsheet, or comparing data across different workbooks. Identifying the differences between these files can be a crucial, yet time-consuming task. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare, designed specifically to highlight discrepancies between Excel files. This guide will walk you through how to effectively use Spreadsheet Compare to pinpoint exactly what has changed between two Excel spreadsheets, ensuring data accuracy and saving you valuable time.
Utilizing Spreadsheet Compare to Identify Discrepancies
Spreadsheet Compare is a utility designed to meticulously analyze and report on the differences it detects between two Excel workbooks. This can be invaluable for tasks such as auditing changes, verifying data integrity, or troubleshooting formula errors. It’s particularly useful when you need to understand what modifications have been made between different versions of a workbook, or when you suspect inconsistencies between similar spreadsheets.
Important Note: It’s crucial to note that Spreadsheet Compare is not included in all versions of Microsoft Office. It is specifically available with Office Professional Plus 2013, Office Professional Plus 2016, Office Professional Plus 2019, or Microsoft 365 Apps for enterprise. If you are using a standard version of Office, you may need to explore alternative methods or consider upgrading to a version that includes this feature.
Accessing Spreadsheet Compare
The first step is to locate and open the Spreadsheet Compare application. Here’s how you can do it:
- Navigate to the Start Menu in Windows.
- Look for Spreadsheet Compare in your list of applications. If you don’t immediately see it, start typing “Spreadsheet Compare”.
- Select Spreadsheet Compare from the search results to launch the application.
Once opened, you’ll notice a clean and intuitive interface designed for comparing files. Alongside Spreadsheet Compare, you might also find Microsoft 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 Files
Comparing two Excel workbooks for differences using Spreadsheet Compare is a straightforward process:
-
Initiate the Comparison: Click on the Home tab, and then select Compare Files. This action will open the Compare Files dialog box, where you will specify the files you wish to compare.
-
Select the Original Workbook: In the Compare Files dialog box, locate the Compare box. Click on the blue folder icon next to it. This will open a browse window, allowing you to navigate to and select the earlier version of your Excel workbook. You can choose files stored locally on your computer, on a network drive, or even input a web address if your workbooks are saved online.
-
Choose the Revised Workbook: Next, find the To box and click on the green folder icon adjacent to it. Browse to and select the workbook you want to compare against the earlier version. This is typically the more recent or modified version of the file. Click OK after selecting the file.
Tip: Spreadsheet Compare allows you to compare two files even if they share the same name, as long as they are saved in different folders. This is useful when you have versioned files saved in separate directories.
-
Specify Comparison Options: In the left pane of the Compare Files dialog box, you’ll find a list of options to customize your comparison. You can choose to focus on specific elements such as Formulas, Macros, Cell Format, and more. Select the checkboxes next to the aspects you want to include in the comparison. If you want to compare everything, simply click Select All.
-
Execute the Comparison: Once you have selected your files and comparison options, click OK to start the comparison process. Spreadsheet Compare will analyze the two workbooks based on your chosen settings.
Password Protected Workbooks: If either of the 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 for the protected workbook. Spreadsheet Compare can work with password-protected files, but you will need to provide the password to access their content. Microsoft provides further details on managing passwords with Spreadsheet Compare.
Deciphering the Comparison Results
After running the comparison, Spreadsheet Compare presents the results in a user-friendly, two-pane grid.
-
Side-by-Side View: The left pane displays the workbook you selected as the “Compare” file (typically the older version), while the right pane shows the “To” file (usually the newer one). Worksheets within each workbook are compared correspondingly. If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar.
Note: Even worksheets that are hidden in the original workbooks are included in the comparison and displayed in the results.
-
Color-Coded Differences: Spreadsheet Compare uses a color-coding system to highlight the types of differences it finds. Changes are indicated by cell fill colors or text font colors, depending on the nature of the modification. For instance, cells containing “entered values” (cells with manually typed data, not formulas) that have been altered are typically highlighted with a green fill color in the grid and a green font in the results list pane. A legend in the lower-left pane clearly explains what each color represents.
Example Interpretation: Consider a scenario where you are comparing sales reports for two quarters. In the earlier version, the sales figures for Q4 were preliminary. The updated workbook contains the final Q4 numbers. In the comparison results:
-
Cells in the columns containing the Q4 figures (e.g., E2:E5 in both versions) might have a green fill, indicating that an entered value has changed.
-
Consequently, cells with formulas that depend on these changed values (e.g., totals or year-to-date calculations in columns F2:F4 and E6:F6) might be highlighted with a blue-green fill, signifying that a calculated value has changed as a result of other modifications.
-
You might even discover formula corrections. For example, a cell like F5 might show a change not just in value but also in the underlying formula, indicating that a previous error has been corrected in the updated version.
-
Adjusting Cell Display: If cell contents are truncated due to column width, you can click Resize Cells to Fit to automatically adjust column widths and ensure you can view the complete cell information.
Exploring Excel’s Inquire Add-in for Advanced Analysis
Beyond Spreadsheet Compare, Excel 2013 and later versions offer another powerful tool: the Inquire add-in. This add-in, once activated, introduces an “Inquire” tab in Excel, providing a suite of analytical capabilities. While Spreadsheet Compare is excellent for direct file comparison, the Inquire add-in offers broader workbook analysis tools.
Overview of the Inquire Add-in
The Inquire add-in allows you to:
- Analyze Workbook Structure: Gain insights into the relationships between cells, worksheets, and even links to other workbooks.
- Visualize Workbook Dependencies: Create diagrams that map out formula dependencies and data flow within your spreadsheets.
- Clean Excess Formatting: Identify and remove unnecessary formatting that can bloat file size and potentially cause performance issues.
If you have two workbooks open directly in Excel and wish to compare them, you can still initiate Spreadsheet Compare functionality through the Inquire add-in using the Compare Files command, providing a convenient alternative access point.
Enabling the Inquire Add-in
If you don’t see the Inquire tab in your Excel ribbon, you may need to enable it. Here’s how:
- Go to File > Options > Add-ins.
- In the Manage dropdown at the bottom of the dialog box, select COM Add-ins and click Go.
- In the COM Add-Ins dialog, check the box next to Inquire and click OK.
The Inquire tab should now appear in your Excel ribbon, providing access to its analytical tools. For a deeper dive into the capabilities of the Inquire add-in, Microsoft provides comprehensive documentation on what you can do with Spreadsheet Inquire.
Taking the Next Step
For organizations heavily reliant on Excel workbooks and Access databases for critical operations, investing in Microsoft’s management and auditing tools can be highly beneficial. Microsoft Audit and Control Management Server offers robust change management features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and analysis features designed to help mitigate risks associated with user-developed tools in Excel and Access environments. These enterprise-level solutions offer a more comprehensive approach to managing and controlling spreadsheet and database assets within an organization.
In conclusion, whether you are a casual Excel user or manage complex business spreadsheets, understanding how to compare Excel spreadsheets for differences is a valuable skill. Spreadsheet Compare, along with the Inquire add-in, provides powerful tools to ensure data accuracy, streamline auditing processes, and enhance your overall efficiency when working with Excel files.
