Merge Two Tables for Excel

The Merge Tables Wizard add-in can match and merge data from two Excel worksheets in seconds. This smart tool is an easy-to-understand and convenient-to-use alternative to Excel Vlookup/Index+Match functions.

Video: How to merge two tables in Excel

Before you start

Excel instances

Open the Excel workbooks that contain the tables you are going to compare. Both tables should be opened in the same instance of Excel.

Hidden rows

If you have hidden rows in your main and lookup tables, they won't be processed.

Backup copies

Pay attention to the Create a backup copy of the worksheet checkbox. We recommend keeping this option selected as Excel doesn't let you cancel changes made by add-ins.

How to use Merge Tables Wizard

Start Merge Tables

On the Ablebits Data tab in the Merge group, click the Merge Two Tables icon:
Merge Two Tables in Excel.

Step 1: Select your main table

The main table is a table you merge with a lookup table. Your data will be updated only in the main table.
Select the main table.
There are 4 ways to select the main table:

  • Select any cell in your worksheet before running the add-on, and the whole table will be highlighted automatically.
  • To limit the range to certain data, select your records, and the add-in will pick up your selection.
    Note. If your records are formatted as a table, the add-in will always get the entire table.
  • If you'd prefer to have only a certain range highlighted rather than the whole table, use the Select range icon in the add-in window.
    Select the needed range.
  • You can also type the range address in the Select your main table field manually.
Note. If you have the standard Excel auto filter option switched on, the add-in will match only the visible filtered rows. If you need to update the entire table, turn the filter off before starting the add-in.

Click the Next button in the Merge Tables Wizard dialog box to continue.

Step 2: Pick your lookup table

The lookup table is a worksheet or range where you search for (look up) matching data. The add-in will pull information from this table. The lookup table remains intact after the add-in merges two tables.
Choose the lookup table.
On this step, you see all the open workbooks and worksheets in the Select your lookup table area. Choose the Excel worksheet with your lookup table and the add-in will highlight the used range.

Tip. You can edit the range by clicking the Select range icon or simply using your mouse cursor to select it in your table.

Step 3: Select matching columns

Your tables may have several columns in common. Key columns are the important ones that let you identify the same records in your sheets, for example, IDs or the combination of First and Last names. Please note that the values in these columns are only compared; you will be able to select the columns to update on the next step.

Here you can see a table with a list of all columns you have in your main sheet. Tick the checkboxes next to the columns you need to compare. Once chosen, the add-in will automatically pick a column with a matching header in a lookup table if there is one. If there is no match, please select one in the drop-down list of Lookup table columns.
Pick matching columns.

  1. If your tables have header rows, select the Main table has headers and/or Lookup table has headers checkboxes. If your tables do not have headers, clear these boxes. In this case, the contents of 1st row will be displayed to help you match the right records.
  2. If text case in the key columns is important to you, tick off the Case-sensitive matching checkbox at the top. This will instruct the Merge Tables Wizard to distinguish between uppercase and lowercase letters in the values it compares.
  3. Pressing Unselect All will remove selection from all columns.
Tip. If you have a lot of columns in your tables, you can expand the wizard window by dragging the bottom-right corner down and to the right until you get a suitable size.

Click Next to continue.

Step 4: Choose the columns to update in your main table

On this step, select the columns you want to update in the main table and pick the corresponding columns from the lookup table with the new values:
Select the columns to update.
You can also click the Auto Select button to select all matching columns at once. Press Unselect All to remove selection from all columns.

Tip. If you have a lot of columns in your table, the counter at the bottom of the add-in window will help you keep track of how many you select.

Press Next to proceed.

Step 5: Pick the columns to add to your main table

If there are new records in your lookup table, you can add them to your main sheet on this step. You will see a list of Lookup table columns that were not selected as a source for comparison or update on the previous steps.

Tick off the checkboxes next to the columns you want to insert into your main worksheet:
Choose the columns to add.
Select or deselect all columns at once by using the buttons Select All and Unselect All respectively.

Step 6: Choose additional merging options

The last step offers advanced options that let you fine-tune the merge. All these options are applied to the main table:
Adjust the merging options.

Add rows and columns

  • Add non-matching rows to the end of the main table
    Non-matching are the rows with the key values that are not present in your main worksheet.

    For example, you match tables by the column with IDs. The main table has IDs from 1 to 15. The lookup table contains IDs from 1 to 20. So, the IDs from 16 to 20 in your lookup table are non-matching.When you choose Add non-matching rows to the end of the main table, the rows with such values will be inserted after all rows in the appropriate columns of the main table:
    How to add non-matching rows.

  • Insert additional matching rows
    Select this option to add rows with duplicate key values that may contain unique information in other columns. You can paste additional matching rows at the end of the table or after the row with the same value in the key column.

    • Attach repeated rows after all data in the main table by choosing the At the end of the main table option:
      Additional matching rows at the end of your table.
    • Or insert additional rows from the lookup table right after the same key value in your main table and get all the duplicates grouped together. For this, select After the row with the same key value:
      Collect all dupes together.

      Tip. You can run Merge Duplicates to combine these rows and keep all unique information in place.
  • Set background color for all added rows
    Choose this option to mark the added rows with a background color.
  • Add a status column
    If you tick this option, the add-in will add a new column to your main table and mark rows as Matching, Matching and updated, or New row.

Update cells in the main table

This group lets you specify how to deal with empty cells in the main table.

  • Empty cells only
    If you have important information in the column you are updating, select this option to fill only the empty cells. The existing values in your main table will not be overwritten with this option selected.
  • Only if cells in the lookup table contain data
    Choose this option if you may have empty cells in your lookup table, and you don't want them to overwrite the existing values in your main table.

Highlight cells

You can highlight all updated records in your main table with color by ticking off this checkbox and picking an appropriate hue from the drop-down list. This way you will easily see the changes after joining tables.

Once you select all the options you need, click the Finish button and enjoy the results.
Merge two lists in Excel.

Responses

Hi,

My company is interested in buying the product, yet we wanted to solve a doubt first:
Can it combine multiple workbooks with multiple sheets to update a "master" workbook, but giving the option to choose which information we want to preserve? Or do you have another product that could do it?

Thanks.

ACC

Reply

Hi!

Thank you for your interest in our products.

I think Combine Worksheets Toolkit is the add-in you need.

Have a closer look at Ultimate Suite for Excel, it includes more than 60 tools to boost your Excel.
By the way, you still can take advantage of the Special May Offer!

Reply

I want ONLY the Merge Two Worksheets wizard, but you charge $100 for the entire Ultimate Suite, which I do NOT want. You were offering the Merge Tables tool for $60, what happened to it?

Reply

Hi Jim,
Thank you for your comment. You still can get Merge Tables Wizard without buying the entire Ultimate Suite. Please go here and click the Buy Now button under the Merge Tables Wizard section.

Reply

Post a comment

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.