MeadInKent
| Links | Books | Alphabetical Index | Linking Word to Excel cell values |
book

Word Mail Merge linked to an Excel table

The mail merge tool in Microsoft Word allows you to print a series of standard letters or documents with particular details (such as names and addresses) changed on each one. The details which are to be merged into each letter are typically read from an external file such as an Excel spreadsheet or an Access database.

Ensure that the data in Excel is in a single table (i.e. a continuous range). If there is one single table of data located at the top of a worksheet, the Word wizard will be able to easily identify it.


- A B C D E F G
1 send_to address1 address2 address3 invoice_ref overdue_amt escalation_date
2 Chris Jenkins The Vicarage Canterbury Kent CT1 ABC 123 456.23 12/01/08
3 Paul Davies The Castle Tonbridge Kent TN1 DEF 456 123.56 17/01/08
4 Anne Rutter 24 The Avenue Ashford Kent CT7 GHJ 741 789.12 22/01/08
5 Richard Willcox 34 Church Road Sevenoaks Kent TN22 KLM 852 1951.2 27/01/08

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.

browse to find an Excel data source for Word mail merge

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
word document containing mail merge - click for full size   Word document with mail merge fields - click for full size   Word document with formatted merge fields - click for full size
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.

Word toggle field codes to edit switches

It is possible to edit any of the Word fields and apply switches (codes) which will affect how numbers and dates are formatted. To edit a field, right click on it and choose the Toggle Field Codes option. It is then necessary to add an extra bit of code after the field name. These are in the format of \#"format code" for numbers, or \@"format code" for dates. For further information on Field Code Switches see Word Help.

In this example, the invoice number has been formatted as a four digit number using the code \#"0000". The currency amount has been set to two decimal places using the switch \#"#,##0.00". The date has been modified to include the month name using the format switch \@"dd MMMM yyyy". After changing a mergefield code, toggle the field code and then update the field in order to reveal the effect of the new switch code.

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

Word mail merge toolbar

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.
Document is in PDF format Click here for details about
obtaining this file

file: mail-merge1.htm Page last updated: MAR14 MeadInKent.co.uk 2014 CMIDX S5 P16 Y