Merge Excel Documents

If you’ve got several Excel files that you need to merge (or worksheets in a workbook), you might be having a hard time working out how to do it. There are some useful features in Excel such as 'Consolidate' and 'Remove Duplicates' but these often don't quite hit the mark.

Subscribe Now:More:a Word document and an Excel docu. If you want to merge two tables into one and update data based on a column as below screenshot shown, you can try the Tables Merge utility of Kutools for Excel. Kutools for Excel: more than 200 handy Excel add-ins to simplify complicated tasks into a few clicks in Excel. After free installing Kutools for Excel, please do as below: 1.

The first thing to work out is what type of merge you want to do. What you need from a merge can vary from situation to situation. Maybe you just need all the rows from each spreadsheet into one, consolidated, workbook. Or maybe your needs are more complex and you need to merge spreadsheets that have different formats, de-duplicating rows as you go. Some of the variations are discussed in the following sections so read on to find what you need.

Concatenate rows from multiple spreadsheets into one

This is probably the simplest type of merge where you’d like to add the rows from all the source spreadsheets to a single output spreadsheet. In this situation, all the worksheets to be merged will have the same columns in the same order.

An example of this type of merge is where you have several spreadsheets, each containing a single worksheet with columns A to F populated with:

  • First Name
  • Last Name
  • Email
  • Age
  • Sex
  • Marital Status

Each spreadsheet has this column structure and you’d like to concatenate the rows from each into just one sheet in one spreadsheet.

If this is what you need, you have are a few options, including:

  • Copy & paste the rows from the source spreadsheets to the end of the rows
  • Use a VBA (Visual Basic for Applications) macro to merge the spreadsheets
  • Convert the spreadsheets to CSV files then concatenate them from the command line

Which option you choose really depends on how many spreadsheets you have to merge. More than a few and copy/paste becomes a real pain, as does converting them all to CSV files, so using VBA code may be your best option.

For step by step instructions on how to do each of these, including example spreadsheets, please have a look at How to merge Excel files with the same columns.

Merge spreadsheets with different columns

If the spreadsheets you’d like to merge have differing columns, just concatenating them together isn't much use as the data in the output spreadsheet will not be aligned. What you need to do in this case is map the columns from each spreadsheet onto the correct columns in the desired output spreadsheet.

As an example of this situation, imagine you have 3 spreadsheets, each containing a single worksheet:

How Do I Merge Excel Documents

  • The first spreadsheet has columns A to D populated with: First Name, Last Name, Email, Age
  • The second spreadsheet has columns A to D populated with: Email, First Name, Last Name, Sex
  • The third spreadsheet has columns A and B populated with: Email, Date Of Birth

Here, you’d like to merge the data from each spreadsheet into just one sheet in one spreadsheet with the same set of columns, lets say we want columns A to F to be:

  • First Name
  • Last Name
  • Email
  • Age
  • Sex
  • Date Of Birth

Merge Documents Excel To Word

This implies that the column mapping you need to perform is as follows:

To achieve this kind of mapping when merging spreadsheets, you will need to use VBA (Visual Basic For Applications). If you’d like to delve further into this approach, you might like to read How to merge Excel files with different columns.

Copy all sheets from separate workbooks into a single workbook

Sometimes, you have a number of separate spreadsheets and you want to copy all of the worksheets from each into single Excel workbook.

Merge Excel Documents

For example, you may have one workbook with 2 sheets, another workbook with 7 sheets in and a third with 3 sheets. The data is all related and you simply want to have all the sheets from the separate workbooks into a single workbook.

You have a couple of options for how to do this:

  • Copy & Paste the worksheets into the master spreadsheet
  • Use a VBA (Visual Basic for Applications) macro to merge the worksheets into the master spreadsheet

The copy and paste approach can be a good choice if there aren’t too many worksheets to copy but you’d get pretty tired of it when there are lots of worksheets. VBA code is probably your best bet when you have lots of worksheets to to copy.

Step by step instructions on how to do both of these options are, including example spreadsheets, are covered in How to merge worksheets from multiple Excel workbooks into one.

De-duplication of rows

Taking these approaches a step further, you may also want to de-duplicate the rows in the final spreadsheet. If duplicates can be identified as all the columns having the same values, there are several approaches you can use but if duplicates are identified based on just one or two columns (e.g. email address or first name, last name and date of birth), a good approach is to merge the spreadsheets using VBA. Your VBA code can merge the duplicate rows as each spreadsheet is processed. This is discussed in the section, De-duplicate using VBA code, below.

Remove Duplicates feature in Excel

Under the Data ribbon in the Excel menu, there’s an option called Remove Duplicates. This identifies duplicates as rows where the column values are the same and will remove all duplicate rows except one.

Select all the data in the spreadsheet then click Remove Duplicates and you're done.

Use Advanced Filters

This method uses the built in Advanced Filters functionality of Excel to hide the duplicate rows. Again, this identifies duplicates as rows where the column values are the same. To use this feature, follow the steps below:

Documents

How To Merge Two Excel Spreadsheets

  • Select all the data you want to hide duplicates in
  • In the Data ribbon in the Excel menu, select the Advanced button in the Sort & Filter section
  • Select the ‘Unique records only’ checkbox and click OK

De-duplicate using VBA code

This is the most flexible of the approaches as you can define how the de-duplication works and which values from each spreadsheet take precedence in the merged row. The concept is based on that discussed above, in Merge spreadsheets with differing columns.

  • You build a matrix mapping the columns from each spreadsheet to the output columns
  • Identify the column(s) which will be used to identify duplicates, the key column(s)
  • In the VBA code, whilst adding rows, lookup the matching key columns in the output spreadsheet. If there is a match, merge the other columns into the matching row as appropriate

More information and step by step instructions on this technique, including example code and spreadsheets, can be found in How to combine multiple Excel files into one whilst merging row data.

Martin Judd heads up Joined-up Data, a product and service which saves you the headaches of merging and de-duplicating Excel files. If you want more detail on how to merge and de-duplicate Excel files, see The Ultimate Guide on How to Merge Excel Files.

Merge Data from an Excel Workbook into a Word Document

Merging data from an Excel spreadsheet or workbook is a very basic, yet powerful tool that can save a tremendous amount of time when trying to create documents that have variable fields. Everything from envelopes and letters to name badges and table tents, data merging will eliminate the exhaustive task of typing each record by hand by pulling data from a designated Excel file. Imagine the possibilities, especially for direct mail purposes.

There are three main steps involved in building a merged document. The first and most important is your list and data within the list. You need to make sure the column headings are recognizable and properly labeled. This will allow the mapping process to go a lot smoother (I’ll talk about mapping a bit later).

Let’s say I want to create mailing labels for a “save the date” postcard. The first thing I would do is create the Excel worksheet from which my Word template will pull data from.

Merge Data from an Excel Workbook into a Word Document

1. Open Microsoft Excel or whichever spreadsheet software you use. (This works with Google Docs’ spreadsheets too). Since we’re creating mailing labels, the data we will be merging will be first name, last name, and address.

2. The first row will be the column headings, so in the first cell type the word First, in the next cell type Last and in the third cell of the first row, type Address. Likewise type City, State and Zip in the fourth, fifth and sixth cell respectively.

Now that the column headings are set, fill in the appropriate information for each of the people you are creating a label for. When finished, save your spreadsheet with a name and in a place you’ll be able to locate easily later (I prefer the desktop). Your excel spreadsheet should look like this (with the exception of the specific recipient information:

Now that the list of recipients has been created, the second step is to create the labels template.

1. Open Microsoft Word and click the “Mailings” tab at the top.

2. Next, click the labels button located on the top left of the “Mailings” menu.

3. When the “Envelopes and Labels” dialogue box opens, locate the “Options” button and click on it.

4. The next part is where we choose the labels that we’re using. For this example, I’ll be using Avery Shipping Labels, template #8163. They are 2”x4” mailing labels. So, in the “Label Options” dialogue box, select the appropriate Avery template number and hit “OK”.

5. This brings you back to the “Envelopes and Labels” dialogue box. It should now display that you have selected Avery Template #8163. Click the button that says “New Document”.

6. Now you should be looking at this:

Above is the empty labels template with text boxes inserted that correspond exactly with the specific template you are using (in this case Avery Labels 8163).

Now that the page is set up, we’re going to insert the fields that we want merged onto the mailing label. In order to do this, we need to tell Microsoft Word where to pull the data from.

1. Locate and click on the “Mailings” tab on the top of the tool bar.

2. Click on the “Select Recipients” button and select “Use Existing List”.

3. This brings up a dialogue box that allows you to select your data source from a location on your computer.

4. Locate the excel spreadsheet you just created and click the “Open” button. When you do this a little box pops up asking you which table you want to use within the excel file. Select “Sheet1$”. If you had multiple worksheets in the data file you created, you would want to select the one with the data you want merged. (see image below)

5. Now that the data source has been chosen, it’s time to insert the merge fields based on the headings you created in the data file (Name, Address, etc.). So, locate and click on the “Insert Merge Field” button.

6. Select “First” then repeat step 5(previous step) five more times until you have each of the fields inserted into the text box. It should look like this when you’re done:

4. If you’ll notice, you just inserted data fields into the word document wherever your cursor was placed. Now, what you need to do is format the text box so that the fields are arranged the way you would want the text to look on the label. Click the mouse cursor in between the “«First»” and “«Last»” fields and add a space. Likewise, place the mouse cursor in between the “«Last»” and “«Address»” fields and hit “Enter” on your keyboard. Add a newline (hit “Enter”) between “«Address»” and “«City»” fields and a space and then a comma after the “«City»” and “«State»” fields. Add a space in between the “«State»” and “«Zip»” fields and add a space. All done, your document should look like this:

5. Next, we’ll want to copy and paste those fields into the next label, so highlight the fields, copy them, then paste them into the text box to the right:

6. Now we want to insert a rule so that it chooses the next record from the data sheet. To do this, click the mouse cursor in front of the “«First»” field of the second box. Then, locate the “Rules” button on the above toolbar and select the “Next Record” rule. Your document should now look like this:

7. The next step is to copy the fields from the second text box and paste them into the remaining eight empty text boxes. So, highlight the fields in the second text box (including the “Next Record” rule), copy them, then click in the next empty text box and click paste. Click in the next empty text box and click paste again. Repeat the pasting step until all the text boxes in the document are filled with the merged fields. The document should now look like this:

Now that all the pertinent information is mapped into the word document, it’s time to mess with the aesthetics. We want to make sure everything is the right font size and that everything is centered and looks the way a mailing label should.

1. Hold down the “Ctrl” button on your keyboard and while holding that button down, press the “A” button. This is a shortcut for selecting everything on the page. This will allow us to edit multiple things at once.

2. Now that everything is selected, choose a font size that you’re happy with. I’m going to select a 12pt font. Also, I want to choose a new font style, so with everything still selected, I choose Arial.

3. Next, click anywhere inside the first text box. This will de-select everything. Now, right click your mouse in the first box and up pops a menu. Roll the cursor over “Cell Alignment” then select “Align Center Left” (it’s the first icon in the middle row of alignment icons).

4. Next, right click your mouse in the second text box and repeat previous step for each text box until the whole document is aligned properly as in the image below:

Now the template is complete and we’re ready to merge the data. Click the “Mailings” tab at the top of the tool bar. Then, select the “Preview Results” button.

Merge Excel Documents

Make sure everything looks as it should. If it all looks okay, then click the “Finish & Merge” button and select “Edit Individual Documents”. When you do this, a little box pops up asking you which records to merge. Select “All” and hit “OK”.

A new document has now been created with everything from your data list merged into the word document. You can now make any edits you want to each label incase some lengthy names or addresses created formatting issues. Once you look it over, put your Avery labels in your printer and print them out. I suggest saving the new merged document in case you have to make an edit or re-print. Otherwise if you close it out, you’ll have to re-merge.

Merge Excel Documents

I hope this tutorial has been helpful. Once you get the basic steps down, you can get creative with it. Here in the direct mail department at Vistage, I use this tool all the time for generating personalized letters for long lists of recipients.

Import Excel Data into a Word Table

This is method number 2 and it works equally well. However, this is not that advanced as it is in the first tutorial. However, you can do your job pretty well.

Excel

To get started, open Excel and enter all the details you want to import into Word table.

Select all the rows and columns > copy them.

Excel Merge Data

Open Microsoft Word and right-click on an empty space. You can find some Paste Options as shown in the following image-

Merge Multiple Excel Documents Into One

Now you can hover your mouse over some of the options and check which one is right for you. Upon selecting an option, your Excel data will be imported into Word table.

This is a guest post from Athan Anas is the Direct Mail Coordinator at Vistage International, an executive coaching organization that helps CEO members build better companies through unique business coaching and executive development opportunities.