How to Compare Two Sheets in Excel to Find Differences

How To Compare Two Sheets In Excel To Find Differences? COMPARE.EDU.VN provides several effective methods to compare data in Excel, identify discrepancies, and streamline your workflow, whether you’re dealing with financial data, sales reports, or project timelines. From viewing files side-by-side to using formulas and conditional formatting, discover the best approaches for your needs. Improve your efficiency and accuracy with Excel comparison, data analysis, and spreadsheet management.

1. Comparing Two Excel Files Side by Side

This method is suitable for smaller workbooks and those with a keen eye for detail. Excel’s View Side by Side mode allows you to arrange two Excel windows next to each other, facilitating visual comparison of workbooks or sheets within the same workbook.

1.1. Comparing Two Excel Workbooks

Consider you have two monthly sales reports and want to compare them to identify which products performed better in each month.

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

Open the workbooks you want to compare.
Go to the View tab, Window group, and click the View Side by Side button.

By default, the Excel windows are displayed horizontally.

To arrange Excel windows vertically, click the Arrange All button and select Vertical:

This will arrange the Excel windows side by side, enabling you to compare the data.

To scroll through both worksheets at the same time, ensure that the Synchronous Scrolling option is enabled. This is located on the View tab, in the Window group, below the View Side by Side button:

1.2. Arranging Multiple Excel Windows Side by Side

To view more than two Excel files simultaneously, open all the workbooks you want to compare and click the View Side by Side button. A 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 at once, click the Arrange All button on the View tab, in the Window group, and choose an arrangement option such as tiled, horizontal, vertical, or cascade.

1.3. Comparing Two Sheets in the Same Workbook

Sometimes, the two sheets you need to compare are in the same workbook. To view them side by side, follow these steps:

Open your Excel file, go to the View tab > Window group, and click the New Window button.
This will open the same Excel file in a new window.
Enable View Side by Side mode by clicking the corresponding button on the ribbon.
Select sheet 1 in the first window and sheet 2 in the second window.

2. Comparing Two Excel Sheets for Differences in Values

This is a basic method to compare data in Excel to find 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 copy it down and to the right by dragging the fill handle:

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

Due to the use of relative cell references, the formula will adapt based on the column and row. 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 appear as follows:

As shown, the formula compares two sheets, identifies cells with different values, and displays the differences in the corresponding cells. Note that dates in the difference report (cell C4) are displayed as serial numbers, 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 a color of your choice, use Excel’s conditional formatting feature.

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.
On the Home tab, in the Styles group, click Conditional Formatting > New Rule, and create a rule with the following formula:

=A1<>Sheet2!A1

Where Sheet2 is the name of the other sheet you are comparing.

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

This method is straightforward, but it has limitations:

It only identifies differences in values, not in formulas or cell formatting.
It cannot identify added or deleted rows and columns. Adding or deleting a row/column in one sheet marks all subsequent rows/columns as different.
It works on a sheet level and cannot detect workbook-level structural differences such as sheet additions and deletions.

4. Comparing and Merging Copies of a Shared Workbook

When merging different versions of the same Excel file, the Compare and Merge feature is useful, especially when multiple users collaborate on the same Excel workbook. This feature allows you to view changes and comments from all users simultaneously. Ensure these preparations are made:

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

4.1. Enabling the Compare and Merge Workbooks Feature in Excel

The Compare and Merge Workbooks feature is available in Excel 2010 through Excel 365, but it is not displayed by default. Add it to the Quick Access toolbar:

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 down to Compare and Merge Workbooks, select it, and click the Add button to move it to the right-hand section.
Click OK.

4.2. Comparing and Merging Workbooks

After users have finished working with the shared Excel workbook, merge the copies into one file:

Open the primary version of the shared workbook.
Click 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 clicking the file names, and then click OK.

4.3. Reviewing the Changes

To view all edits by different users:

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

Excel highlights column letters and row numbers with differences in dark red, and edits from different users are marked with different colors at the cell level. To see who made a specific change, hover over the cell.

If the Compare and Merge Workbooks command is greyed out, ensure you are combining copies of the same shared workbook. This feature only merges copies of the same shared workbook, not different Excel files.

5. Third-Party Tools to Compare Excel Files

Excel offers several built-in features to compare data, but none are comprehensive enough to compare entire workbooks, identifying all differences in values, formulas, or formatting. Third-party tools provide advanced and efficient means to compare, update, and merge Excel sheets and workbooks. Here’s a quick overview of some top performers in this area.

5.1. Synkronizer Excel Compare: 3-in-1 Tool to Compare, Merge, and Update Excel Files

The Synkronizer Excel Compare add-in allows you to quickly compare, merge, and update two Excel files, saving the effort of manual searching for differences.

Key features of Synkronizer Excel Compare:

Identifying differences between two Excel sheets.
Combining multiple Excel files into a single version without duplicates.
Highlighting differences in both sheets.
Showing task-relevant differences.
Merging and updating sheets.
Presenting detailed, readable difference reports.

5.1.1. Comparing Two Excel Files for Differences

Suppose you are organizing an event and gather participant information in an Excel table, including names, arrival dates, and seat numbers. Two managers are in direct contact with participants, resulting in two versions of the same Excel file.

Let’s see how Synkronizer can efficiently compare these sheets and identify differences.

To run Synkronizer Excel Compare, go to the Add-ins tab and click the Synchronizer 11 icon:

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

Select two workbooks to compare:

Select sheets to compare. If the selected workbooks have sheets with the same names, they are matched and automatically selected for comparison (like Participants sheets in the example):

Worksheets can be selected manually, or the add-in can match sheets by criteria such as worksheet type (all, protected, or hidden).

Once the sheets are selected, Synkronizer opens them side by side, arranged vertically or horizontally, similar to Excel’s View Side by Side mode.

Select one of the following comparison options:
- Compare as normal worksheets – the default option for most cases.
- Compare with link options – compares sheets “1 on 1” if they do not contain new or deleted rows and columns.
- Compare as database – recommended for sheets with a database structure.
- Compare selected ranges – define the ranges to compare instead of entire sheets.
Choose the content types to be compared (optional). On the Select tab, in the Compare group, you can choose the relevant content types:
- Under Content, select comments and names (in addition to cell values, formulas, and calculated values, which are compared by default).
- Under Formats, choose cell formats such as alignment, fill, font, border, etc.
- The Filters option lets you filter out differences that you don’t want to display, such as ignoring case, leading or trailing spaces, all formulas or different formulas with the same result, and hidden rows or columns.
Click the big red Start button on the ribbon to examine the results.

5.1.2. Visualizing and Analyzing the Differences

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

A summary report displays all difference types at a glance: changes in columns, rows, cells, comments, formats, and names.
A detailed difference report can be viewed by clicking a specific difference type on the summary report.

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

Clicking on a difference in the detailed report selects the corresponding cells on both sheets:

In addition, a difference report can be created in a separate workbook (standard or hyperlinked), allowing you to jump to a specific difference with a mouse click:

5.1.3. Comparing All Sheets in Two Workbooks Simultaneously

If the two Excel files contain multiple sheets, all matching worksheet pairs will be presented in the summary report for review:

5.1.4. Highlighting Differences Between Sheets

By default, Synkronizer Excel Compare highlights all found differences:

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

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

5.1.5. Updating and Merging Sheets

The merge function is one of the most useful features of the Synkronizer Excel Compare add-in. You can transfer individual cells or move columns/rows from the source to target sheet, updating your primary sheet in seconds.

To update one or more differences, select them in the Synkronizer pane and click one of the four update buttons. The first and last buttons update all differences, while the second and third buttons update selected differences only (button arrows indicate the transfer direction):

5.2. Ablebits Compare Sheets for Excel

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

To make the comparison intuitive and user-friendly, the add-in is designed with:

A step-by-step wizard to guide you through the process and help configure options.
A choice of comparison algorithms suited for different data sets.
Compared sheets displayed in Review Differences mode to view all differences at a glance and manage them one-by-one, instead of a difference report.

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

Click the Compare Sheets button on the Ablebits Data tab, in the Merge group.
The wizard prompts you to select the two worksheets to compare. By default, the entire sheets are selected, but you can also select the current table:

or a specific range:

On the next step, select the comparison algorithm:
- No key columns (default) – best 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 comparing spreadsheets with the same layout and size, like balance sheets or year-to-year reports.
Tip: If unsure, choose the default option (No key columns). The add-in finds all differences regardless, but highlights them differently (entire rows or individual cells).

On the same step, choose the preferred match type:
- First match (default) – compares a row in Sheet 1 to the first found row in Sheet 2 with at least one matching cell.
- Best match – compares a row in Sheet 1 to the row in Sheet 2 with the maximum number of matching cells.
- Full match only – finds rows in both sheets with exactly the same values in all cells, marking all other rows as different.
In this example, we will look for Best match by using the default No key columns comparing mode:

Specify which differences to highlight and ignore, and how to mark the differences. If cell formatting is important, select Show differences in formatting. Hidden rows and columns can be ignored:

Click the Compare button and allow the add-in a few minutes to process your data and create backup copies. Backups are always created automatically, ensuring data safety.

5.2.1. Reviewing and Merging Differences

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

In the screenshot above, differences are highlighted with the default colors:

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

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

Use the toolbar to go through the differences one-by-one and decide whether to merge or ignore them:

When the last difference is dealt with, you will be prompted to save the workbooks and exit Review differences mode.

If you need to take a break before processing all differences, click the Exit Review Differences button at the bottom of the toolbar and choose to:

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

5.3. xlCompare: Compare and Merge Workbooks, Sheets, and VBA Projects

With the xlCompare utility, you can compare two Excel files, worksheets, names, and VBA Projects. It identifies added, deleted, and changed data and allows you to quickly merge differences. Additional options include:

Finding and removing duplicate records between two worksheets.
Updating existing records in one sheet with values from another sheet.
Adding unique (new) rows and columns from one sheet to another.
Merging all updated records from one workbook to another.
Sorting data on the sheets by the key column.
Filtering comparison results to display differences or identical records.
Highlighting comparison results with colors.

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

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

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

6. Online Services to Compare Excel Files

Besides desktop tools, online services allow you to quickly compare two Excel sheets for differences without installing software. While security might be a concern, these services can provide immediate results if your Excel files do not contain sensitive information.

Examples include XLComparator and CloudyExcel. The CloudyExcel service looks like this:

Upload the two Excel workbooks and click the Find Difference button. The differences in the two active sheets are highlighted with different colors:

These methods provide various ways to compare Excel files for differences. If none of these solutions meet your needs, additional resources cover other aspects of Excel file comparison.

Address: 333 Comparison Plaza, Choice City, CA 90210, United States.

Whatsapp: +1 (626) 555-9090.

Website: COMPARE.EDU.VN

COMPARE.EDU.VN offers various methods for comparing Excel sheets, ensuring you can find the best solution for your specific needs. Whether you’re comparing financial data, managing projects, or analyzing sales reports, these techniques will enhance your efficiency and accuracy.

7. Frequently Asked Questions (FAQ)

1. What is the easiest way to compare two sheets in Excel?

The easiest way is to use the “View Side by Side” feature, which allows you to visually compare two sheets simultaneously. This is suitable for smaller workbooks and users with a keen eye for detail.

2. How can I highlight differences between two Excel sheets?

Use conditional formatting. Select the data range, create a new rule, and use a formula like =A1<>Sheet2!A1 to highlight cells with different values.

3. Can I compare two sheets for differences in formulas and formatting?

Built-in Excel features have limitations. For comprehensive comparison, consider third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets, which identify differences in values, formulas, and formatting.

4. How do I compare and merge copies of a shared workbook in Excel?

Enable the “Compare and Merge Workbooks” feature. Share the workbook, have each user save a copy, and then merge the copies back into one file, reviewing the changes using the “Track Changes” feature.

5. What are the limitations of using Excel’s built-in comparison features?

Built-in features primarily compare values and may not effectively identify differences in formulas, formatting, added/deleted rows, or workbook-level structural changes.

6. Are there online services available to compare Excel files?

Yes, services like XLComparator and CloudyExcel allow you to upload and compare Excel files online. However, ensure your data is not sensitive before using these services.

7. What is the “Compare and Merge Workbooks” feature used for?

It is used to combine multiple copies of a shared workbook into one file, allowing you to review and merge changes made by different users.

8. How do third-party tools enhance Excel comparison capabilities?

Tools like Synkronizer and Ablebits Compare Sheets offer advanced features such as identifying differences in values, formulas, and formatting, as well as providing detailed difference reports and merging options.

9. What should I do if the “Compare and Merge Workbooks” command is greyed out?

Ensure you are trying to merge copies of the same shared workbook. This feature does not work with different Excel files.

10. How can I ensure data safety when using online Excel comparison services?

Only use these services for non-sensitive data, as uploading confidential information may pose a security risk.

COMPARE.EDU.VN provides thorough comparisons to help you make informed decisions. If you’re seeking detailed comparisons of products, services, or ideas, visit COMPARE.EDU.VN to explore comprehensive analyses that simplify decision-making.

8. Call to Action

Ready to make smarter decisions? Visit compare.edu.vn today to access detailed, objective comparisons that help you choose the best options for your needs. Whether it’s software, services, or products, we’ve got you covered. Don’t make a decision without us!