How To Add, Compare, and Merge Workbooks In Excel?

Do you want to know how to effectively compare and merge Excel workbooks? At COMPARE.EDU.VN, we provide a detailed guide on how to add, compare, and merge workbooks in Excel, enabling you to identify discrepancies and consolidate data efficiently. Learn how to leverage Excel’s built-in features and third-party tools to streamline your workflow. Explore methods for synchronous scrolling, conditional formatting, and utilizing add-ins for advanced comparisons, and enhance your data management skills with our comprehensive resources.

1. How to Compare Two Excel Files Side by Side?

Comparing two Excel files side by side allows for a visual inspection of data, which is particularly useful for smaller workbooks. This method helps in identifying discrepancies quickly by arranging the Excel windows in a way that facilitates direct comparison.

1.1 Comparing Two Excel Workbooks

To compare two Excel workbooks, follow these straightforward steps:

Open both Excel workbooks you intend to compare.
Navigate to the View tab. In the Window group, click the View Side by Side option.

By default, the workbooks are displayed horizontally. To arrange them vertically:

Click the Arrange All button, located in the same Window group under the View tab.
Select Vertical.

To enable synchronized scrolling:

Ensure the Synchronous Scrolling option is activated. It can be found under the View tab, in the Window group, right below the View Side by Side button.

Synchronous scrolling allows you to scroll through both worksheets simultaneously, facilitating a row-by-row comparison.

1.2 Arranging Multiple Excel Windows Side by Side

To compare more than two Excel files:

Open all the workbooks.
Click the View Side by Side option. A Compare Side by Side dialog box will appear.
Choose the files to display alongside the active workbook.

To view all open Excel files at once:

Click Arrange All under the View tab.
Select your preferred arrangement: tiled, horizontal, vertical, or cascade.

1.3 Comparing Two Sheets in the Same Workbook

To compare two sheets within the same workbook:

Open the Excel file. Go to the View tab, then click New Window.
This will open the same file in a new window.
Enable View Side by Side mode.
Select the first sheet in one window and the second sheet in the other window.

2. How to Compare Two Excel Sheets for Differences in Values?

Comparing two Excel sheets for differences in values helps pinpoint specific cells where the data varies. This is useful for generating a difference report that highlights these discrepancies.

To implement this method:

Open a new, blank sheet in Excel.
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 to cover the range of cells you need to compare.

This formula checks corresponding cells in Sheet1 and Sheet2. If the values differ, it displays the values from both sheets in the format “Sheet1:value1 vs Sheet2:value2”. If the values are the same, the cell remains blank.

3. How to Highlight Differences Between Two Sheets with Conditional Formatting?

Conditional formatting provides a visual method to highlight differences between two Excel sheets. This method uses color-coding to draw attention to cells with different values.

Here’s how to set it up:

Select all the used cells in the worksheet where you want to highlight the differences. Start by clicking on the upper-left cell (usually A1), then press Ctrl + Shift + End to extend the selection to the last used cell.
Go to the Home tab, and in the Styles group, click Conditional Formatting > New Rule.
Choose “Use a formula to determine which cells to format.”
Enter the following formula:

=A1<>Sheet2!A1

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

Click the Format button to choose a color or style for highlighting the differences.
Click OK to apply the rule.

4. How to Compare and Merge Copies of a Shared Workbook?

The Compare and Merge feature is useful when multiple users collaborate on the same Excel workbook. It allows you to view and integrate changes made by different users into a single file.

Before using this feature, ensure the following:

Share the Workbook: Enable the Share Workbook feature by going to the Review tab, in the Changes group, and clicking on Share Workbook. Check the box labeled Allow Changes by More Than One User… and click OK.
Unique Copies: Each user must save their edited copy with a unique file name.

4.1 Enabling the Compare and Merge Workbooks Feature in Excel

The Compare and Merge Workbooks command is not readily available in Excel. To add it to the Quick Access Toolbar:

Click the dropdown arrow on the Quick Access Toolbar and select More Commands.
In the Excel Options dialog box, choose All Commands from the “Choose commands from” dropdown.
Scroll down and select Compare and Merge Workbooks, then click Add.
Click OK.

4.2 Comparing and Merging Workbooks

Once the command is added and all users have finished their edits:

Open the primary version of the shared workbook.
Click the Compare and Merge Workbooks command on the Quick Access Toolbar.
Select the copies you want to merge in the dialog box that appears. Hold the Shift key to select multiple files.
Click OK.

4.3 Reviewing the Changes

To review all edits made by different users:

Go to the Review tab, in the Changes group, click Track Changes > Highlight Changes.
In the Highlight Changes dialog, select All in the When box, Everyone in the Who box, and ensure the Highlight changes on screen box is checked.
Click OK.

Excel highlights the column letters and row numbers in dark red to indicate where changes have been made. Different users’ edits are marked with different colors at the cell level. Hovering over a cell will show who made the change.

5. What Are Third-Party Tools for Comparing Excel Files?

While Excel offers built-in features to compare data, they may not be sufficient for comprehensive comparisons involving values, formulas, and formatting. Third-party tools offer advanced capabilities to compare, update, and merge Excel sheets and workbooks.

5.1 Synkronizer Excel Compare

The Synkronizer Excel Compare add-in is designed to quickly compare, merge, and update Excel files. Its key features include:

Identifying differences between Excel sheets.
Combining multiple Excel files into a single version without duplicates.
Highlighting differences in both sheets.
Merging and updating sheets.
Providing detailed difference reports.

5.1.1 Comparing Two Excel Files for Differences Using Synkronizer

To compare two Excel files:

Go to the Add-ins tab and click the Synkronizer icon.
Select the two workbooks to compare in the Synkronizer pane.
Choose the sheets to compare. If sheets have the same names, they will be matched automatically.
Select a comparison option:
- Compare as normal worksheets
- Compare with link options
- Compare as database
- Compare selected ranges
Choose the content types to compare (e.g., comments, names, formats).
Click the Start button.

5.1.2 Visualizing and Analyzing Differences Using Synkronizer

Synkronizer generates two summary reports on the Results tab:

A summary report showing all difference types.
A detailed difference report accessible by clicking on a specific difference type in the summary report.

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

5.1.3 Highlighting Differences Between Sheets Using Synkronizer

Synkronizer highlights differences by default:

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

5.1.4 Updating and Merging Sheets Using Synkronizer

The merge function allows transferring individual cells or moving columns/rows from the source to the target sheet.

Select the differences in the Synkronizer pane.
Click one of the four update buttons to update selected or all differences.

5.2 Ablebits Compare Sheets for Excel

Ablebits Compare Sheets is a tool designed with a step-by-step wizard to guide you through the comparison process, helping you configure different options. Key features include:

Choice of comparison algorithm.
Display of compared sheets in a Review Differences mode.

To use the tool:

Click the Compare Sheets button on the Ablebits Data tab.
Select the worksheets you want to compare.
Choose the comparison algorithm:
- No key columns
- By key columns
- Cell-by-cell
Specify which differences to highlight and how to mark them.
Click the Compare button.

5.2.1 Review and Merge Differences Using Ablebits

Once the worksheets are processed, they open side-by-side in a Review Differences mode. Differences are highlighted with default colors:

Blue rows: rows that exist only in Sheet 1.
Red rows: rows that exist only in Sheet 2.
Green cells: different cells in partially matching rows.

You can use the vertical toolbar to go through the differences one-by-one and decide whether to merge or ignore them.

5.3 xlCompare

xlCompare allows you to compare two Excel files, worksheets, names, and VBA projects. Key features:

Find duplicate records and remove them.
Update existing records with values from another sheet.
Add unique rows and columns.
Merge updated records.
Sort data by key column.
Filter comparison results.
Highlight comparison results with colors.

5.4 Change pro for Excel

Change pro for Excel is used to compare two sheets on desktop and mobile devices. Key features include:

Find differences in formulas and values.
Identify layout changes.
Recognize embedded objects.
Create and print difference reports.
Compare files directly from Outlook or document management systems.

6. Online Services to Compare Excel Files

Online services allow you to quickly compare two Excel sheets without installing software. While security should be considered, these services can be useful for non-sensitive data.

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

7. FAQ: Comparing and Merging Excel Workbooks

How can I compare two Excel files to find differences in data entries?
Use the formula =IF(Sheet1!A1<>Sheet2!A1, "Sheet1:"&Sheet1!A1&" vs Sheet2:"&Sheet2!A1, "") in a new sheet to compare corresponding cells. Alternatively, use conditional formatting to highlight differences.
What is the best way to highlight differences between two Excel sheets?
Conditional formatting is the best way to highlight differences. Create a new rule using the formula =A1<>Sheet2!A1 and choose a formatting style to highlight differing cells.
Can I compare two sheets in the same Excel workbook side by side?
Yes, open the Excel file, go to View > New Window, then enable View Side by Side to compare the two sheets.
How do I merge copies of a shared workbook in Excel?
Enable Share Workbook, have each user save a unique copy, add the Compare and Merge Workbooks command to the Quick Access Toolbar, and then use this command to merge the copies.
What is the Synkronizer Excel Compare add-in, and how does it help?
Synkronizer Excel Compare is a third-party tool that compares, merges, and updates Excel files. It identifies differences, highlights changes, and provides detailed reports, streamlining the comparison process.
How does Ablebits Compare Sheets for Excel work?
Ablebits Compare Sheets uses a step-by-step wizard to compare Excel worksheets, offering different comparison algorithms and displaying results in a Review Differences mode for easy management.
What features does xlCompare offer for Excel file comparison?
xlCompare allows you to compare files, worksheets, names, and VBA projects. It finds duplicates, updates records, adds unique rows/columns, and provides filtering and highlighting options.
Can I use online services to compare Excel files?
Yes, online services like XLComparator and CloudyExcel allow you to upload and compare Excel files without installing software.
How do I highlight changes made by different users in a shared Excel workbook?
Go to the Review tab, click Track Changes > Highlight Changes, and set the options to highlight changes by different users on the screen.
Are there any limitations to using Excel’s built-in comparison features?
Excel’s built-in features may not be sufficient for comprehensive comparisons involving values, formulas, and formatting. Third-party tools offer more advanced capabilities.

Comparing and merging Excel workbooks doesn’t have to be a daunting task. With the right techniques and tools, you can efficiently manage and consolidate your data. Whether you’re using Excel’s built-in features or exploring third-party add-ins like Synkronizer and Ablebits, understanding these methods will enhance your data management skills.

Ready to dive deeper into the world of Excel comparisons? Head over to COMPARE.EDU.VN for more in-depth guides, reviews, and tutorials. Don’t let data discrepancies slow you down – equip yourself with the knowledge to tackle any Excel challenge.

For further assistance, contact us at:

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

WhatsApp: +1 (626) 555-9090

Website: compare.edu.vn

We’re here to help you make informed decisions!