Comparing Excel sheets is a common task for professionals who work with data, spreadsheets, and reporting. Whether you’re auditing financial records, tracking changes in project data, or consolidating information from different sources, identifying discrepancies between Excel files is crucial. Fortunately, Microsoft offers a powerful tool called Spreadsheet Compare to streamline this process. This guide will walk you through how to effectively Compare Excel Sheets and understand the comparison results.
Utilizing Spreadsheet Compare for Excel Sheet Comparison
Microsoft Spreadsheet Compare is a specialized tool designed to highlight the differences between two Excel workbooks or different versions of the same workbook. It’s particularly useful for pinpointing issues like unintentional manual data entries in calculated totals or identifying broken formulas that can compromise data integrity.
Important Note: Spreadsheet Compare is a feature included in 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 tool.
Accessing Spreadsheet Compare
To begin comparing your Excel sheets, you first need to open the Spreadsheet Compare application.
- Navigate to the Start Menu: Click on the Windows “Start” button.
- Find Spreadsheet Compare: Look for “Spreadsheet Compare” in your list of applications. If you don’t see it immediately, start typing “Spreadsheet Compare.”
- Launch the Application: Select “Spreadsheet Compare” from the search results or application list to open the program.
Alongside Spreadsheet Compare, Microsoft provides a similar tool for Access databases called Microsoft Database Compare. These tools are designed to work in tandem for comprehensive data management and comparison within the Microsoft Office ecosystem.
Step-by-Step Guide to Comparing Two Excel Workbooks
Once Spreadsheet Compare is open, follow these steps to compare your Excel sheets:
-
Initiate File Comparison: Click on the “Home” tab, and then select “Compare Files.” This action will open the “Compare Files” dialog box.
-
Select the Original Workbook: In the “Compare Files” dialog box, locate the “Compare” field. Click on the blue folder icon next to it. This allows you to browse your file system and select the earlier version or the first Excel workbook you want to compare. You can choose files from your local computer, network drives, or even enter a web address if your workbooks are stored online.
-
Select the Revised Workbook: Next, find the “To” field and click on the green folder icon adjacent to it. Browse to and select the second Excel workbook, which is typically the more recent version or the one you want to compare against the first. Click “OK” after selecting the file.
Tip: You can easily compare two files that have the same name as long as they are saved in different folders. This is useful for comparing versions stored in different project directories, for example.
-
Choose Comparison Parameters: In the left-hand pane of the “Compare Files” dialog, you’ll see a list of options. These options let you specify what elements of the Excel sheets you want to compare. You can choose to compare “Formulas,” “Macros,” “Cell Format,” and other aspects. For a comprehensive comparison, you can simply click “Select All.”
-
Execute the Comparison: After selecting your desired options, click “OK” to start the comparison process. Spreadsheet Compare will analyze the two Excel workbooks based on your chosen parameters.
Password Protected Workbooks: If you encounter an “Unable to open workbook” message, it’s likely that one or both of the Excel workbooks are password protected. Click “OK” on the message prompt and you will be asked to enter the password for the protected workbook. Spreadsheet Compare requires the password to access and compare the contents.
Interpreting the Comparison Results
Once the comparison is complete, the results are displayed in a clear, two-pane grid. The left pane shows the “Compare” workbook (typically the older file), and the right pane displays the “To” workbook (usually the newer file). A detailed pane below the grids provides a legend and further information about the identified differences.
Changes and discrepancies are visually highlighted using colors. The color-coding helps you quickly understand the type of difference detected.
-
Worksheet Alignment: Each worksheet in the first file is compared against the corresponding worksheet in the second file. If your workbooks contain multiple worksheets, you can navigate through the comparison results using the forward and back buttons located on the horizontal scroll bar.
Note: Even hidden worksheets are included in the comparison and will be displayed in the results. -
Color-Coded Differences: Differences are marked by cell fill colors or text font colors, depending on the nature of the change. For example, cells containing “entered values” (cells with manually typed numbers or text, not formulas) that have been modified are typically highlighted with a green fill color in the side-by-side grid view and a green font in the results list pane. The legend in the lower-left pane clearly explains what each color signifies.
Example Interpretation: Consider a scenario where you are comparing quarterly sales reports. In an earlier version, the Q4 figures were preliminary. The updated version contains the final sales numbers for Q4 in column E.
In the comparison results:
-
Cells E2:E5 in both versions are filled with green, indicating that entered values have changed.
-
Because these values were updated, the calculated year-to-date (YTD) results in column F also changed. Cells F2:F4 and E6:F6 are marked with a blue-green fill, signifying that calculated values have been altered.
-
Cell F5 also shows a change, but importantly, it reveals a formula correction. In the older version, the formula in F5 was incorrectly summing only B5:D5, omitting Q4’s value. In the updated workbook, the formula in F5 was corrected to
=SUM(B5:E5). This highlights not just data changes but also formula errors. -
Adjusting Cell Width: If cell content is truncated due to narrow columns, simply click “Resize Cells to Fit” to expand column widths and view the complete cell contents.
Leveraging Excel’s Inquire Add-in
Beyond Spreadsheet Compare, Excel 2013 and later versions include a valuable add-in called Inquire. Once activated, the Inquire add-in adds an “Inquire” tab to your Excel ribbon. From this tab, you can access a range of analytical tools.
The Inquire add-in allows you to:
- Analyze Workbooks: Gain insights into workbook structure, formula dependencies, and potential errors.
- Visualize Relationships: See the connections between cells, worksheets, and even links to other workbooks.
- Clean Excess Formatting: Remove unnecessary formatting that can bloat file size and hinder performance.
For comparing two currently open Excel workbooks directly within Excel, you can use the “Compare Files” command which will launch Spreadsheet Compare from within Excel.
If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it manually.
Taking the Next Step in Excel Management
For organizations heavily reliant on “mission-critical” Excel workbooks and Access databases, consider implementing Microsoft’s comprehensive management tools. Microsoft Audit and Control Management Server offers advanced change management and auditing features specifically for Excel and Access files. Complementing this, Microsoft Discovery and Risk Assessment Server provides inventory and in-depth analysis capabilities. These server solutions are designed to minimize risks associated with user-developed tools in Excel and Access, ensuring data integrity and process control.
Further explore the capabilities of Spreadsheet Compare to enhance your Excel file management and data accuracy.
