How to Compare Two Sheets in Excel and Highlight Differences

Comparing two worksheets in Excel to pinpoint differences can be a time-consuming task. COMPARE.EDU.VN provides effective solutions for comparing two sheets in Excel and highlight differences, helping users quickly identify variations in data, formulas, and formatting. This article explores multiple methods to efficiently compare Excel sheets, saving you valuable time and improving accuracy. Discover user-friendly techniques and third-party tools for effective Excel file comparison, enabling you to streamline your workflow and make informed decisions by identifying data discrepancies with ease.

1. Comparing Excel Sheets Side-by-Side

If you have a keen eye for detail and relatively small workbooks, the “View Side by Side” mode offers a straightforward way to visually compare Excel files. This method enables you to arrange two Excel windows next to each other, allowing you to compare two workbooks or two sheets within the same workbook visually.

1.1. Comparing Two Excel Workbooks

Imagine you have sales reports from two different months and want to see both simultaneously to determine which products performed better each month.

Follow these steps to open two Excel files side by side:

Open both workbooks that you want to compare.
Navigate to the View tab, in the Window group, and select View Side by Side.

By default, Excel will show the two separate windows horizontally.

To show the Excel windows vertically, select Arrange All and select Vertical.

As a result, you’ll see two separate Excel windows arranged side by side.

If you want to compare data row by row, ensure Synchronous Scrolling is enabled. You can find this option in the View tab, in the Window group, under View Side by Side. It is usually enabled as soon as you activate View Side by Side mode.

1.2. Arranging Multiple Excel Windows Side by Side

If you need to view more than two Excel files simultaneously, open all the workbooks you want to compare, then select View Side by Side. The Compare Side by Side dialog box will appear, allowing you to select the files to display with the active workbook.

To view all open Excel files simultaneously, select Arrange All in the View tab, in the Window group, and choose the arrangement you want: tiled, horizontal, vertical, or cascade.

1.3. Comparing Two Sheets in the Same Workbook

When the two sheets you want to compare are in the same workbook, here’s how to view them side by side:

Open the Excel file, go to View > Window, and select New Window.
This will open the same Excel file in a different window.
Enable View Side by Side by selecting the corresponding button on the ribbon.
Choose sheet 1 in the first window and sheet 2 in the second window.

2. Comparing Two Excel Sheets for Value Differences

This simple data comparison method helps identify cells with different values, creating a difference report in a new worksheet.

To compare two Excel worksheets for differences, open a new, empty sheet, enter the following formula in cell A1, and drag the fill handle to copy it down and to the right:

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

Because the formula uses relative cell references, it changes based on the column and row position. So, the formula in A1 compares cell A1 in Sheet1 and Sheet2, the formula in B1 compares cell B1 in both sheets, and so on. The result will look like this:

As you can see, the formula identifies and displays differences in corresponding cells. In the difference report, dates (cell C4) appear as serial numbers because of how they are stored internally in Excel, which can be inconvenient for analyzing date differences.

3. Highlighting Differences Between Two Sheets with Conditional Formatting

To highlight cells with different values in two sheets using color, use Excel’s conditional formatting:

In the worksheet where you want to highlight differences, select all used cells. Click the upper-left cell of the used range (usually A1) and press Ctrl + Shift + End to extend the selection to the last used cell.
In the Home tab, in the Styles group, select Conditional Formatting > New Rule, and create a rule with this formula: =A1<>Sheet2!A1

Here, Sheet2 is the name of the other sheet you are comparing.

As a result, cells with different values will be highlighted with the color you chose:

While formulas and conditional formatting make it easy to compare two Excel sheets, they have limitations that make them unsuitable for comprehensive comparisons:

They only find value differences and can’t compare formulas or formatting.
They can’t identify added or deleted rows and columns; adding or deleting a row or column in one sheet marks all subsequent rows/columns as differences.
They operate at the sheet level and can’t detect workbook-level structural changes like sheet additions or deletions.

4. Comparing and Merging Shared Workbook Copies

When merging different versions of the same Excel file, the Compare and Merge feature is useful. It’s beneficial when multiple users collaborate on an Excel workbook, as it displays changes and comments from all users at once. To use this feature, make these preparations:

Share your Excel workbook before making it available to other users. Select the Share Workbook button in the Review tab, in the Changes group, check the Allow Changes by More Than One User… box, and select OK. Allow Excel to save the workbook if prompted. Sharing the workbook automatically turns on the Track Changes feature.
Each person editing the shared workbook must save a copy (.xls or .xlsx file) with a unique name.

After completing these steps, you can combine the copies of the shared workbook.

4.1. Enabling the Compare and Merge Workbooks Feature

Although the Compare and Merge Workbooks feature is available in Excel 2010 through Excel 365, it isn’t displayed by default. To add it to the Quick Access toolbar, do this:

Open the Quick Access drop-down menu and select More Commands.
In the Excel Options dialog box, select All Commands under Choose commands from.
In the list of commands, scroll to Compare and Merge Workbooks, select it, and select Add to move it to the right-hand section.
Select OK.

4.2. Comparing and Merging Workbooks

Once users finish working with your shared Excel workbook, you can merge all the copies into a single file.

Open the primary version of the shared workbook.
Select the Compare and Merge Workbooks command on the Quick Access toolbar.
In the dialog box, select a copy of the shared workbook to merge. To select multiple copies, hold the Shift key while selecting the file names, and select OK.

The changes from each copy are merged into one workbook.

4.3. Reviewing the Changes

To see all the edits by different users, do this:

Go to the Review tab > Changes, and select Track Changes > Highlight Changes.
In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, clear the Where box, check Highlight changes on screen, and select OK.

Excel highlights the column letters and row numbers in dark red to indicate rows and columns with differences. Edits from different users are marked with different colors at the cell level. Hover over the cell to see who made a specific change.

Note: If the Compare and Merge Workbooks command is grayed out, you might be trying to combine different Excel files. The Compare and Merge Workbooks feature only merges copies of the same shared workbook.

5. Third-Party Excel Comparison Tools

While Microsoft Excel offers features to compare data in workbooks, its built-in options aren’t sufficient for comprehensively comparing Excel sheets, let alone entire workbooks, detecting all differences in values, formulas, or formatting.

Third-party tools designed for comparing, updating, and merging Excel sheets and workbooks can provide advanced, efficient solutions. Here’s an overview of some top performers:

5.1. Synkronizer Excel Compare: A 3-in-1 Tool for Comparing, Merging, and Updating Excel Files

The Synkronizer Excel Compare add-in quickly compares, merges, and updates Excel files, saving you the trouble of manual difference searches.

Synkronizer Excel Compare offers helpful features for comparing two Excel sheets or workbooks:

Identifies differences between two Excel sheets.
Combines multiple Excel files into a single version without unwanted duplicates.
Highlights differences in both sheets.
Shows only task-relevant differences.
Merges and updates sheets.
Presents detailed and easy-to-read difference reports.

To demonstrate Synkronizer Excel Compare’s capabilities and performance, here are a couple of field tests.

5.1.1. Comparing Two Excel Files for Differences

Suppose you are planning an event and gathering participant information (name, arrival date, number of seats, etc.) in an Excel table. With two managers in contact with the participants and the database, you end up with two versions of the same Excel file.

Let’s see how efficiently Synkronizer can compare the two sheets and identify differences between them.

To run Synkronizer Excel Compare, select the Synchronizer 11 icon on the Add-ins tab.

The Synkronizer pane appears on the left side of your Excel window, where you do the following:

Select two workbooks to compare:

Select sheets to compare. If the selected workbooks have sheets with the same names, they will be matched and selected for comparison (like the Participants sheets in the screenshot).

You can also select worksheets manually or instruct the add-in to match sheets by other criteria, such as worksheet type (all, protected, or hidden).

Once you select the sheets, the Synkronizer add-in opens them side by side, arranged vertically or horizontally, like in Excel’s View Side by Side mode.

Select one of the comparison options:

Compare as normal worksheets (default option for most cases)
Compare with link options (if the selected sheets don’t contain new/deleted rows or columns, you can compare them “1 on 1”)
Compare as database (recommended for sheets with a database structure)
Compare selected ranges (define the ranges to be compared)

Choose the content types to compare (optional). On the Select tab, in the Compare group, choose the content type(s) relevant to your task:

Under Content, select comments and names (in addition to default cell values, formulas, and calculated values).
Under Formats, choose cell formats like alignment, fill, font, border, etc.
The Filters option filters out differences you don’t want to display. For example, ignore case, leading/trailing spaces, all formulas or different formulas with the same result, hidden rows/columns, and more.

Select the red Start button on the ribbon and examine the results.

5.1.2. Visualizing and Analyzing the Differences

Synkronizer typically takes a few seconds to compare two sheets and presents two summary reports on the Results tab:

The summary report displays all difference types at a glance: changes in columns, rows, cells, comments, formats, and names.
To view the detailed difference report, select a specific difference type on the summary report.

This screenshot shows the summary report (top of the Results pane) and the cell difference report (bottom of the pane) created for our sample sheets:

Selecting a difference in the detailed report selects the corresponding cells on both sheets (only one sheet is shown in the screenshot due to space limitations):

Additionally, you can create a standard or hyperlinked difference report in a separate workbook and jump to a specific difference with a mouse click:

5.1.3. Comparing All Sheets in Two Workbooks

If the two Excel files contain multiple sheets, all matching worksheet pairs appear in the summary report:

5.1.4. Highlighting Differences Between Sheets

By default, the Synkronizer Excel Compare add-in highlights all found differences:

Yellow – differences in cell values
Lilac – differences in cell formats
Green – inserted rows

To highlight only relevant differences, select the Outline button on the Results tab and select the needed options:

5.1.5. Updating and Merging Sheets

The merge function is a key feature of the Synkronizer Excel Compare add-in, allowing you to transfer individual cells or move columns/rows from the source to the target sheet, updating your primary sheet in seconds.

To update differences, select them on the Synkronizer pane and select one of the four update buttons. The first and last buttons update all differences, while the 2nd and 3rd buttons update selected differences (the button arrows indicate the transfer direction):

These are the key features of the Synkronizer add-in, and an evaluation version is available for download here.

5.2. Ablebits Compare Sheets for Excel

The latest version of our Ultimate Suite includes over 40 new features and improvements, including Compare Sheets, our tool for comparing worksheets in Excel.

Designed for intuitive and user-friendly comparison, the add-in:

Offers a step-by-step wizard to guide you through the process and configure options.
Allows you to choose the comparison algorithm best suited for your data sets.
Displays compared sheets in a Review Differences mode instead of a difference report, allowing you to view and manage all differences at a glance.

Let’s try the tool on sample spreadsheets from the previous example to see the results.

Select the Compare Sheets button in the Ablebits Data tab, in the Merge group:
The wizard appears, asking you to select the two worksheets you want to compare. The entire sheets are selected by default, but you can also select the current table

or a specific range by selecting the corresponding button:

In the next step, select the comparison algorithm:

No key columns (default) – best for sheet-based documents like invoices or contracts.
By key columns – appropriate for column-organized sheets that have one or more unique identifiers (order numbers or product IDs).
Cell-by-cell – best for spreadsheets with the same layout and size, like balance sheets or year-to-year reports.

If unsure, use the default option (No key columns). The add-in will find all differences regardless of the algorithm, but it will highlight them differently (entire rows or individual cells).

In the same step, choose the match type:

First match (default) – compare a row in Sheet 1 to the first found row in Sheet 2 that has at least one matching cell.
Best match – compare a row in Sheet 1 to the row in Sheet 2 that has the maximum number of matching cells.
Full match only – find rows in both sheets that have exactly the same values in all cells, and mark all other rows as different.

In this example, we will look for the Best match using the default No key columns mode:

Next, specify which differences to highlight and ignore, and how to mark the differences. Cell formatting is important in this example, so we select Show differences in formatting. Hidden rows and columns are irrelevant, so we instruct the add-in to ignore them:

Select the Compare button and allow the add-in a few minutes to process your data and create backup copies. Backups are created automatically, so your data is safe.

5.2.1. Reviewing and Merging Differences

Once the worksheets are processed, they open side-by-side in a Review Differences mode, with the first difference selected:

The differences are highlighted with default colors:

Blue rows – rows that exist only in Sheet 1 (left).
Red rows – rows that exist only in Sheet 2 (right).
Green cells – difference cells in partially matching rows.

Each worksheet has its own vertical toolbar to help you review and manage the differences. The toolbar is disabled for the inactive worksheet (left). To enable it, select any cell in the corresponding sheet.

Using this toolbar, go through the found differences one-by-one and decide whether to merge or ignore them:

Once the last difference is handled, you will be prompted to save the workbooks and exit the Review differences mode.

If you need to take a break before finishing, select the Exit Review Differences button at the bottom of the toolbar and choose to:

Save your changes and remove the remaining difference marks, or
Restore the original workbooks from the backup copies.

You can download an evaluation version here.

5.3. xlCompare: Comparing and Merging Workbooks, Sheets, and VBA Projects

xlCompare allows you to compare two Excel files, worksheets, names, and VBA Projects. It identifies added, deleted, and changed data and allows you to merge differences quickly. It also offers these options:

Find and remove duplicate records between two worksheets.
Update existing records in one sheet with values from another sheet.
Add unique (new) rows and columns from one sheet to another.
Merge updated records from one workbook to another.
Sort data on sheets by the key column.
Filter comparison results to display differences or identical records.
Highlight comparison results with colors.

5.4. Change pro for Excel: Comparing Excel Sheets on Desktop and Mobile Devices

Change pro for Excel allows you to compare two sheets in desktop Excel and on mobile devices with optional server-based comparison. Key features include:

Finding formula and value differences between two sheets.
Identifying layout changes, including added/deleted rows and columns.
Recognizing embedded objects (charts, graphs, images).
Creating and printing difference reports of formula, value, and layout differences.
Filtering, sorting, and searching the difference report for key changes.
Comparing files directly from Outlook or document management systems.
Support for all languages, including multi-byte.

6. Online Excel Comparison Services

Besides desktop tools and utilities, several online services allow you to quickly compare two Excel sheets for differences without installing software on your computer. While not ideal for security, these services can be used if your Excel files don’t contain sensitive information.

For example, try XLComparator or CloudyExcel. Here’s how the CloudyExcel service looks:

Upload the two Excel workbooks to compare and select the Find Difference button. The differences in the two active sheets will be highlighted with different colors:

7. FAQs About Comparing Excel Sheets and Highlighting Differences

Q1: What is the easiest way to compare two sheets in Excel?

The easiest way to compare two sheets in Excel is to use the “View Side by Side” feature. Open both sheets, go to the View tab, and click “View Side by Side.” This allows you to visually compare the sheets and manually identify differences.

Q2: How can I automatically highlight differences between two sheets in Excel?

You can automatically highlight differences using conditional formatting. Select the range of cells in one sheet, go to the Home tab, click “Conditional Formatting,” choose “New Rule,” and use a formula like =A1<>Sheet2!A1 to highlight differing cells.

Q3: Can I compare two Excel files without installing any software?

Yes, you can use online services like XLComparator or CloudyExcel. Simply upload your Excel files to the website, and it will highlight the differences between the sheets.

Q4: How do I compare two sheets in Excel if they have different layouts?

For sheets with different layouts, tools like Synkronizer Excel Compare or Ablebits Compare Sheets for Excel are useful. These tools offer advanced comparison algorithms to handle different structures and identify added, deleted, or modified data.

Q5: What is the Compare and Merge Workbooks feature used for?

The Compare and Merge Workbooks feature is used to combine multiple copies of a shared workbook. This is particularly useful when several users have made changes to the same file, allowing you to review and merge all edits into a single version.

Q6: How can I compare two sheets in Excel for formula differences?

Built-in Excel features have limitations when comparing formulas. Third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets for Excel are better suited for comparing formulas and identifying changes.

Q7: How do I highlight differences in formatting between two sheets in Excel?

To highlight formatting differences, use third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets for Excel. These tools can detect and highlight differences in cell formatting, such as font, color, and alignment.

Q8: Can I compare specific ranges within two sheets in Excel?

Yes, tools like Synkronizer Excel Compare allow you to select specific ranges within the sheets to compare, rather than comparing the entire sheets. This can be useful when you only need to focus on particular sections of the data.

Q9: How can I ensure my data is safe when using online Excel comparison services?

When using online services, be cautious about uploading sensitive information. If your data is confidential, it’s better to use desktop software or add-ins that perform comparisons locally.

Q10: What are the key considerations when choosing an Excel comparison tool?

When choosing an Excel comparison tool, consider factors like the complexity of your data, the need to compare formulas and formatting, the ability to handle different layouts, and your data security requirements. Evaluate your needs and choose a tool that best fits your specific use case.

COMPARE.EDU.VN offers a variety of resources and tools to help you compare data in Excel efficiently. Visit COMPARE.EDU.VN today to find the perfect solution for your comparison needs and make informed decisions with ease. For assistance, contact us at 333 Comparison Plaza, Choice City, CA 90210, United States, or via WhatsApp at +1 (626) 555-9090. Visit our website at compare.edu.vn.