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

Get FREE Advanced Excel Exercises with Solutions!

While writing a document you may run into scenarios where you need to import data from different files. For this purpose, importing from spreadsheet 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.


How to Auto Populate Word Document from Excel: Step-by-Step Procedures

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 easier understanding.

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

Read More: How to Put a Large Excel Table into Word


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.


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.

Read More: How to Insert Excel Table into Word


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

Read More: How to Create Multiple Word Documents 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 a 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.


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.


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.


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.


Related Articles

<< Go Back to Export Excel to Word | Export Data from Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

21 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.

      • Thank you, this was the most enjoyed reading time for the day. Very well written and so detailed . You forgot the printing part, but while reading the comment section I found your answer.

        • Hello, Nabil sellami!

          Thanks for your appreciation. To get more enjoyable articles stay in touch with ExcelDemy.

          Regards
          ExcelDemy

  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.

  4. First of all, I want to say thank you for this very helpful post, it really helped me alot
    I was wondering if the result could be all on one page and when that page is full it could go to the next page and so on

    • Hi FADYA,
      Thanks for your appreciation.
      When we auto-populate Word documents from Excel it automatically generates data in individual documents for each data.
      However, you can go to the View tab and then click on Web Layout from the Views group to see all the values in a single layout.

  5. hello,
    I wish to produce a mail merge that includes an excel spreadsheet chart that is unique for each customer e.g.include an invoice (spreadsheet) for each customer showing goods they purchase this month.
    Mailmerge is simple to achieve but to get the data for the individual customers details from within the data that contains the customer name, address.
    Appreciate any help that you can provide.
    Stuart

    • Thank you for your inquiry. Regrettably, Microsoft Word does not have the capability to perform a mail merge with Excel charts. Although there are third-party add-ins that claim to provide this functionality, we cannot vouch for their security, and therefore, we do not recommend their use.

      Perhaps, you may post this query on Microsoft Community, where experts can direct you to a trusted alternative.

      Regards,
      ExcelDemy

  6. HI,

    Thank you so much for making this tutorial, it is very useful.

    I want to make a document where I can generate a table with different information for each person (like the example above with IsaacNewton, AlbertEinstein, and MarieCurie) however when I change the results preview it changes every table to the same record (i.e. every table is IsaacNewton, then every one becomes AlbertEinstein etc. but I want them to all be their own person). Do you have any advice for how to change this?

    Thanks in advance.

    • Hello, Elena.
      Thank you for your kind words. Now, what I can understand from your query is that when you navigate through the preview results, you can only see just one individual’s information rather than everyone’s information. If you exactly follow the steps outlined above, you should be able to see all of the persons’ information in the preview result. Hence, I encourage that you carefully follow the instructions outlined above and observe whether or not your problem is resolved. If your issue persists, you can upload your file to our Exceldemy Forum(https://exceldemy.com/forum/). We will do our best to find a solution.

  7. Thank you, really helpful and easy to follow. Is there a way to save each sheet indivudually as a doc automatically?

    • Reply Avatar photo
      Mehedi Hasan Shimul Jun 4, 2023 at 3:24 PM

      Hi MIGUEL,

      We are glad, you asked the questions. It’s quite easy to import data from separate sheets and save it as separate documents. Simply, use the following code in a new module of VBA. Only, you have to change the file directory to save the doc file.

      Sub ExportWorksheetsToWord()
      Dim objWord As Object
      Dim objDoc As Object
      Dim objRange As Object
      Dim ws As Worksheet
      ' Create a new instance of Microsoft Word
      Set objWord = CreateObject("Word.Application")
      ' Make Word visible (optional)
      objWord.Visible = True
      ' Loop through each worksheet in the workbook
      For Each ws In ThisWorkbook.Worksheets
      ' Create a new Word document for each worksheet
      Set objDoc = objWord.Documents.Add
      ' Reference the range of the current worksheet
      Set objRange = ws.UsedRange
      ' Copy the range to the Clipboard
      objRange.Copy
      ' Paste the range into the Word document
      objDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
      ' Save the Word document with the worksheet name
      objDoc.SaveAs "C:\ExcelDemy\" & ws.Name & ".docx" ' Replace with your desired file path
      ' Close the Word document
      objDoc.Close
      ' Clean up
      Set objDoc = Nothing
      Next ws
      ' Close Microsoft Word
      objWord.Quit
      ' Clean up
      Set objWord = Nothing
      Set objRange = Nothing
      End Sub

      After running the code, you will see the doc files created according to your dataset.

      output seperate doc files for each worksheet data

  8. In 1990 I bought a wonderful program called Ragtime. I could create a template with a spreadsheet on page one and a Will that followed was populated by the data in the cells.

    It is now 33 years later and this can’t be done anymore? The steps here are crazy.

    Absurd. Or more likely, less profitable

    • Thanks, GENE for your comment. As you mentioned in your comment, RagTime is a specialized publishing tool with advanced layout and design capabilities. It may be a better choice for complex design and layout requirements for your mail merge documents. If you’re comfortable with a specialized tool, RagTime is a good option. However, if your needs are simple and you’re already familiar with Excel, using it for mail merging can be practical and efficient. So overall it depends on your requirements and in which platform you are used to do your work.

      Regards
      Aniruddah
      Team Exceldemy

  9. Thank you for the information. How would you do multiple line items? For example, like a purchase form.
    Qty Description Model/Catalog/Item# Unit Price Total Price
    1 Tape logic 1 tape logic 1 1 1
    3 Tape logic 2 tape logic 2 1 3

    When I followed the steps it only put in the first line item.

    • Hello RYAN,

      Thank you for sharing your experience.

      I believe you want to auto-populate Word documents from multiple Excel data like a purchase form. The reason you were able to display only one item after following the above steps is that the Mailings feature merges through only one entity for each line.

      However, it is possible to display every line of the Purchase form using 2 easy steps.
      Let’s start creating the dataset as per your requirements:

      purchase form in Excel

      Afterward, let’s create another dataset in Word and follow the steps 3 to 9 of this article. Thus, we get the first line data according to your dataset. Now, we can toggle through each line data using the toggle buttons.

      purchase template in Word and Toggle buttons

      Or, we can display the desired number of lines in a single Word file.
      Go to Finish & Merge >> Edit Individual Documents.

      edit individual

      Put the number of lines you want to populate >> OK.

      displaying lines 1 and 2

      As a result, you get all the lines of data in your Word file.

      output

      Regards,
      Yousuf Khan Shovon

  10. I would like to automatically update a word document (that contains every persons information) as the excel sheet is automatically updated from the form.

    This is for a Conflict of Interest Register.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 7, 2024 at 10:07 AM

      Hello VICTORIA SHARP

      Thank you for reaching out and sharing your requirements. Based on your comment, it seems like you’re looking for a dynamic link between your Excel sheet and Word document to ensure that any updates in the Excel sheet reflect immediately in the Word document.

      The solution provided in the article serves this purpose quite effectively. Using the Mail Merge feature in Word, you can connect your Excel sheet (containing the Conflict of Interest Register data) and your Word document.

      So, do not hesitate to follow the steps outlined in the article to establish the connection between the two. Once set up, any changes you make to the Excel sheet will automatically propagate to the Word document when you update the merge fields. This way, you can maintain an accurate and current Conflict of Interest Register without manual effort. Good luck.

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo