How to Compare Two Excel Worksheets and Highlight Differences

When working with data in Excel, you often find yourself managing multiple worksheets that are similar but not quite identical. Whether you’re tracking changes in reports, consolidating data, or simply ensuring consistency, knowing how to Excel Compare Two Worksheets Highlight Differences is an invaluable skill. This guide will walk you through various methods to effectively compare Excel worksheets and pinpoint discrepancies, ranging from built-in Excel features to specialized third-party tools.

Having the ability to quickly identify differences between two Excel worksheets can save you significant time and reduce errors. It’s crucial for tasks like auditing financial data, verifying data entry, or merging updates from different team members. Let’s explore the best approaches to compare your Excel worksheets and highlight those key variations.

Visually Compare Excel Worksheets Side by Side

For a quick overview, especially with smaller datasets, visually comparing worksheets side by side is a straightforward method. Excel’s “View Side by Side” feature allows you to arrange two worksheet windows adjacent to each other, facilitating a direct visual comparison.

Comparing Two Excel Workbooks Side by Side

Imagine you have monthly sales reports in two separate Excel files and need to quickly see month-over-month performance. The “View Side by Side” feature makes this easy:

Open both Excel workbooks you want to compare.
Navigate to the View tab on the Excel ribbon. In the Window group, click the View Side by Side button.

Instantly, Excel will display the two workbooks in separate windows positioned horizontally by default.

If you prefer a vertical arrangement for easier top-to-bottom comparison, click the Arrange All button in the Window group and select Vertical.

Arranging two Excel windows vertically for side-by-side comparison.

To ensure synchronized scrolling, allowing you to scroll through both worksheets simultaneously for row-by-row inspection, verify that the Synchronous Scrolling option is enabled. This is typically activated automatically when you use “View Side by Side” and is located directly below the “View Side by Side” button in the Window group on the View tab.

Synchronous scrolling in Excel for row-by-row worksheet comparison.

For a deeper dive into this feature, Microsoft Support offers a comprehensive guide on viewing Excel workbooks side by side.

Arranging Multiple Excel Windows for Comparison

When dealing with more than two Excel files, you can still use the “View Side by Side” feature. After opening all the workbooks, clicking “View Side by Side” will open the “Compare Side by Side” dialog box. This allows you to select which files you want to display alongside the currently active workbook.

Arranging multiple Excel windows side by side for simultaneous viewing in Excel.

Alternatively, to view all currently open Excel files at once, use the Arrange All button in the Window group on the View tab. You can then choose from tiled, horizontal, vertical, or cascade arrangements to best suit your viewing needs.

Comparing Two Worksheets Within the Same Workbook

Sometimes, the two worksheets you need to compare reside within the same Excel workbook. To view these sheets side by side:

Open the Excel file. Go to the View tab and in the Window group, click New Window.
This action opens a new window displaying the same Excel file.
Click the View Side by Side button to activate side-by-side mode.
In each window, navigate to the specific worksheet you want to compare – select Sheet1 in one window and Sheet2 in the other.

This method allows you to directly compare two worksheets from the same workbook in a side-by-side view.

Creating a Difference Report Using Excel Formulas

For a more analytical approach, you can use Excel formulas to create a difference report. This method highlights cells with different values in a new worksheet, providing a clear record of discrepancies.

To compare two Excel worksheets for value differences, follow these steps:

Open a new, blank worksheet in your Excel workbook.
In cell A1 of the new sheet, enter the following formula:
=IF(Sheet1!A1<>Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")
Drag the fill handle (the small square at the bottom-right of cell A1) to copy this formula down and across the range covering your data in Sheet1 and Sheet2.

This formula leverages relative cell references to dynamically compare corresponding cells in Sheet1 and Sheet2. If the values in the compared cells are different, the formula will display the values from both sheets in the report; otherwise, it will leave the cell blank.

The resulting difference report will look similar to this:

Comparing two Excel sheets for value differences using an Excel formula.

Note that while this method effectively identifies value differences, it may present dates as serial numbers in the difference report, which might be less intuitive for analysis.

Highlighting Differences with Conditional Formatting

A visually impactful way to excel compare two worksheets highlight differences is by using conditional formatting. This technique automatically highlights cells that differ between two worksheets directly within one of the worksheets.

Here’s how to highlight differing cells using conditional formatting:

Select the range of cells you want to compare in the worksheet where you want the differences to be highlighted. Start by clicking the top-left cell of your data range (usually A1) and press Ctrl + Shift + End to select all used cells.
On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
In the “New Formatting Rule” dialog box, select “Use a formula to determine which cells to format”.
Enter the following formula in the “Format values where this formula is true” box: =A1<>Sheet2!A1
Remember to replace “Sheet2” with the actual name of the worksheet you are comparing against.
Click the Format button to choose your desired highlighting style (e.g., fill color).
Click OK in both dialog boxes to apply the conditional formatting rule.

This conditional formatting rule will compare each cell in your selected range against the corresponding cell in “Sheet2”. If the values are different, the cell in the first worksheet will be highlighted with the formatting style you selected.

Creating a conditional formatting rule to highlight differences between two Excel sheets.

For a more detailed guide on conditional formatting based on another cell’s value, refer to this tutorial on Excel conditional formatting formulas.

While formulas and conditional formatting are useful for basic comparisons, they have limitations:

They primarily compare cell values and don’t account for formula or formatting differences.
They struggle with added or deleted rows/columns, often misidentifying subsequent rows/columns as differences.
They operate at the worksheet level and cannot detect workbook-level structural changes like added or removed sheets.

Comparing and Merging Shared Workbook Copies

For collaborative environments where multiple users might be working on different copies of a shared Excel workbook, Excel’s “Compare and Merge” feature is designed to consolidate changes. This feature is especially useful when tracking and integrating edits from various contributors.

Before using “Compare and Merge,” ensure these prerequisites are met:

The workbook must be shared. To share a workbook, click Share Workbook in the Changes group on the Review tab, check “Allow changes by more than one user…”, and click OK. Save the workbook if prompted. Enabling “Track Changes” automatically shares the workbook.
Each user must save their edited copy of the shared workbook with a unique filename (.xls or .xlsx format).

Once these steps are completed, you can proceed to merge the copies:

Enabling the “Compare and Merge Workbooks” Feature

Although available in Excel 2010 through Excel 365, the “Compare and Merge Workbooks” command is not visible by default. To add it to your Quick Access Toolbar:

Click the dropdown arrow on the Quick Access Toolbar and select More Commands.
In the “Excel Options” dialog, choose All Commands from the “Choose commands from” dropdown.
Scroll down the command list to find Compare and Merge Workbooks, select it, and click Add to move it to the right-hand section.
Click OK.

Adding the Compare and Merge Workbooks feature to Excel’s Quick Access Toolbar.

Merging Workbooks

With the command added, you can now merge the copies:

Open the original, shared workbook.
Click the Compare and Merge Workbooks command on the Quick Access Toolbar.
In the dialog box, select the copies of the shared workbook you want to merge. You can select multiple copies by holding the Shift key while clicking filenames. Click OK.

Selecting a copy of a shared Excel workbook for merging changes.

Excel will then merge the changes from each selected copy into the primary workbook.

Reviewing Merged Changes

To review the changes made by different users:

Go to the Review tab, Changes group, and click Track Changes > Highlight Changes.
In the “Highlight Changes” dialog, set “When” to “All,” “Who” to “Everyone,” clear the “Where” box, check “Highlight changes on screen,” and click OK.

Highlighting changes made by different users in a merged Excel workbook.

Excel will highlight columns and rows with changes in red. Cell-level edits from different users will be marked with unique colors. Hovering over a changed cell will display who made the edit.

Note that “Compare and Merge Workbooks” only works for copies of the same shared workbook. It cannot merge different Excel files.

Third-Party Tools for Advanced Excel Comparison

For more comprehensive and efficient Excel comparison, especially when needing to compare values, formulas, and formatting, third-party tools offer advanced capabilities beyond Excel’s built-in features. These tools are often essential for detailed analysis and merging complex Excel files.

Synkronizer Excel Compare: A Comprehensive Tool for Comparison, Merge, and Update

Synkronizer Excel Compare is a powerful add-in designed to compare, merge, and update Excel files efficiently. It saves users from manual difference hunting by offering features like:

Identifying differences between Excel worksheets.
Merging multiple Excel files into a single version without duplicates.
Highlighting differences directly in the worksheets.
Filtering differences to focus on relevant changes.
Merging and updating worksheets selectively.
Generating detailed and readable difference reports.

To illustrate Synkronizer’s capabilities, let’s consider an example of comparing two versions of an event participant list.

To start using Synkronizer Excel Compare:

Go to the Add-ins tab in Excel and click the Synchronizer 11 icon.

Running the Synkronizer Excel Compare add-in in Excel.

The Synkronizer pane will appear on the left side of your Excel window.

Select the two workbooks you want to compare.

Selecting two Excel workbooks to compare using Synkronizer.

Choose the specific worksheets to compare. If workbooks have sheets with identical names, they are automatically matched and selected for comparison, like the “Participants” sheets in the example below. You can also manually select worksheets or match them based on criteria like worksheet type (all, protected, hidden).

Selecting specific worksheets for comparison in Synkronizer.

Once worksheets are selected, Synkronizer can display them side by side, either vertically or horizontally, similar to Excel’s “View Side by Side” mode.

Choose a comparison option:
- Compare as normal worksheets: Suitable for most standard comparisons.
- Compare with link options: For worksheets without added/deleted rows/columns, enabling “1 on 1” comparison.
- Compare as database: Recommended for worksheets with a database structure.
- Compare selected ranges: To compare only specific ranges instead of entire worksheets.
Optionally, select specific content types to compare on the Select tab in the Compare group. You can choose to include comments and names in addition to default comparison of cell values, formulas, and calculated values. Under Formats, you can specify cell formats like alignment, fill, font, and border to be compared. Filters allow you to ignore differences like case, spaces, formulas with same results, and hidden rows or columns.
Click the large red Start button to initiate the comparison and view results.

Visualizing and Analyzing Differences with Synkronizer

Synkronizer quickly compares worksheets and presents results in two summary reports on the Results tab:

A summary report provides an overview of all difference types: column, row, cell, comment, format, and name changes.
A detailed difference report is accessible by clicking on a specific difference type in the summary report, providing granular information.

Summary and detailed difference reports generated by Synkronizer Excel Compare.

Clicking on a difference in the detailed report highlights the corresponding cells in both worksheets, facilitating direct inspection.

Navigating to specific cell differences in compared worksheets using Synkronizer.

Synkronizer can also generate a difference report in a separate workbook, offering both standard and hyperlinked options for easy navigation to differences.

Detailed difference report in a separate Excel workbook created by Synkronizer.

Comparing All Worksheets Simultaneously

If you are comparing two multi-sheet Excel files, Synkronizer can compare all matching worksheet pairs and present a summary report for each pair.

Comparing all worksheets in two Excel workbooks simultaneously with Synkronizer.

Highlighting Differences in Worksheets

By default, Synkronizer highlights all found differences with distinct colors:

Yellow: Value differences.
Lilac: Format differences.
Green: Inserted rows.

Highlighting differences between worksheets with Synkronizer Excel Compare.

To focus on specific types of differences, use the Outline button on the Results tab to filter and highlight only relevant changes.

Highlighting only relevant differences using Synkronizer’s filtering options.

Updating and Merging Worksheets

Synkronizer excels in merging capabilities, allowing you to transfer individual cells or entire rows/columns from a source worksheet to a target worksheet, updating your primary sheet quickly.

To update differences, select them in the Synkronizer pane and use the update buttons. Buttons are available to update all or selected differences, with arrows indicating the direction of transfer.

Updating and merging differences between worksheets using Synkronizer.

Synkronizer Excel Compare offers a robust solution for in-depth Excel file comparison and merging. A trial version is available for download here.

Ablebits Compare Sheets for Excel

Ablebits Compare Sheets for Excel is another powerful tool, integrated within their Ultimate Suite, designed for intuitive worksheet comparison. Key features include:

A step-by-step wizard to guide the comparison process and configure options.
Choice of comparison algorithms tailored to different data structures.
A “Review Differences” mode, displaying compared worksheets side-by-side for easy difference management.

Using Ablebits Compare Sheets involves a streamlined process:

Click the Compare Sheets button on the Ablebits Data tab, in the Merge group.
The wizard prompts you to select the two worksheets for comparison. You can choose entire sheets, current tables, or specific ranges.

Selecting the current table for worksheet comparison with Ablebits Compare Sheets.

Selecting two worksheets for comparison using Ablebits Compare Sheets.

Select a comparison algorithm:
- No key columns (default): Ideal for sheet-based documents like invoices or contracts.
- By key columns: Suitable for column-organized sheets with unique identifiers like order numbers or product IDs.
- Cell-by-cell: Best for spreadsheets with identical layouts, such as balance sheets or year-over-year reports.
The default “No key columns” option is generally a good starting point. The algorithm choice affects highlighting style (entire rows or individual cells) but all differences are still identified.

Choose a match type:
- First match (default): Compares a row in Sheet 1 with the first matching row in Sheet 2.
- Best match: Compares a row in Sheet 1 with the row in Sheet 2 that has the most matching cells.
- Full match only: Identifies rows that are identical in all cells; other rows are marked as different.

Selecting comparison algorithm and match type in Ablebits Compare Sheets.

Specify which differences to highlight and ignore, and choose how to mark differences. You can choose to include formatting differences and ignore hidden rows and columns.

Choosing differences to highlight and ignore in Ablebits Compare Sheets.

Click Compare. Ablebits Compare Sheets will process your data, create backup copies, and then open the worksheets in “Review Differences” mode.

Reviewing and Merging Differences in Ablebits Compare Sheets

In “Review Differences” mode, worksheets are displayed side-by-side with the first difference selected. Differences are highlighted with default colors:

Blue rows: Rows unique to Sheet 1 (left).
Red rows: Rows unique to Sheet 2 (right).
Green cells: Different cells in partially matching rows.

Reviewing differences between two worksheets in Ablebits Compare Sheets’ Review Differences mode.

Each worksheet has a vertical toolbar to navigate and manage differences. Use the toolbar to step through differences and decide to merge or ignore each one.

Reviewing and merging differences using Ablebits Compare Sheets’ toolbar.

Once all differences are addressed, you can save the workbooks and exit “Review Differences” mode. If you need to pause, you can exit and save changes, or restore original workbooks from backups.

Ablebits Compare Sheets provides an intuitive and effective way to excel compare two worksheets highlight differences. A trial version is available for download here.

xlCompare and Change pro for Excel

Other notable third-party tools include:

xlCompare: Compares and merges workbooks, worksheets, names, and VBA projects, with features for finding duplicates, updating records, and filtering comparison results.
Change pro for Excel: Compares worksheets on desktop and mobile, identifying formula, value, and layout changes, including embedded objects, and generates detailed difference reports.

Online Excel Comparison Services

For quick, software-free comparison, online services offer a convenient option. While security may be a concern for sensitive data, these services can be useful for non-confidential Excel files.

Services like XLComparator and CloudyExcel allow you to upload two Excel workbooks and quickly highlight differences in active sheets.

For example, CloudyExcel provides a simple interface:

Using an online service to compare Excel files.

After uploading files and clicking “Find Difference,” the service highlights differences directly in the worksheets.

Highlighted differences in two worksheets using an online Excel comparison service.

Online services offer a rapid way to visually excel compare two worksheets highlight differences without needing to install any software.

Conclusion

Comparing two Excel worksheets and highlighting differences is crucial for data accuracy and efficiency. From Excel’s built-in features like “View Side by Side,” formulas, and conditional formatting, to advanced third-party tools like Synkronizer Excel Compare and Ablebits Compare Sheets, and even online services, you have a range of options to choose from. The best method depends on the complexity of your data, the depth of comparison needed, and your workflow requirements. By mastering these techniques, you can significantly enhance your data management and analysis capabilities in Excel.