Jun 13 2017

Word Mail Merge using Excel data #microsoft #excel, #mail #merge, #word, #mergefield, #field #switches, #letters,


Word Mail Merge linked to an Excel table

A table of data saved in an Excel Spreadsheet which will be a source of field variables for Mail Merge

Note that the mail merge wizard and tools differs slightly in different versions of Word. This example was produced using Word 2010.

Within Word create the outline of a letter which you wish to reprint many times with different field variables. Leave spaces where you wish to add field variables. Start the Word Wizard using the ribbon option [Mailings] Start Mail Merge | Step by Step Mail Merge Wizard. |. It will then guide you through 6 steps of linking the data and inserting the fields.

When you are prompted to select recipients, choose the option to browse for an existing list. Select the appropriate spreadsheet from the Dialog box and the worksheet containing the table. Note that it is important to identify whether or not the first row of the table contains field names (such as in row 1 in the example above).

Place the cursor in your document where you wish to insert a field. Although Word offers some potentially useful tools for compiling address or greeting line blocks, it is relatively simple to use the More Items. option. This will allow you to pick from your list of field variables and drop it into your document. Repeat this with each of the field variables.

Click on any of these images to view a full size PDF version of the page

Letter with merge fields inserted

Preview letter and show data from Excel

Preview letter after reformatting fields

The mail merge process can leave you with fields in unexpected formats. Numbers may appear with lots of additional decimal places due to the peculiar way in which Excel handles values (such as in the 2nd example above). Dates may be have the month and days in the wrong order (depending on Windows international settings) and you may wish to include weekday or month names rather than numbers. The 3rd example (above) shows how the document has been improved by formatting some of the merged fields.

When editing your document, the wizard can be restarted at any time using the same menu option. In older versions of Word (up to 2003) it is helpful if the Mail Merge toolbar is revealed. ( View | Toolbars | Mail Merge ).

Mail merge is not only useful for letters. It can be used to update a document with a set of values which regularly change. There is an alternative method of doing this for pages based on a single record. It is possible to copy and Paste Link individual Excel cells into a Word document and they will automatically update whenever the underlying spreadsheet is modified. Excel charts and tables can also be linked in the same manner. [Click here for instructions on linking Excel and Word].

Download an Excel Functions Guide accompanied
by Excel worksheets. This page however is NOT
currently included.

Written by admin

Leave a Reply

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