How to Compare Sheets In Excel: A Comprehensive Guide

Comparing sheets in Excel is a common task for many professionals. COMPARE.EDU.VN provides a detailed guide on How To Compare Sheets In Excel, covering various methods from simple side-by-side viewing to advanced third-party tools. Learn how to compare and merge Excel data, identify differences in values, formulas, and formatting, and streamline your data analysis process with Excel comparison techniques.

1. Viewing Excel Files Side by Side for Comparison

For smaller workbooks where a visual inspection suffices, Excel’s View Side by Side feature offers a straightforward solution. This method allows you to visually compare two workbooks or two sheets within the same workbook.

1.1. Comparing Two Excel Workbooks

Consider a scenario where you have sales reports for two different months and wish to analyze their performance concurrently.

To open two Excel files side by side:

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

By default, Excel arranges the windows horizontally.

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

With two separate Excel windows now arranged side by side, you can visually inspect the data.

To ensure synchronized scrolling while comparing data row by row, verify that the Synchronous Scrolling option is enabled. This option is found on the View tab, within the Window group, directly beneath the View Side by Side button. This feature is typically activated automatically when you enable View Side by Side mode.

1.2. Arranging Multiple Excel Windows Side by Side

To view more than two Excel files simultaneously, open all relevant workbooks and then click the View Side by Side button. The Compare Side by Side dialog box will appear, prompting you to select the files to be displayed along with the currently active workbook.

To view all open Excel files at once, click the Arrange All button on the View tab, located in the Window group, and then choose your preferred arrangement: tiled, horizontal, vertical, or cascade.

1.3. Comparing Two Sheets Within the Same Workbook

When the two sheets you wish to compare reside in the same workbook, follow these steps to view them side by side:

Open the Excel file, go to the View tab, and in the Window group, click the New Window button.
This will open a new instance of the same Excel file in a separate window.
Activate View Side by Side mode by clicking the corresponding button on the ribbon.
Select the first sheet in the first window and the second sheet in the second window.

2. Identifying Value Differences in Excel Sheets Using Formulas

A more analytical approach to comparing data in Excel involves identifying cells with differing values. This method allows you to create 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:

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

Copy this formula down and to the right by dragging the fill handle.

This formula utilizes relative cell references, which automatically adjust based on the position of the column and row. For example, the formula in cell A1 compares cell A1 in both Sheet1 and Sheet2, while the formula in cell B1 compares cell B1 in both sheets, and so on.

The result will look similar to this:

The formula compares the two sheets, identifies cells with different values, and displays these differences in the corresponding cells of the new sheet. Note that dates in the difference report are shown as serial numbers due to how Excel internally stores dates, which may not be ideal for analyzing date differences.

3. Highlighting Differences Between Excel Sheets Using Conditional Formatting

To visually highlight cells with differing values in two sheets, you can leverage Excel’s conditional formatting feature.

In the worksheet where you want to highlight differences, select all the used cells. Start by clicking the upper-left cell of the used range, typically A1, and then 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 using the following formula:

=A1<>Sheet2!A1

Ensure that you replace Sheet2 with the actual name of the other sheet you are comparing.

As a result, cells with different values will be highlighted in your chosen color.

While formulas and conditional formatting offer simple ways to compare two Excel sheets, they have limitations:

They only find differences in values and do not compare formulas or cell formatting.
They cannot identify added or deleted rows and columns. Adding or deleting a row or column in one sheet misaligns subsequent comparisons.
They operate on a sheet level and cannot detect workbook-level structural differences, such as added or deleted sheets.

4. Comparing and Merging Shared Workbook Copies

When dealing with multiple versions of the same Excel file, particularly in collaborative environments, the Compare and Merge feature can be invaluable. This feature allows you to view changes and comments from multiple users simultaneously.

To effectively use this feature, adhere to the following preparations:

Share your Excel workbook before distributing it to other users. To share the workbook, click the Share Workbook button on the Review tab, in the Changes group, check the Allow Changes by More Than One User… box, and click OK. If prompted, allow Excel to save the workbook. Enabling the Track Changes feature automatically shares the workbook.
Each user editing the shared workbook must save their copy (as a .xls or .xlsx file) with a unique name.

With the preliminary steps completed, you can proceed to combine the copies of the shared workbook.

4.1. Enabling the Compare and Merge Workbooks Feature

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

Open the Quick Access Toolbar dropdown menu and select More Commands.
In the Excel Options dialog box, choose All Commands under Choose commands from.
From 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

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

Open the primary version of the shared workbook.
Click the Compare and Merge Workbooks command in the Quick Access Toolbar.
In the dialog box, select the copies of the shared workbook that you want to merge. To select multiple copies, hold down the Shift key while clicking the file names, and then click OK.

The changes from each copy are then merged into the primary workbook.

4.3. Reviewing the Changes

To review all edits made by different users at a glance:

Go to the Review tab, in the 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, check the Highlight changes on screen box, and click OK.

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

Note: The Compare and Merge Workbooks command will be greyed out if you are trying to combine different Excel files. This feature only merges copies of the same shared workbook.

5. Third-Party Tools for Excel File Comparison

While Microsoft Excel provides some features for comparing workbooks, none of the built-in options can comprehensively compare Excel sheets or entire workbooks, especially when looking for differences in values, formulas, and formatting. Third-party tools are designed to overcome these limitations by comparing, updating, and merging Excel sheets and workbooks more efficiently.

5.1. Synkronizer Excel Compare: A Comprehensive Tool for Comparing, Merging, and Updating Excel Files

The Synkronizer Excel Compare add-in is a versatile tool that streamlines the process of comparing, merging, and updating Excel files.

Key features of Synkronizer Excel Compare include:

Identifying differences between two Excel sheets.
Combining multiple Excel files into a single version without creating duplicates.
Highlighting differences in both sheets.
Filtering differences to show only what is relevant to your task.
Merging and updating sheets.
Generating detailed and easy-to-read difference reports.

5.1.1. Comparing Two Excel Files for Differences

Consider an example where you are organizing an event and tracking participant information in an Excel table, including names, arrival dates, and the number of seats required. With multiple managers updating the database, you end up with two versions of the same Excel file.

To compare these sheets efficiently using Synkronizer:

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

The Synkronizer pane will appear, allowing you to:

Select the two workbooks to compare.

Choose the specific sheets to compare. If the selected workbooks contain sheets with identical names, they will be matched and automatically selected for comparison.

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

Select a comparison option:
- Compare as normal worksheets: Suitable for most cases.
- Compare with link options: Ideal if the selected sheets have no new or deleted rows and columns, allowing for a one-to-one comparison.
- Compare as database: Recommended for sheets structured as a database.
- Compare selected ranges: Allows you to specify which ranges to compare instead of the entire sheets.
Choose the content types to compare. On the Select tab, in the Compare group, you can specify which elements to compare, such as:
- Content: Compare comments and names in addition to the default cell values, formulas, and calculated values.
- Formats: Include cell formats like alignment, fill, font, and border.
- Filters: Exclude differences that are not relevant, such as case, spaces, formulas, hidden rows, and columns.
Click the Start button to begin the comparison.

5.1.2. Visualizing and Analyzing Differences

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

A summary report provides an overview of all difference types, including changes in columns, rows, cells, comments, formats, and names.
A detailed difference report is available by clicking on a specific difference type in the summary report.

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

Additionally, you can generate a difference report in a separate workbook, either standard or hyperlinked, allowing you to navigate directly to specific differences with a single click.

5.1.3. Comparing All Sheets in Two Workbooks

If the two Excel files you are comparing contain multiple sheets, all matching worksheet pairs will be listed in the summary report for your review.

5.1.4. Highlighting Differences Between Sheets

By default, Synkronizer Excel Compare highlights all identified differences:

Yellow indicates differences in cell values.
Lilac indicates differences in cell formats.
Green indicates inserted rows.

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

5.1.5. Updating and Merging Sheets

The merge function is a particularly valuable feature of the Synkronizer Excel Compare add-in, allowing you to transfer individual cells or entire columns/rows from the source sheet to the target sheet.

To update 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 only selected differences, with arrows indicating the transfer direction.

5.2. Ablebits Compare Sheets for Excel

Ultimate Suite includes Compare Sheets – to compare worksheets in Excel.

To make the comparison more intuitive and user-friendly, the add-in is designed in this way:

A step-by-step wizard walks you through the process and helps configure different options.
You can choose the comparison algorithm best suited for your data sets.
Instead of a difference report, the compared sheets are displayed in the Review Differences mode so that you could view all the differences at a glance and manage them one-by-one.

Click the Compare Sheets button on the Ablebits Data tab, in the Merge group:
The wizard will show up asking you to select the two worksheets you’d like to compare for differences. By default, the entire sheets are selected, but you can also select the current table

or a specific range by clicking the corresponding button:

On the next step, you select the comparison algorithm:
- No key columns (default) – works best for sheet-based documents like invoices or contracts.
- By key columns – is appropriate for column-organized sheets that have one or more unique identifiers such as order numbers or product ID’s.
- Cell-by-cell – best to be used to compare spreadsheets with the same layout and size, like balance sheets or year-to-year reports.
Tip. If you are unsure which option is right for you, go with the default one (No key columns). Whichever algorithm you select, the add-in will find all the differences, it will only highlight them differently (entire rows or individual cells).

On the same step, you can choose the preferred 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 the cells, and mark all other rows as different.
In this example, we will look for Best match by using the default No key columns comparing mode:

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

Click the Compare button and allow the add-in a couple of minutes to process your data and make the backup copies. Backups are always created automatically, so you may not worry about the safety of your data.

5.2.1. Review and Merge Differences

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

On the screenshot above, the 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.

To help you review and manage the differences, each worksheet has its own vertical toolbar. For the inactive worksheet (on the left) the toolbar is disabled. To enable the toolbar, just select any cell in the corresponding sheet.

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

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

If you have not finished processing the differences yet but would like to take a break for now, click the Exit Review Differences button at the bottom of the toolbar and choose either to:

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

5.3. xlCompare: Comprehensive Comparison and Merging Tool for Excel

xlCompare utility enables you to compare Excel files, worksheets, names, and VBA projects, identifying added, deleted, and changed data for efficient merging. Key features include:

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

5.4. Change pro for Excel: Desktop and Mobile Excel Sheet Comparison

Change pro for Excel facilitates the comparison of two sheets in desktop Excel and on mobile devices, with server-based comparison available. Key features include:

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

6. Online Services for Comparing Excel Files

In addition to desktop tools, several online services allow you to quickly compare two Excel sheets for differences without needing to install software. While security might be a concern, these services can be useful for Excel files that do not contain sensitive information.

Services such as XLComparator and CloudyExcel let you upload Excel workbooks and identify differences in a few clicks.

Here’s how CloudyExcel works:

Upload two Excel workbooks, click the Find Difference button, and the differences in the active sheets will be highlighted with different colors.

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 in the same or different workbooks.

2. How can I highlight the differences between two Excel sheets?
Use conditional formatting with a formula like =A1<>Sheet2!A1 to highlight cells that have different values.

3. Can I compare two Excel files without installing any software?
Yes, you can use online services like XLComparator or CloudyExcel to compare Excel files directly in your web browser.

4. What is the best tool for merging changes from multiple users in a shared Excel workbook?
The “Compare and Merge Workbooks” feature in Excel is designed for this purpose. You must share the workbook before allowing multiple users to edit copies.

5. How can I compare not just values, but also formulas and formatting in Excel?
Third-party tools like Synkronizer Excel Compare or Ablebits Compare Sheets for Excel are better suited for this task, as they can identify differences in formulas and formatting.

6. Is it possible to compare data based on specific key columns?
Yes, tools like Ablebits Compare Sheets for Excel offer comparison algorithms that use key columns to match and compare data.

7. How do I ensure the security of my data when using online comparison tools?
Only use trusted online services and avoid uploading files that contain sensitive or confidential information.

8. Can I create a report that lists all the differences between two Excel sheets?
Yes, Synkronizer Excel Compare, for example, generates detailed difference reports that summarize and highlight all the discrepancies between the sheets.

9. How do I compare VBA projects within Excel files?
The xlCompare utility allows you to compare VBA projects, as well as Excel files, worksheets, and names.

10. What should I do if the “Compare and Merge Workbooks” command is greyed out?
Ensure that you are trying to merge copies of the same shared workbook. This feature will not work on different Excel files.

8. Conclusion

Comparing sheets in Excel can be accomplished through various methods, each with its own strengths and limitations. Whether you opt for Excel’s built-in features or explore third-party tools, understanding these techniques will enhance your data analysis and decision-making capabilities. At COMPARE.EDU.VN, we strive to provide comprehensive comparisons to help you make informed choices.

Do you often find yourself struggling to compare different versions of spreadsheets or need to merge data from multiple sources? Visit COMPARE.EDU.VN today to explore detailed comparisons of various data management tools and find the perfect solution tailored to your specific needs. Make data-driven decisions with confidence.

Address: 333 Comparison Plaza, Choice City, CA 90210, United States
Whatsapp: +1 (626) 555-9090
Website: compare.edu.vn