Microsoft KB Archive/318117

= How to use addresses from an Excel worksheet to create labels in Word =

Article ID: 318117

Article Last Modified on 12/5/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Word 2007
 * Microsoft Office Word 2003
 * Microsoft Word 2002 Standard Edition

-



This article was previously published under Q318117



IN THIS TASK
SUMMARY Step 1: Set Up the Excel Data File Step 2: Set Up the Main Document Step 3: Specify the Excel Data Source Step 4: Select the Recipients Step 5: Arrange the Content of Your Labels
 * Insert Merge Fields
 * Change the Format of the Merged Data
 * Format by Using Field Codes

Step 6: Save the Document Step 7: Preview the Labels and Fine-Tune the Recipient List Step 8: Complete the Merge
 * Personalize Individual Labels
 * Print the Sheet of Labels
 * Save the Sheet of Labels for Later Use

REFERENCES



SUMMARY
You can use the Mail Merge feature in Microsoft Word to create and print labels for a mass mailing by using data from a Microsoft Excel worksheet. This article contains instructions that you can use to create such a list.

When you use the Word mail merge feature, Word merges a &quot;main document&quot; with a &quot;recipient list&quot; to generate a set of &quot;output documents&quot;:
 * The main document contains the basic text that is the same in all of the output documents. It may contain a letterhead, text, and instructions in &quot;merge fields&quot; for inserting text (such as recipient names and addresses) that varies from one output document to another.
 * The recipient list is a database-for example, a Microsoft Access 2002 database file or an Excel workbook-that contains the data that is to be merged into the output documents. This database is typically a list of names, addresses, and phone numbers.
 * The output documents are the result of the mail merge. The text in an output document can be the same in all output documents, but you can apply formatting to specific documents.

back to the top

Step 1: Set Up the Excel Data File
Before you proceed with the Mail Merge Wizard, make sure that your Excel worksheet is well structured for this purpose. Note the following requirements for the data table:
 * The first row should contain field names for each column. For example, Title, Salutation, First Name, Middle Name, Last Name, Address1, and Address2.
 * Each field name must be unique.
 * Each row must provide information about a particular item. In a mailing list, for example, each row may include information about a particular recipient.
 * The table must not have blank rows.

To arrange your Excel data file, follow these steps:  Create your Excel data file, and arrange it by using the fields that you want to use for your label, as shown in the following sample data file:

 After you create your Excel data file, save it, and then close the data file.

For more information about how to create a mail-merge address list, click the following article number to view the article in the Microsoft Knowledge Base:

294688 How to design and set up a mail merge address list in Word 2002

back to the top

Step 2: Set Up the Main Document
 Start Microsoft Office Word 2003 or an earlier version of Word, point to Letters and Mailings on the Tools menu, and then click Mail Merge Wizard.

Start Microsoft Office Word 2007, click the Mailings tab, click Start Mail Merge in the Start Mail Merge group, and then click Step by Step Mail Merge Wizard. Under Select document type, click Labels.

The active document becomes the main document (the document that contains the text and graphics that are the same for each version of the merged document, for example, the return address or salutation in a form letter). Click Next: Starting document.

NOTE: If you are familiar with the mail merge feature or you prefer to work without the wizard, you can use the Mail Merge toolbar. Do one of the following: <ul> Create a new sheet of labels:  Click Change document layout.</li> Click Label options.</li> In the Label Options dialog box, select the options that you want, such as the label type and size, and then click OK.

For help with an option, click the question mark, and then click the option.</li></ol> </li> Start with an existing sheet of labels:  Click Start from existing document.</li> In the Start from existing box, select the document that you want, and then click Open.</li> If you do not see the document, click More files, and then click Open. In the Open dialog box, locate the document that you want, and then click Open.

Word displays the document in the document window. If you decide to use a different label document instead, Click Start from existing document, and then select a different document. If you want to change the layout, click Change document layout, and then click Label options.</li></ol> </li></ul> </li> Click Next: Select recipients.</li></ol>

back to the top

Step 3: Specify the Excel Data Source

 * 1) Under Select recipients, click Use an existing list.
 * 2) Click Browse.
 * 3) In the Select Data Source dialog box, locate and click the Excel worksheet that you want to use.

By default, Word opens the My Data Sources folder.
 * 1) Click Open.
 * 2) If your Excel worksheet has information on multiple tabs, you have to select the tab that contains the information that you want, and then click OK. All of the entries in the data source appear in the Mail Merge Recipients dialog box, where you can refine the list of recipients to include in the merge.

back to the top

Step 4: Select the Recipients
 In the Mail Merge Recipients dialog box, select the recipients that you want to include. To do this, do any of the following: <ul> Use the check boxes to designate recipients.

This method is most useful if your list is short. Select the check boxes next to the recipients that you want to include, and clear the ones next to the recipients that you want to exclude.

NOTE: If you know that you want to include most of the list in your merge, click Select All and then clear particular records. Similarly, if you want to include only a few records in the list, click Clear All, and then select the records that you want.</li> Sort items in the list.

This is useful if you want to see items in alphabetical or numeric order. Click the column heading of the item that you want to sort by. For example, if you want to display the list alphabetically by last name, click the Last Name column heading.</li> <li>Filter items in the list.

This is useful if the list contains records that you know that you do not want to see or include in the merge. After you have filtered the list, you can use the check boxes to include and exclude records as described in the previous section. To filter the list, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click the arrow next to the column heading of the item by which you want to filter.</li> <li>Click any of the following: <ul> <li>(Blanks) displays all the records in which the corresponding field is blank.</li> <li>(Nonblanks) displays all the records in which the corresponding field contains information.</li> <li>If your data source contains records that share the same information, and there are ten or fewer unique values in the column, you can filter by specific information. For example, if there are multiple addresses that list Australia as the country/region, you can filter on Australia.</li></ul> </li></ol> </li> <li>The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All).</li></ul>

NOTES: <ul> <li>For advanced sorting and filtering, click the arrow next to any column name, and then click (Advanced). Use the Filter Records tab and the Sort Records tab to set up the sorting or filtering query that you want.</li> <li>If you have installed address validation software, you can click Validate in the Mail Merge Recipients dialog box to validate your recipients' addresses.</li></ul> </li> <li>:Click OK to return to the Mail Merge Wizard.

Word will use the recipients that you designated for the merge.</li> <li>:Click Next: Arrange your labels.</li></ol>

back to the top

Step 5: Arrange the Content of Your Labels
To arrange the content of your labels, follow these steps.

Insert Merge Fields
Insert merge fields where you want to merge names, addresses, and other information such as a postal bar code. For example, insert the merge field &quot;City&quot; to have Word insert a city name, such as &quot;Atlanta,&quot; that is stored in the City data field. To insert merge fields, follow these steps: <ol> <li>In the main document, click where you want to insert the field.</li> <li>Insert any of the following: <ul> <li>Address block with name, address, and other information <ol style="list-style-type: lower-alpha;"> <li>Click Address block.</li> <li>In the Insert Address Block dialog box, select the address elements that you want to include and the formats that you want, and then click OK. For help with an option, click the question mark, and then click the option.

</li> <li>For help with an option, click the question mark, and then click the option.If the Match Fields dialog box appears, Word may not have been able to find some of the information it needs for the address block. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.</li></ol> </li> <li>Greeting line <ol style="list-style-type: lower-alpha;"> <li>Click Greeting line.</li> <li>Select the greeting line format, which includes the salutation, name format, and following punctuation.</li> <li>Select the text that you want to appear in cases where Word cannot interpret the recipient's name, for example, when the data source contains no first or last name for a recipient, but only a company name.</li> <li>Click OK.</li> <li>If the Match Fields dialog box appears, Word may not have been able to find some of the information it needs for the greeting line. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.</li></ol> </li> <li>Other fields of information <ol style="list-style-type: lower-alpha;"> <li>Click More items.</li> <li>Do one of the following: <ul> <li>To select from address fields that will automatically map to corresponding fields in your data source, even if the data source's fields do not have the same name as your fields, click Address Fields.</li> <li>To select from fields that always take data directly from a column in a database, click Database Fields.</li></ul> </li> <li>In the Fields box, click the field that you want.</li> <li>Click Insert, and then click Close.</li> <li>If the Match Fields dialog box appears, Word may not have been able to find some of the information it needs to insert the field. Click the arrow next to (not available), and then select the field from your data source that corresponds to the field required for the mail merge.

NOTE: If you insert a field from the Database Fields list, and then later switch to a data source that does not have a column with the same name, Word will not be able to insert that field information into the merged document.</li></ol> </li> <li>Electronic postage

To add electronic postage, you must first install an electronic postage program, such as one that you purchased from a service on the World Wide Web. To use electronic postage, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click Electronic postage.

If you do not have an electronic postage program installed, Word prompts you to install one and offers to connect to the following Microsoft Web site:

Microsoft Office Web

</li> <li>Insert the postage according to the program's instructions.</li></ol> </li> <li>To add electronic postage, you must first install an electronic postage program, such as one that you purchased from a service on the World Wide Web.

You must select a label or envelope type that supports the POSTNET bar code. To use the Postal bar code, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Click Postal bar code.</li> <li>In the Insert Postal Bar Code dialog box, select the appropriate address fields.

NOTE: The Postal bar code option appears only if you are using the U.S. language version of Word.</li> <li>Repeat steps a and b for all of the fields that you want to insert.

NOTES: <ul> <li>In Word 2003 and in earlier versions of Word, you cannot type merge field characters (&quot; &quot;) or insert them by using the Symbol command on the 'Insert' menu.

In Word 2007, you cannot type merge field characters (&quot; &quot;) or insert them by using the Symbol command in the Symbols group on the Inert tab.</li> <li>If the merge fields appear inside braces, such as { MERGEFIELD City }, then Word is displaying field codes instead of field results. This does not affect the merge, but if you want to display the results instead, right-click the field code, and then click Toggle Field Codes on the shortcut menu.</li></ul> </li></ol> </li></ul>

Note In Word 2003 and in earlier versions of Word, you can also use the Mail Merge toolbar to insert merge fields, work with your mail merge main document, or to run a mail merge. To display the Mail Merge toolbar, point to Letters And Mailings on the Tools menu, and then click Show Mail Merge Toolbar. The Mail Merge toolbar provides additional commands not included in the Mail Merge wizard task panes. For example, you can use the Insert Word Field drop-down menu on the Mail Merge toolbar to insert Word fields for controlling the merge process (for example, an IF field that inserts text only if a particular merge field has a specified value). Or, you can click Check For Errors to have Word run the mail merge and report any errors contained in the main document.</li></ol>

back to the top

Change the Format of the Merged Data
To format merged data, you must format the merge fields in the main document. Do not format the data in the data source, because its formatting is not retained when you merge the data into the document. To change the format of the merged data, follow these steps:
 * 1) In the main document, select the field containing the information that you want to format, including the merge field characters (&quot;&quot; &quot;&quot;) that enclose it.
 * 2) In Word 2003 and in earlier versions or Word, click Font on the Format menu, and then select the options that you want.

In Word 2007, on the Home tab, click Font in the Font group to open the Font dialog box, and then select the options that you want.

Format by Using Field Codes
To control other aspects of formatting, press ALT+F9 to display field codes, and then add switches to the merge fields. When you work with fields, a switch is special instruction that causes a specific action to occur. Generally, you add a switch to a field to modify a result.

For example:
 * 1) To display the number &quot;34987.89&quot; as &quot;$34,987.89,&quot; add the Numeric Picture switch (\#).
 * 2) To print client names in uppercase letters, add the Format switch (\*).
 * 3) To make sure that the merged information has the same font and point size that you apply to the merge field, add the \* Charformat switch.

To copy the format and layout of the first label to all the other labels on the page, click Update all labels.

For example, in the sample database shown earlier in this article, if you insert only the AddressBlock field, and then click Update all labels, the page should appear similar to the following:

<<AddressBlock>><<Next Record>><<AddressBlock>>

<<Next Record>><<AddressBlock>><<Next Record>><<AddressBlock>>

back to the top

Step 6: Save the Document
After you have completed the main document and inserted all of the merge fields, save the document before you proceed.
 * 1) In Word 2003 and in earlier versions of Word, click Save As on the File menu.

In Word 2007, click the Microsoft Office Button, and then click Save As.
 * 1) Name the document, and then click Save.
 * 2) Click Next: Preview your labels.

back to the top

Step 7: Preview the Labels and Fine-Tune the Recipient List
When the wizard displays the Step 5 Mail Merge task pane, it replaces each of the merge fields in the main document with the actual text from the first entry of the recipient list, so that you can see how your first output document will look.

For example, if you continue to use the sample database shown earlier, after you click Next: Preview your labels, the first page should appear similar to the following: <pre class="fixed_text">Vice President, Sales Andrew Fuller    Sales Representative Anne Dodsworth 1235 Main St                           1231 Elm St. Tacoma                                  London Sales Representative Janet Leverling    Inside Sales Coordinator Laura Callahan 1235 Elm St. 1230 Elm St. Kirkland                                Seattle To preview additional entries, do either of the following:
 * To preview the items in order, click the left or right arrow buttons.

Each record is previewed in the first label on the sheet.
 * To locate and preview a specific item, click Find a recipient, and then enter the search criteria in the Find Entry dialog box.

To fine-tune the recipient list, for example, to exclude a recipient:
 * 1) Click Edit recipient list, and then make your changes in the Mail Merge Recipients dialog box.
 * 2) Click Next: Complete the merge.

back to the top

Step 8: Complete the Merge
To complete the merge, do any of the following:

Personalize Individual Labels
To personalize individual labels, complete the merge, and then edit the information that you want in the resulting merged document.
 * 1) Click Edit individual labels.
 * 2) In the Merge to New Document dialog box, select the records that you want to merge.
 * 3) Click OK.

Word creates and opens a new merged document. Your main document also remains open, and you can switch back to it if you want to make a change to all the items.
 * 1) Scroll to the information that you want to edit, and make your changes.
 * 2) Print or save the document just as you would any regular document.

back to the top

Print the Sheet of Labels
To print the sheet of labels, do either of the following:
 * If you personalized the items and the merged document is active:
 * In Word 2003 and in earlier versions of Word, click Print on the File menu.

In Word 2007, click the Microsoft Office Button, point to Print, and then click Print.
 * Select the options that you want.
 * If you want to print directly from the Mail Merge Wizard:
 * In Step 6 of the Mail Merge Wizard (Complete the merge), click Print.
 * In the Merge to Printer dialog box, do one of the following, and then click OK:
 * To print all the documents, click All.
 * To print the document that you see in the document window, click Current record.
 * To print a range of documents, click From, and then type the record numbers in the From and To boxes.
 * In the Print dialog box, select the options that you want.

back to the top

Save the Sheet of Labels for Later Use
If you want to edit merged labels or save them for later use, you can collect them into a single document.
 * 1) Click Edit individual labels.
 * 2) In the Merge to a New Document dialog box, do one of the following, and then click OK:
 * 3) * To merge all the documents, click All.
 * 4) * To merge only the document that you see in the document window, click Current record.
 * 5) * To merge a range of documents, click From, and then type the record numbers in the From and To boxes.
 * 6) Word opens a single new document that contains all the individual labels. You can then save the document for later use, just as you would any regular document.

back to the top