Compare 2 Excel Sheets: Highlight Differences to Spot Key Data Changes

When working with data in Excel, it’s common to have multiple versions of spreadsheets. Whether you’re tracking changes over time, collaborating with colleagues, or managing different datasets, the ability to compare 2 Excel sheets and highlight differences is crucial. This skill allows you to quickly identify modifications, understand data evolution, and ensure accuracy in your analysis.

This comprehensive guide explores various effective methods to compare two Excel sheets and highlight differences. From simple visual checks to advanced features and third-party tools, you’ll learn techniques to pinpoint exactly what has changed, saving you time and preventing errors.

Visual Side-by-Side Comparison of Excel Sheets

For a quick, initial overview, especially with smaller datasets, visually comparing Excel sheets side-by-side is a straightforward approach. Excel’s built-in “View Side by Side” mode allows you to arrange two sheet windows for easy visual inspection. This method is excellent for spotting obvious discrepancies with the naked eye.

Comparing Two Excel Workbooks Visually

Let’s say you have two monthly sales reports in separate Excel files and need to quickly see performance variations. Here’s how to set up a side-by-side view:

Open both Excel workbooks you intend 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 next to each other, typically in a horizontal arrangement by default.

To switch to a vertical layout, which might be preferable for wider spreadsheets, click the Arrange All button within the Window group on the View tab and select Vertical.

To enhance the comparison process, activate Synchronous Scrolling. This feature links the scroll bars of both windows, allowing you to scroll through both sheets simultaneously and keep corresponding rows aligned for easier row-by-row comparison. Synchronous Scrolling is usually enabled automatically when you activate “View Side by Side” and is located right below the “View Side by Side” button on the View tab, in the Window group.

For more detailed instructions on utilizing this feature, refer to guides on viewing Excel workbooks side by side.

Comparing Multiple Excel Windows Simultaneously

If you need to compare more than two Excel files at once, Excel allows you to arrange multiple windows side by side. After opening all the workbooks, clicking View Side by Side will open the Compare Side by Side dialog box. Here, you can select which files you want to display alongside the currently active workbook.

Alternatively, to view all open Excel files in a structured manner, use the Arrange All button in the Window group on the View tab. Choose from options like tiled, horizontal, vertical, or cascade arrangement to organize your Excel windows.

Comparing Two Sheets Within the Same Workbook

Often, the two sheets you need to compare are within the same Excel workbook. To view these sheets side by side, follow these steps:

With your workbook open, 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 the side-by-side mode.
In each window, navigate to the specific sheet you want to compare.

Now you have two windows showing different sheets from the same workbook, perfectly positioned for visual comparison.

Formula-Based Comparison to Find Value Differences

For a more analytical approach to compare 2 Excel sheets and highlight differences, you can use Excel formulas. This method is particularly effective for identifying cells with different values and generating a difference report directly within Excel.

To compare two worksheets and create a difference report, insert a new blank worksheet. In cell A1 of this new sheet, enter the following formula:

=IF(Sheet1!A1 <> Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "")

Then, use the fill handle (the small square at the bottom-right corner of the cell) to drag this formula across columns and down rows, covering the range of data you need to compare in both Sheet1 and Sheet2.

This formula works by checking if the value in cell A1 of ‘Sheet1’ is different from the value in cell A1 of ‘Sheet2’. If they are different, the formula returns a text string indicating the values from both sheets. If the values are the same, it returns an empty string, leaving the cell blank in your difference report.

The result is a new sheet that acts as a difference report, listing only the cells where values diverge between the two original sheets.

Keep in mind that dates in the difference report might appear as serial numbers because of Excel’s internal date storage format. This might require you to adjust the formatting in the report sheet for better readability when comparing dates.

Conditional Formatting to Highlight Differences Directly

A visually impactful way to compare 2 Excel sheets and highlight differences is by using conditional formatting. This Excel feature allows you to automatically format cells based on specific rules. For comparing sheets, you can set up a rule to highlight cells that have different values across two sheets.

Here’s how to use conditional formatting to highlight differences:

Select the range of cells in the worksheet where you want to highlight the differences. Start by clicking the top-left cell of your data range (usually A1) and press Ctrl + Shift + End to select all used cells in your sheet.
Go to the Home tab on the ribbon, then to the Styles group, and click Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, choose “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 sheet you are comparing against.
Click the Format button to choose the formatting style (e.g., fill color) you want to apply to cells that are different.
Click OK in both dialog boxes to apply the conditional formatting rule.

Now, all cells in your selected range that have different values compared to the corresponding cells in “Sheet2” will be highlighted with the formatting you selected.

If you need more detailed guidance on conditional formatting, numerous tutorials are available online, such as those focusing on Excel conditional formatting based on another cell value.

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

They only compare values, not formulas or formatting.
They struggle with added or deleted rows or columns, often misidentifying subsequent rows/columns as different after an insertion or deletion.
They operate at the sheet level and cannot detect structural differences at the workbook level like added or removed sheets.

Compare and Merge Shared Workbook Copies

When dealing with collaborative work on Excel files, the “Compare and Merge Workbooks” feature can be invaluable. This Excel tool is designed to merge changes from different copies of a shared workbook, making it ideal for teams working on the same data.

Before using this feature, ensure the following preparations are made:

Share your Excel workbook: Enable sharing by clicking the Share Workbook button in the Changes group on the Review tab. Check the “Allow changes by more than one user…” box and click OK. If prompted, allow Excel to save the workbook. Enabling Track Changes automatically shares the workbook.
Each user saves a copy: Every team member editing the shared workbook must save a copy (.xls or .xlsx format) with a unique filename.

With these steps completed, you are ready to compare and merge the copies.

1. Enable the “Compare and Merge Workbooks” Feature

Although available in Excel 2010 through Microsoft 365, the “Compare and Merge Workbooks” command is not readily visible. You need to add it to the Quick Access Toolbar:

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

2. Compare and Merge Workbooks

Once configured, merging workbook copies is straightforward:

Open the original, shared workbook (the primary version).
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.

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

3. Review the Merged Changes

To review all edits made by different users, follow these steps:

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.

Excel highlights columns and rows with changes in red. Cell-level edits are marked with different colors for each user. Hovering over a changed cell will display information about who made the edit.

Note: The “Compare and Merge Workbooks” feature only works with copies of the same shared workbook. It cannot merge changes from different Excel files that are not copies of a shared original.

Third-Party Tools for Advanced Excel Comparison

While Excel offers built-in comparison features, they might not be sufficient for complex scenarios requiring in-depth analysis of differences across values, formulas, and formatting. For more robust and efficient Excel comparison, consider using third-party tools designed specifically for this purpose. These tools often provide advanced features for comparing, updating, and merging Excel sheets and workbooks.

Synkronizer Excel Compare: A Comprehensive Excel Comparison Tool

Synkronizer Excel Compare is a powerful add-in for Excel that excels in comparing, merging, and updating Excel files. It’s designed to save time and reduce manual effort in identifying differences.

Key features of Synkronizer Excel Compare include:

Detailed identification of differences between two Excel sheets.
Intelligent merging of multiple Excel files without creating duplicates.
Comprehensive highlighting of differences in both sheets.
Filtering options to focus on relevant differences.
Sheet merging and updating capabilities.
Creation of detailed, easy-to-understand difference reports.

Let’s look at an example of using Synkronizer Excel Compare to compare 2 Excel sheets and highlight differences. Imagine you’re managing event participant data in Excel, and you have two versions of the participant list due to updates from different managers.

To use Synkronizer:

Go to the Add-ins tab in Excel and click the Synchronizer 11 icon to launch the add-in.
In the Synkronizer pane:
- Select the two Excel workbooks you want to compare.
- Choose the specific sheets to compare. If sheets have the same names across workbooks, they are often automatically matched. You can also manually select sheets or match by other criteria like sheet type.
- Choose a comparison method:
  - Compare as normal worksheets (default for most cases)
  - Compare with link options (for sheets with identical row/column structure)
  - Compare as database (for structured data sheets)
  - Compare selected ranges (to compare specific parts of sheets)
- Optionally, select content types to compare under the Select tab > Compare group. You can include comments, names, formats, and filters to customize your comparison.
Click the Start button to initiate the comparison.

Analyzing and Visualizing Differences with Synkronizer

Synkronizer typically completes the comparison in seconds and presents results in two reports on the Results tab:

Summary report: Provides an overview of all difference types (columns, rows, cells, comments, formats, names).
Detailed difference report: Click on a difference type in the summary report to see a detailed breakdown of those specific differences.

Clicking on a difference in the detailed report highlights the corresponding cells in both compared sheets.

You can also generate a difference report in a separate Excel workbook, with hyperlinks to quickly jump to each difference.

Synkronizer can also compare all sheets in two workbooks simultaneously, presenting all matching sheet pairs in the summary report.

By default, Synkronizer highlights differences using colors:

Yellow: Cell value differences
Lilac: Cell format differences
Green: Inserted rows

You can customize highlighting to focus on specific difference types using the Outline button on the Results tab.

Synkronizer also allows you to update and merge sheets by transferring individual cells or entire rows/columns between sheets, simplifying the process of reconciling differences.

Synkronizer Excel Compare offers a robust solution for those needing advanced Excel comparison and merging capabilities. A trial version is available for download to explore its full features.

Ablebits Compare Sheets for Excel: User-Friendly Comparison Wizard

Ablebits Compare Sheets for Excel is another excellent tool, integrated into their Ultimate Suite for Excel. It focuses on user-friendliness with a step-by-step wizard and intuitive difference review.

Key features of Ablebits Compare Sheets include:

Step-by-step wizard for easy configuration.
Choice of comparison algorithms tailored to different data structures.
“Review Differences” mode for managing differences interactively.

Using Ablebits Compare Sheets on our example spreadsheets:

Click Compare Sheets 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.

Alt text: Option to select the current table for comparison in Ablebits Compare Sheets wizard.
Select a comparison algorithm:
- No key columns (best for sheet-based documents)
- By key columns (for column-organized sheets with identifiers)
- Cell-by-cell (for spreadsheets with identical layouts)
The default No key columns option is generally suitable if you’re unsure.

Choose a match type:
- First match
- Best match
- Full match only
For this example, we’ll use Best match with No key columns.
Specify differences to highlight and ignore, and how to mark them. You can choose to include formatting differences and ignore hidden rows/columns.
Click Compare. Ablebits Compare Sheets processes the data and creates automatic backups.

Reviewing and Merging Differences in Ablebits

After processing, the sheets open in “Review Differences” mode, side-by-side, with the first difference selected.

Differences are highlighted by default:

Blue rows: Rows only in Sheet 1
Red rows: Rows only in Sheet 2
Green cells: Different cells in partially matching rows

Each worksheet has its own vertical toolbar in “Review Differences” mode to navigate and manage differences. Use the toolbar to step through differences and decide to merge or ignore each one.

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

Ablebits Compare Sheets offers a user-friendly and efficient way to compare 2 Excel sheets and highlight differences, especially with its intuitive wizard and review mode. A trial version is available for download as part of the Ultimate Suite.

xlCompare: Workbook, Sheet, and VBA Project Comparison

xlCompare is another utility focused on comparing and merging Excel files, worksheets, names, and VBA projects. It identifies changes and facilitates quick merging, with features including:

Finding and removing duplicate records.
Updating records from one sheet to another.
Adding unique rows and columns.
Merging updated records between workbooks.
Sorting data by key columns.
Filtering comparison results.
Color-coded highlighting of differences.

Change pro for Excel: Desktop and Mobile Sheet Comparison

Change pro for Excel allows sheet comparison in desktop Excel and on mobile devices, with server-based comparison available. Key features include:

Formula and value difference detection.
Layout change identification (rows/columns added/deleted).
Embedded object recognition (charts, images).
Difference reports for formulas, values, and layout.
Filtering, sorting, and searching in difference reports.
Direct file comparison from Outlook or document management systems.
Multi-language support.

Online Excel File Comparison Services

For quick, software-free Excel sheet comparison, several online services are available. While security should be considered for sensitive data, these services can be handy for immediate, simple comparisons.

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

For example, CloudyExcel highlights differences with colors after you upload your files and click “Find Difference”.

These online tools offer a fast way to compare 2 Excel sheets and highlight differences without needing to install any software.

In conclusion, whether you need to visually inspect data, use formulas for value checks, apply conditional formatting for direct highlighting, or leverage advanced third-party tools for comprehensive comparison and merging, Excel offers a range of methods to compare 2 Excel sheets and highlight differences. Choose the technique that best fits your needs and complexity of your data comparison tasks. Explore further resources for more specialized comparison techniques if needed, and feel free to share your own preferred methods in the comments below.