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

This article was created by Microsoft, the original address is:

https://support.microsoft.com/en-us/kb/318117#bookmark-93

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 “main document” with a “recipient list” to generate a set of “output documents”:

• 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 “merge fields” 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:

1. 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: 
   
   Last NameFirst NameTitleAddressCityPostal CodeCountryDavolioNancySales Representative1234 Main St. Apt. 2ASeattle14222USAFullerAndrewVice President, Sales1235 Main St.Tacoma14222USALeverlingJanetSales Representative1235 Elm St.Kirkland14222USAPeacockMargaretSales Representative1236 Main St.Redmond14222USABuchananStevenSales Manager1237 Main StLondonSW2 8MRUKSuyamaMichaelSales Representative1238 Elm St.LondonEC3 7HRUKKingRobertSales Representative1239 Main St.LondonRK2 9NPUKCallahanLauraInside Sales Coordinator1230 Elm St.Seattle14222USADodsworthAnneSales Representative1231 Elm St.LondonWG2 7LTUK
   
   
2. 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 2002back to the top

Step 2: Set Up the Main Document

1. Start Microsoft Office Word 2003 or an earlier version of Word, point to Letters and Mailings on theTools menu, and then click Mail Merge Wizard. 
   
   Start Microsoft Office Word 2007 and Microsoft Word 2010, click the Mailings tab, click Start Mail Mergein the Start Mail Merge group, and then click Step by Step Mail Merge Wizard.
2. 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).
3. 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.
4. Do one of the following:
   • Create a new sheet of labels:
     1. Click Change document layout.
     2. Click Label options.
     3. 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.
   • Start with an existing sheet of labels:
     1. Click Start from existing document.
     2. In the Start from existing box, select the document that you want, and then click Open.
     3. 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.
5. Click Next: Select recipients.

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.
4. Click Open.
5. 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

1. In the Mail Merge Recipients dialog box, select the recipients that you want to include. To do this, do any of the following:
   • 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.
   • 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.
   • 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:
     1. Click the arrow next to the column heading of the item by which you want to filter.
     2. Click any of the following:
        • (Blanks) displays all the records in which the corresponding field is blank.
        • (Nonblanks) displays all the records in which the corresponding field contains information.
        • 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.
   • The Mail Merge Recipients dialog box displays only the designated records. To display all the records again, click (All).
   NOTES:
   • 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.
   • If you have installed address validation software, you can click Validate in the Mail Merge Recipients dialog box to validate your recipients’ addresses.
2. :Click OK to return to the Mail Merge Wizard. 
   
   Word will use the recipients that you designated for the merge.
3. :Click Next: Arrange your labels.

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 “City” to have Word insert a city name, such as “Atlanta,” that is stored in the City data field. To insert merge fields, follow these steps:

1. In the main document, click where you want to insert the field.
2. Insert any of the following:
   • Address block with name, address, and other information
     1. Click Address block.
     2. 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.
        
        
     3. For help with an option, click the question mark, and then click the option.If the Match Fieldsdialog 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.
   • Greeting line
     1. Click Greeting line.
     2. Select the greeting line format, which includes the salutation, name format, and following punctuation.
     3. 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.
     4. Click OK.
     5. 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.
   • Other fields of information
     1. Click More items.
     2. Do one of the following:
        • 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.
        • To select from fields that always take data directly from a column in a database, clickDatabase Fields.
     3. In the Fields box, click the field that you want.
     4. Click Insert, and then click Close.
     5. 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.
   • 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:
     1. 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
     2. Insert the postage according to the program’s instructions.
   • 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:
     1. Click Postal bar code.
     2. 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.
     3. Repeat steps a and b for all of the fields that you want to insert.
        
        NOTES:
        • In Word 2003 and in earlier versions of Word, you cannot type merge field characters (” “) or insert them by using the Symbol command on the Insert menu. 
          
          In Word 2007 and Word 2010, you cannot type merge field characters (” “) or insert them by using the Symbol command in the Symbols group on the Insert tab.
        • 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.
   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.

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 (“” “”) 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 “34987.89” as “$34,987.89,” 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.
   
   In Word 2010, click File and then click Save As. 
2. Name the document, and then click Save.
3. 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:

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.
4. Scroll to the information that you want to edit, and make your changes.
5. 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:
  1. 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.
     
     In Word 2010, click File, click Print and then click Print,  
  2. Select the options that you want.
• If you want to print directly from the Mail Merge Wizard:
  1. In Step 6 of the Mail Merge Wizard (Complete the merge), click Print.
  2. 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 andTo boxes.
  3. 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:
   • To merge all the documents, click All.
   • To merge only the document that you see in the document window, click Current record.
   • To merge a range of documents, click From, and then type the record numbers in the From and Toboxes.
3. 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

REFERENCES

For more information about IF fields, press F1 to open Word Help, type Field Codes: IF field in the search box, and then click Search to view the topics returned. 

For more information about MERGESEQ fields, press F1 to open Word Help, type Field Codes: MERGESEQ field in the search box, and then click Search to view the topics returned. 

For more information about SET field, press F1 to open Word Help, type Field Codes: SET field in the search box, and then click Search to view the topics returned. 

For more information about general field formatting switches, press F1 to open Word Help, type general switches in the search box, and then click Search to view the topic.294686 How to use mail merge to create a list sorted by category in Word 2002290408 Frequently asked questions about mail merge in Word 2002294688 How to design and set up a mail merge address list in Word 2002294693 How to use mail merge to create a directory in Word 2002294683 How to use mail merge to create form letters in Word 2002back to the top