Video: Merge two Excel sheets in 6 simple steps

See how you can use Merge Tables Wizard to quickly and efficiently combine data from two Excel files.

We have introduced some exciting changes to one of our best time-savers. Now the add-in can process the largest tables in seconds, it allows you to consider duplicate key records, and it offers schematic hints on each step. All these changes make the add-in clearer and more flexible than ever. You can try the updates with the latest version of Ultimate Suite.

No formulas, no redundant work. See how you can follow 6 simple steps to get the job done:

Merge Tables Wizard video transcript

Merge Tables Wizard will help you whenever you want to bring updates from one table to another.

Say, I regularly get tables listing actual products in stock from different shops. I have a master table that summarizes information about all our products. Whenever I get another file, I need to compare the product IDs, update product names and numbers, and add any missing information.

Let's take a closer look at the simple steps you follow.

  1. Once you install the add-in, you'll see its icon on Ablebits Data tab in Excel. The very first thing it offers me to do is pick my main table. This is the master table that you want to update. The add-in will pick the range with all data, but you can always change it right in the field or by clicking this button. Since undo is not available for add-ins, we always recommend creating a backup copy of the sheet.
    Select the range and decide to back up your table
  2. Next I select the lookup table that I want to check for updates. It can be in the same or in a different spreadsheet, and you can edit the range as needed.
    Select the lookup table on the second step of the Wizard
  3. Step 3 allows us to define key columns for comparison. What has to stay the same in my tables is the column with IDs. So I select it to compare the IDs in my main table to the IDs in the lookup table. I go next to choose the information to update.
    Choose what columns you want to compare
  4. When the product IDs match, I want to get new prices and product names from my lookup table, so I tick off these two columns and choose where to look for the new information.
    Select those columns you want to update
  5. Step 5 shows all remaining columns in my lookup table that I can add to my master table. As I don't have the column with the department in my product list, I'm going to introduce it by selecting it here. This means that when the IDs match, Merge Tables Wizard will add the corresponding department names into a new column.
    See if you want to add other columns to your master table
  6. You can fine-tune the merge process on the last step, let me briefly describe the options you get:
    • Non-matching rows are new records you have only in your lookup table. If I want to add new IDs to my master table, and I do, I select this box.
    • The next option allows me deal with duplicate IDs. So if there is more than one occurrence of the same ID in my lookup table, instead of ignoring it, I can add it to the end of the main table, or right under the same value.
    • I can also add a new column that will show me the status of each row as updated, non-updated, or new.
    • When you pull new information from your lookup table, you can choose to leave all existing records and fill only the empty cells.
    • Also, you can avoid copying blanks over with this setting.
    • Finally, highlighting cells is really helpful as it shows you all changes right away.

    Choose among other options on the last step

  7. Click Finish and instantly get all the updates in your table.
    Enjoy the result of merging tables in less than a minute

Of course you can use a heap of formulas to get the same results, but the add-in does a really great job of automating the steps and avoiding any errors that could creep in when laboriously writing vlookup functions. If you have any questions about the add-in, please contact us.

You may also be interested in

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)