Compare 2 Excel Worksheets for Differences: A Comprehensive Guide

Comparing two Excel worksheets to identify differences is a common task for professionals across various fields. Whether you’re auditing financial data, tracking changes in project plans, or ensuring data integrity, knowing how to effectively compare Excel sheets is crucial. Microsoft Excel offers a built-in tool called Spreadsheet Compare (part of the Inquire add-in) that can help you pinpoint discrepancies between two workbooks or different versions of the same workbook. This guide will walk you through how to use Spreadsheet Compare and explore other methods for identifying differences, ensuring you can confidently manage and analyze your data.

Understanding Spreadsheet Compare

Spreadsheet Compare is a powerful utility designed to highlight the differences between two Excel files. It’s particularly useful when you need to:

Identify changes: See exactly what has been modified between different versions of a workbook.
Audit data: Verify the accuracy of data entry and calculations across multiple sheets.
Troubleshoot errors: Find broken formulas or inconsistencies in your spreadsheets.
Ensure consistency: Make sure different departments or teams are working with the same data standards.

Availability: It’s important to note that Spreadsheet Compare is not available in all versions of Excel. It is included with:

Office Professional Plus 2013
Office Professional Plus 2016
Office Professional Plus 2019
Microsoft 365 Apps for enterprise

If you have one of these versions, you can access Spreadsheet Compare as a standalone application.

How to Open Spreadsheet Compare

To start using Spreadsheet Compare, follow these simple steps:

Access the Start Menu: Click the Windows Start button.
Search for Spreadsheet Compare: Type “Spreadsheet Compare” in the search bar.
Open the Application: Click on the Spreadsheet Compare application icon when it appears in the search results.

If you can’t find it directly, ensure you have one of the compatible Office versions installed. If it’s still missing, you may need to install the Inquire Add-in within Excel first, although Spreadsheet Compare itself should be available as a separate application with the specified Office Pro Plus versions.

Step-by-Step Guide: Comparing Two Excel Workbooks

Once you have Spreadsheet Compare open, comparing two Excel workbooks is straightforward:

Initiate File Comparison: Click Home > Compare Files. This will open the “Compare Files” dialog box.
Select the Original Workbook: In the “Compare Files” dialog box, locate the Compare box. Click the blue folder icon next to it to browse your files. Choose the earlier version or the first workbook you want to compare. You can select files from your computer, network locations, or even enter a web address if your workbooks are saved online.
Select the Revised Workbook: Next, find the To box and click the green folder icon beside it. Browse to and select the second workbook you want to compare against the first one (typically the newer version). Click OK after selecting the file.

Tip: You can easily compare two files with identical names as long as they are stored in different folders. This is useful when comparing versions saved in different project directories.
Choose Comparison Options: In the left pane of the “Compare Files” dialog, you’ll see a list of options to refine your comparison. You can select or deselect options like Formulas, Macros, Cell Format, and others to specify what aspects of the workbooks you want to compare. For a comprehensive comparison, you can simply click Select All.
Run the Comparison: Click OK to start the comparison process. Spreadsheet Compare will analyze both workbooks based on your selected options.

Password Protected Workbooks: If either of the workbooks is password-protected, you might receive an “Unable to open workbook” message. If this happens, click OK and enter the password for the protected workbook when prompted. Spreadsheet Compare can handle password-protected files, ensuring you can still compare sensitive data securely.

Interpreting the Comparison Results

After running the comparison, Spreadsheet Compare displays the results in a clear, two-pane grid.

Side-by-Side View: The left pane shows the “Compare” workbook (typically the older file), and the right pane displays the “To” workbook (usually the newer one).
Worksheet Navigation: If your workbooks contain multiple worksheets, you can navigate through them using the forward and back buttons on the horizontal scroll bar located above the grids. Even hidden worksheets are included in the comparison and displayed in the results.
Color-Coded Differences: Differences between the workbooks are highlighted with distinct colors in both the grid and a results list below. The color coding helps you quickly identify the type of change:
- Green Fill: Indicates “entered values” (cells without formulas) that have been changed.
- Green Font: Also represents changed “entered values” but specifically in the results list pane.
- Blue-Green Fill: Signifies “calculated values” (cells with formulas) that have changed as a result of other modifications.
A legend in the lower-left pane provides a detailed explanation of what each color represents, making it easy to understand the types of differences found.

Example Scenario: Imagine you’re comparing quarterly sales reports. The earlier version might have preliminary Q4 figures, while the latest version contains finalized numbers. Spreadsheet Compare would highlight:

Cells in the Q4 columns with a green fill, indicating changed entered values.
Cells in the “Year-to-Date” (YTD) column with a blue-green fill, showing that calculated totals have been updated due to the changes in Q4 figures.
Formula corrections, if any, would also be highlighted, such as a cell where a SUM formula was updated to include a previously missed column.

Resizing Cells: If cell contents are truncated due to narrow columns, you can click Resize Cells to Fit to automatically adjust column widths and view the full content. This ensures you don’t miss any details in the comparison.

Beyond Spreadsheet Compare: Other Comparison Methods

While Spreadsheet Compare is a robust tool, Excel offers other built-in features and techniques for comparing worksheets, especially if you don’t have access to Spreadsheet Compare or need simpler comparisons:

Side-by-Side View in Excel: Excel’s “View Side by Side” feature allows you to manually compare two worksheets or workbooks visually. Go to the View tab and click View Side by Side. This arranges two open workbooks on your screen for direct visual comparison. Synchronous scrolling is also available to scroll both sheets simultaneously.
Conditional Formatting: Use conditional formatting to highlight differences within a single worksheet or between two worksheets in the same workbook. For example, you can use a formula in conditional formatting to compare cell values in two columns and highlight cells where they differ.
Formulas for Cell-by-Cell Comparison: Employ Excel formulas like EXACT or simple equality operators (=) to compare cell values directly within your worksheets.
- =EXACT(A1,Sheet2!A1): This formula checks if the content of cell A1 in the current sheet is exactly the same as cell A1 in “Sheet2,” considering case sensitivity.
- =A1=Sheet2!A1: This formula checks if the values of cell A1 in the current sheet and cell A1 in “Sheet2” are equal.
Online Comparison Tools: Numerous third-party online tools and Excel add-ins are available that offer worksheet comparison features. These can be useful if you need to compare files without installing additional software or if you require features beyond Spreadsheet Compare.

Leveraging Excel’s Inquire Add-in

Spreadsheet Compare is closely related to Excel’s Inquire add-in. If you are using Excel 2013 or later (and have a compatible Office version), the Inquire add-in provides a dedicated “Inquire” tab in Excel. This tab offers various tools for workbook analysis, including:

Workbook Analysis: Provides a comprehensive report on workbook structure, formulas, errors, and hidden information.
Relationship Diagram: Visually maps the relationships between cells, worksheets, and workbooks, helping you understand data dependencies.
Clean Excess Cell Formatting: Removes unnecessary formatting that can increase file size and slow down performance.
Compare Files (within Excel): Although Spreadsheet Compare is a separate application, the Inquire tab in Excel also includes a “Compare Files” command that launches Spreadsheet Compare, providing integrated access to the comparison tool.

If you don’t see the Inquire tab in your Excel ribbon, you may need to activate it. Go to File > Options > Add-Ins. In the “Manage” dropdown at the bottom, select “COM Add-ins” and click “Go…”. Check the box next to “Inquire” and click “OK” to enable the add-in.

Next Steps for Advanced Excel Management

For organizations that rely heavily on Excel for critical operations, consider implementing robust spreadsheet management solutions. Microsoft offers tools like:

Microsoft Audit and Control Management Server: Provides advanced change management and auditing capabilities for Excel and Access files, enhancing control and compliance.
Microsoft Discovery and Risk Assessment Server: Helps organizations inventory, analyze, and assess risks associated with user-developed Excel and Access tools, improving data governance and reducing potential errors.

These server solutions are designed to complement Spreadsheet Compare and the Inquire add-in, offering enterprise-grade features for managing and securing mission-critical spreadsheets.

Further Resources:

Overview of Spreadsheet Compare: https://o15.officeredir.microsoft.com/r/rlidssc15 (Official Microsoft documentation link for more details on Spreadsheet Compare).

By mastering Spreadsheet Compare and exploring other comparison techniques, you can significantly enhance your ability to manage, audit, and ensure the accuracy of your Excel data, leading to more informed decisions and improved data integrity.