Merge two worksheets in Excel

Combine tables by matching columns

Got stuck with tricky Vlookup formulas? Match and merge Excel worksheets an easy way. The Merge Tables Wizard can combine data from two sheets based on any number of common columns that you select. You can choose to update data in the main table, add new columns from the lookup table, pull non-matching rows and additional matches.

Free download Ultimate Suite for:
2019 365 2016 2013 2010
Download this and 60+ other tools for Excel included in Ablebits Ultimate Suite

14-day fully functional version
Merge Tables Wizard for Excel 2019, 2016 - 2010

With Merge Tables Wizard you will

  • Merge two spreadsheets without copy-pasting Match and combine data from two tables in seconds, even if they are in different Excel files.
  • Update data in the main table Update the selected columns in your main worksheet with information from the lookup table.
  • Insert non-matching rows Add non-matching rows to the end of the main table after all other data.
  • Ignore empty cells in the lookup table Update values in the main table only if the corresponding cells in the lookup table contain data.
  • Perform case-sensitive matching When text case matters, do a case-sensitive lookup to distinguish uppercase and lowercase characters.
  • Match one or multiple columns Auto-detect all matching columns and use any number of such columns as the unique identifier.
  • Add new columns from the lookup table If needed, pull some columns from the lookup table to the main table.
  • Retrieve additional matches Paste additional matching rows at the end of your main worksheet or after the row with the same key value.
  • Update only empty cells in the main table This option comes in handy when you don't want to overwrite any existing values in your main table.
  • Identify updated cells and added rows Add a status column to mark the changes or highlight the updates cells and added rows with color.

How to merge two Excel worksheets together

Video: Merge Excel sheets in 6 quick steps
Select the table you are going to update
Select the table you are going to update
Pick your Excel lookup table
Pick your Excel lookup table
Choose the key columns
Choose the key columns
Select the columns you want to update
Select the columns you want to update
Choose the columns you need to add to your main table
Choose the columns you need to add to your main table
Choose additional options: add rows, identify updates in a status column, etc.
Choose additional options: add rows, identify updates in a status column, etc.
Two Excel files are merged together
Two Excel files are merged together

Merge your Excel tables

Exactly the way you want

Update the existing column and add non-matching rows to the end of the main table
Update the existing column and add non-matching rows to the end
Add a new column from the lookup table and put non-matching rows to the end
Add a new column from the lookup table and put non-matching rows to the end
Paste additional matching rows at the end of the main table
Paste additional matching rows at the end of the main table
Insert multiple matches after the row with the same key value
Insert multiple matches after the row with the same key value
Update only empty cells in the main table
Update only empty cells in the main table

License benefits

Our customers get

Unconditional 60-days money-back guarantee
Life-time license, no additional
fees ever
Free life-time support
Secure payment guarantee
Volume discounts: the more you buy, the less you pay

These companies choose Ablebits products

Frequently asked questions

What is Merge Two Tables and what do I need it for?

Merge Two Tables is designed to quickly combine Excel worksheets by matching columns. The tables of interest can be located in the same workbook or in two separate Excel files and have different numbers of rows and columns.

With Merge Tables Wizard for Excel you can:

  • Use one or several key columns as the lookup criteria.
  • Add new columns to the main table or update the existing ones.
  • Add non-matching rows to the end of the table after all other data.
  • Pull additional matches from your lookup table.
  • Color updated rows or mark the changes in the status column.
  • Update only empty cells in your main table if you don't want to overwrite the existing values.
  • Update only if cells from lookup table contain data.

Is there any free way to match and merge two Excel files?

You can use Vlookup or Index/Match formulas to merge two worksheets by a column. If your knowledge of Excel formulas is above average, you can combine a few different functions in an array formula to return multiple matches. Probably, you could even figure out a way to vlookup with multiple criteria by concatenating several lookup values in a helper column.

Or, you can use our Merge Tables Wizard to combine two Excel worksheets by any number of key columns in six easy steps.

How to combine two Excel files with Merge Tables Wizard?

Click the Merge Two Tables button on the Ablebits Data tab, and do the following:

  1. Select your main table.
  2. Select your lookup table.
  3. Choose the key column(s) to match.
  4. Specify the columns to update in your main table.
  5. Optionally, choose the columns to add to your main table.
  6. Select additional merging options if needed, and click Finish.

See a more detailed description here.

How can I do a case-sensitive lookup?

Just tick off the Case-sensitive matching option on step 3. The wizard will treat lowercase or uppercase characters in the key columns as different values.

Is it possible just to add missing columns from the lookup table without any updates?

Of course. On step 4, don't select any columns to update, simply click Next. On steps 5, choose the columns to add to your main table.

What if my lookup table has rows that do not match any key columns in the main table?

If you want to copy such rows to your main table, select the Add non-matching rows to the end of the main table checkbox on step 6. Obviously, if you don't want to transfer any non-matching rows, then don't select this option :)

My lookup table has several rows with the same values in the key columns. Those rows have different information. Which ones will be taken to my master table?

By default, the Merge Tables Wizard takes the first matching row it finds. If you wish to pull all matches to your main table, on step 6, select the Insert additional matching rows box, and then choose either to add them all to the end of your table or to insert right after the row with the same key value.

What if some cells in my tables are empty?

No worries, we've got you covered. On step 6, select one of the below options, or both:

  • To ignore empty cells in the lookup table, tick off Update only if cells from the lookup table contain data.
  • Not to overwrite any existing values in your main table, select Update only empty and new cells in the main table.

My tables have loads of data. How could I see what's been updated?

There are two ways to identify the changes: text and color. If you prefer textual marks, add the Status column to your main table, and then you can filter updated rows, non-updated rows, or new rows. If you like coloring better, shade the updated cells and/or added rows with any color of your choosing. Of course, nothing prevents you from using both options at a time :)

Supported versions

Microsoft Excel

  • Excel included in ​Microsoft 365 (formerly Office 365)
  • Excel 2019
  • Excel 2016
  • Excel 2013
  • Excel 2010
  • 32-bit and 64-bit
  • Note! Office Insider is not supported

Windows

  • Windows 10
  • Windows 8.1
  • Windows 8
  • Windows 7
  • 32-bit and 64-bit
  • Note! Windows insider is not supported
  • Note! Surface Pro X and Windows X are not supported

Any questions or comments?

Please contact us here

Our working hours:
12:00 am - 10:00 am PST; 3:00 am - 1:00 pm EST; 8:00 am - 6:00 pm CET.

Your message was successfully sent.

Thank you for contacting us!
We will get back to you shortly.

You will receive a delivery confirmation for your message within a few minutes. If you don't get it, please re-send your message and make sure you entered a valid e-mail address.

Close