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.
  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.
  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.
  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.
  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.
  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.
  7. Click Finish and instantly get all the updates in your table.

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.

See also

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard