How to Auto Populate Word Document from Excel (with Quick Steps)

While writing a document you may run into scenarios where you need to import data from different files. For this purpose, importing from spreadsheets software like Excel is a very common scenario. Of course, there is this process where you manually copy and paste it into the Word file from the Excel one. But this article will focus on how to auto populate a Word document from Excel.


Download Practice Workbook

Download the workbook containing the dataset used to demonstrate the steps in this article and try the process yourself while you go through the article.

Here is the Word file, in case you need the reference.


Step-by-Step Procedure to Auto Populate Word Document from Excel

In order to do this you need an Excel file to import data from, and the Word file you are writing your data into. I will be going through all the steps in detail so that anybody can understand regardless of their Excel knowledge. Here is a detailed step-by-step guide.


Step 1: Prepare the Excel File

Create an Excel file with the dataset if you don’t have one already. To practice, you can try out the one given in the download box above. If you have one, make sure the table/dataset starts at cell A1. For demonstration, I am using the following dataset.

You can have multiple sheets in your Excel file, but can only use one sheet to auto populate Word document from Excel.


Step 2: Go to Word Document

Now, go to your Word document and create the template before you want to automate the data. I have created the following table to put all the information separately for an easier understanding.

This is the portion that will remain unchanged for all the iterations.

Read More: How to Insert Excel Table into Word (8 Easy Ways)


Step 3: Go to the Mailings Tab

In the Word document, select the Mailings tab from your ribbon.

auto populate word document from excel


Step 4: Select the Excel Sheet as Recipient

Now, under the tab, you can find the Start Mail Merge group. Click on Select Recipients and then select Use an Existing List from the drop-down menu.

auto populate word document from excel


Step 5: Select the Excel File

A Select Data Source window will pop up. Now navigate to your Excel file and select it.


Step 6: Select the Sheet

If you have multiple spreadsheets within one Excel file, carefully select the one you want to export from. In this file, I have only one named Dataset. Then check the First row of data containing column headers if you have headers in your dataset. I have headers on my dataset so I have checked as you can see from the figure.

auto populate word document from excel

After that, click on OK.

Read More: How to Insert an Excel Spreadsheet into Word (4 Easy Methods)


Similar Readings


Step 7: Insert Merge Field

If you have completed the above steps, you are good to go to auto populate the Word document from Excel. What you have to do now is insert the merge field to insert the data in the desired position.

Let’s say you want the full name in the header. To do that you need to put in the First Name and Last Name consecutively. Follow these steps to know how to do this.

  • First, select the position you want to put it in.

  • Then go to the Mailings tab in your ribbon.
  • In the Write and Insert Field group, you can find the Insert Merge Field Click on the arrow beside it.
  • From the drop-down list, select First_Name.

auto populate word document from excel

You will have something like this.

auto populate word document from excel

  • Repeat the same process, but this time select Last_Name from the drop-down menu to enter the last name.

auto populate word document from excel

By doing so you will have something like this in your Word file.

auto populate word document from excel

In the field of <<First_Name>> all the first names and in the field of <<Last_Name>> all the last names will be iterated.


Step 8: Repeat the Above Step as Many Times as You Need

The sub-steps described in the above step can be repeated for all the data you want to auto populate Word document from Excel. For this dataset, you can auto-import ID, First Name, Last Name, Nationality, Field, and Invented/Discovered data into the Word file. You just need to import the desired one from the drop-down menu.

Filling out the table with the relevant headers will look something like this.

auto populate word document from excel


Step 9: Preview Results

To preview what this will look like, select Preview Results from the Mailings tab.

auto populate word document from excel

It will show the preview of the first one.

To preview the other ones, in the Mailing tab, under the Preview Results group, select the arrows to switch to the previous or later ones.

auto populate word document from excel

For example, if you select the right arrow, you can see this one.

By clicking the right or left arrow again, you can preview the next or previous ones the same way.

Read More: How to Generate a Word Document from an Excel Macro


Step 10: Save the Word File

Finally, save the Word file by going to the File tab and selecting the Save As command.

Note that, you can also save it as a .docx file. In that case, you have to select Yes in the warning box that pops up every time you open up the Word file warning about the document containing an SQL command.

auto populate word document from excel

When you follow all of the steps described above, You mail merge the Excel file with the Word file. For every row in the Excel dataset, the Word file creates different sheets. And in every sheet, the Word file put in the value of the column from the particular row in the template replacing the <<Column Name>> and we get our desired result.

Read More: How to Open Word Document and Save As PDF or Docx with VBA Excel


Conclusion

This was a step-by-step guide to auto populate a Word document from Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions, let us know below. For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

5 Comments
  1. This was really helpful, thank you. Is there a way for you to add data to the spreadsheet which would then automatically update the recipients in word?

    For example, very time I add a new patient to the excel spreadsheet, I need to “select recipients > use an existing list” and choose the excel file again.

    • Hello, SAF. I am glad it was helpful to you. As for your problem, you don’t need to reupload every time you enter new patient names in Excel.

      You can select “Edit Recipient List”> select the file name in the “Data Source” section and hit refresh. It will automatically update the recipients’ list up to the latest entry.

  2. Hi Abrar, thank you so much for sharing this. Do you happen to know how I can save and print each pages? Thank you in advance!

    • Hi Kheiy. Yes, there is an easy way to do that. Go to the Mailings tab on the word file and select Finish & Merge. Then select Print Documents from the drop-down. After that, you can find the option to print all pages or a particular range if you want.

  3. Very helpful thanks.

Leave a reply

ExcelDemy
Logo