Merging Excel and Word Data List 2019

Merging Word and Excel Data List 2019

Creating a data list in Excel may seem tedious at first, let alone needing to merge that data into Word for mailing! But, trust me! Once your first one is created and produces you will never do it any other way again! Just take one step at a time

and you will be glad you did! This document was created using Excel, Microsoft Office 365 in 2019.

Creating Your Excel Data List

  1. In Excel, create a new workbook and set up your mailing list in the first worksheet of the document. Using Line 1, set up a header row with categories (Name, Address, City, etc.). Separate out each piece of information into separate columns for ease of use and personalization later. Each "field" or header must only be one word and have no punctuation.

    Excel Data Sample and Sheet Name
  2. At the bottom, double click on the tab titled ‘Sheet 1’ and personalize the name to easily recognize it later.
  3. Save the document and close.

Create the Word Template

  1. In Word, create a new document.
  2. Choose Mailings in the menu and click on the mailing type at the left. All items are adjustable to fit your needs. {For postcards, I use ‘Envelopes’. For more options, chose ‘Start Mail Merge.’)

Choose Mailings

 

This Example Uses Envelopes

3. a. A window should pop up to set up your size and layout. With 8.5x5.5 postcards, I use Custom to enter size, and then 5” from left and 2.5” from the top.

b. Click ‘Font…’ to design text as you like. Leave the Delivery Address blank and enter a return address if you want it to print on your card. I designed and had my postcards printed so my return address and logo were already there. I then check ‘Omit’ return address.

  1. Choosing Size, Font and Styles

c. Now choose the second tab in the window, Printing Options and enter the settings you need for your printer as the recommended do not always work.

d. Choose ‘OK’ and then click Add to Document.

Personalize Printing Options

 

Special Notice!!

While I am sure there is a good reason for this, I am not aware of why: there may be a second full size page visible. You do not want this when it comes time to merge. These are the only steps I know to delete that page as it will affect printing. Simply do one at a time and you will see it solved.

  1. Press Ctrl+Home to go to the top of the envelope.
  2. Click the Layout tab of the ribbon.
  3. Click the Page Setup dialog arrow in the lower right corner of the Page Setup group.

    Page Setup Dialog Arrow
  4. Click OK without changing anything. If Word wants to modify the margins, click Ignore.
  5. Press Ctrl+End to go to the blank page.
  6. Press F4 to “redo” the page setup. The blank page should now be in envelope format too.
  7. CTRL + Home
  8. Click the Home tab. Turn on Show/Hide ¶ button in the Paragraph group.
  9. Click the section break and press Delete.
  10. Turn off Show/Hide ¶.

Whew!

Now, back to our scheduled program...

It is time to tell Word to connect this document to your mailing list.

1. Choose ‘Select Recipients’, choose ‘Use an Existing List’ and locate your file we created in Excel. Double click on the needed file name and a window will pop up. You should see your personalized sheet name in the list. Additionally, check the box at the bottom of the window to let it know your first line identifies your categories.

Selecting the Data List

 

 

2. Your template format is automatically adjusted to fit your request. You now need to edit the layout. You may not see a box until you try clicking on the text boxes that are in your document. Locate and move them around, edit the size and placement, font, etc., until you are happy. Definitely refer to the USPS requirements for whichever type mailing you will have. Pay attention to the cursor for actual text placement.

The following site has wonderful easy to read design templates to help:

https://www.printingforless.com/MailLayoutGuides.html

3. Choose Insert Merge Field from the top and you should see a list that corresponds with the Headers from your mailing list.

Inserting the Field to Merge

For each field I want to enter, I find it easiest (safest) to place my cursor where I want it to appear in the text box, add one at a time, and I enter commas, hit return, etc. as I go.

 

 

4. Almost done. Double check font style, size, etc. Save the document. This is now your template; you will use it every time you want to print addresses in this layout.

Creating and Printing the Final Excel and Word Merged Document

  1. To generate the final merged document you use to print, open your Word template. Click on Mailings in the menu and go to the right to choose Finish and Merge. If you hover your mouse over ‘Edit Individual Documents’, it will say ‘Merge to New Document.’ This will open a new file generating your Merged Document.

    Creating the Final Merged Document

    Your template is still safe and may be closed.

  2. The merged document will have as many pages as you had contacts in your mailing list. Proofread it as sometimes there will be extra spaces or awkward punctuation. Make your edits.
  3. Arrange your postcards in the printer. Be SURE of the direction they need to load in so, test this ahead of time. (I keep an old postcard with directions on it, in case I forget!) Print one as a test for one last chance at making any changes 4. Print!

This is one of the greatest time savers in the world once you get the hang of it so, even if you have to read the instructions the first several times you do it, it is worth it!!

Add postage, and they're ready to go!

Kudos!

Leave a Reply

Your email address will not be published. Required fields are marked *