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.

Buy now
  • 30-day unconditional money-back guarantee
  • 1 license for 2 PCs, e.g. work and home
  • Life-time license & support, no annual fees
Merge Tables Wizard for Excel 2016 - 2007

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

Thank you for the great product

Thank you for the great product, it truly opened whole new possibilities for me in combining and analysing data from different sources.
Laisan Shafikova

I can't tell you how much we love Merge Tables Wizard

I can't tell you how much we love Merge Tables Wizard here. It has revolutionized the way some of our staff do their jobs - replacing literally hours of manual work with a few quick button clicks. Love it.
Jason D Polete

Fantastic program

The Ablebits Merge Tables add-on is a fantastic program. It facilitates my projects for sure saving numerous hours of drudgery not to mention reducing the chance of error in manually importing data. I would definitely recommend it to anyone who needs to pull data from one Excel file to another.
Dr. Douglas Boyd Ph.D.

Merge Table Wizard increases productivity and efficiency

Your products and customer service are FANTASTIC! I have been spreading the word and others have also discovered how Merge Table Wizard can increase productivity and efficiency. I have used it extensively with targeted marketing initiatives.
Joanne Brennan

Excel Merge Wizard: time saving magic

The Merge Tables Wizard for Excel has been trialled by our team and we give it a big WMG thumbs up; it has now become a daily tool which is indispensable to our data collating and merging needs.
The WebMarketing Group

It does exactly what you say it should do

Thank you so much for making this add-in for Excel. It does exactly what you say it should do, on my 10,250 item database. Awesome!
Nannette DiMascio, Broker / Associate, Realty One Group

Thank you for the great product

Thank you for the great product, it truly opened whole new possibilities for me in combining and analysing data from different sources.
Laisan Shafikova

I can't tell you how much we love Merge Tables Wizard

I can't tell you how much we love Merge Tables Wizard here. It has revolutionized the way some of our staff do their jobs - replacing literally hours of manual work with a few quick button clicks. Love it.
Jason D Polete

  

License benefits

Our customers get

Unconditional 30-day money-back guarantee
Secure payment
guarantee
Life-time license,
no additional fees ever
Volume discounts: the more you buy, the less you pay
1 license
for 2 computers
Free life-time support
Buy now

These companies choose Ablebits products

   

Frequently asked questions

About Merge Two Tables

  • 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

 

Excel

  • Excel 2016
  • Excel 2013
  • Excel 2010
  • Excel 2007
  • 32-bit and 64-bit
  • Desktop Excel included in Office 365
  • Desktop Excel included in Office 365 ProPlus

Windows

  • Windows 10
  • Windows 8.1
  • Windows 8
  • Windows 7
  • Windows Vista
  • Windows XP
  • 32-bit and 64-bit
Free download
7-day trial version

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.
Excel add-ins and Outlook tools - Ablebits.com