Word Mail Merge using Excel data

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.

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.

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.

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

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

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

Home	Excel	Access
\| Links \| Alphabetical Index \| Linking Word to Excel cell values \|

Word Mail Merge linked to an Excel table