How to create labels from Excel using Mail Merge

This tutorial explains how to use mail merge to print envelopes from Excel data. You will learn how to prepare your Excel address list for mail merge, set up the Word document, make custom labels, print them and save the file for later use.

Last week we started to look into the capabilities of Mail Merge. Today let's see how you can leverage this feature to mail merge and print labels from Excel's address list.

• Make address labels from Excel
  • Prepare the address list in Excel
  • Configure a Word mail merge document
  • Connect to Excel's address list
  • Select recipients
  • Arrange your mailing labels
  • Preview the labels
  • Print envelopes
  • Save the labels for later use
• Third party add-ins to print labels from Excel

How to make labels from Excel

If you've had a chance to look through Mail Merge from Excel to Word article, a large part of this tutorial will be already familiar to you because printing envelopes from Excel is yet another variation of the Mail Merge. Whatever intricate and intimidating the task may sound, it actually boils down to 7 basic steps.

Now let's have a closer look at each step. I will be using Microsoft Excel 2013 in this example, but the steps are absolutely identical in Excel 2019, Excel 2016 and Excel 2010 and very similar in Excel 2007.

Step 1. Prepare the address list in Excel for mail merge

In essence, when you mail merge labels from Excel to Word, the column headers of your Excel sheet become placeholders in a Word document denoting the mail merge fields. Each placeholder corresponds to one entry such as first name, last name, salutation, city etc.

Microsoft Word will be pulling out the information from your Excel columns and placing it into the corresponding merge fields in this way:

So, before starting the Mail Merge, invest some time in setting up your Excel spreadsheet to ensure it is properly structured for the merging purpose. This will make it easier for you to arrange, review and print your mailing labels in Word and you save more time in the long run.

Tips:

1. Give clear and self-explanatory names to your Excel columns, for example First Name, Middle Name, Zip Code and so on. In this way you will be able to quickly locate the right column in a Word mail merge document.
2. Separate the recipients' information into very small pieces. For example, you'd better create 3 separate columns for titles, first names and last names rather than a single Name column.
3. Make sure your Excel spreadsheet does not contain any blank rows or columns. When doing a mail merge, empty rows may mislead Microsoft Word and as a result it may merge only some of the entries believing it has already reached the end of your address list.
4. To correctly mail merge zip codes and any other numbers, please follow the recommendations provided in How to format mail merge numbers and dates. This will ensure that all leading zeros in your postal codes will remain intact.
5. If you create a mailing list by importing information from a .csv or a .txt file, then use the Text Import Wizard. You will find the detailed guidance in this article - How to import CSV to Excel.
6. To help Microsoft Word locate your address list during mail merge, you can give it a name in your Excel sheet in this way:
   • Select the address list in your Excel spreadsheet, including column headers.
   • Switch to the Formulas tab >Defined Names group > Define Name.
     
   • Type a name for the address list in the Name box and click OK. Note that neither spaces nor hyphens between words are allowed, you can use an underscore _ instead.

Step 2. Set up the mail merge document in Word

Now you need to configure the main document for the mailing labels you want to print. The good news is that you have to do this only once for all the labels in the mail merge. You can also choose some other content (text or graphics) to include in each label, e.g. your company logo or return address.

You can configure the label main document in 2 ways:

• Using the Mail Merge Wizard. It provides the step-by-step guidance which may be helpful for beginners.
• Using the Mailings ribbon group. If you are pretty comfortable with the mail merge feature or prefer working with the ribbon, you can proceed in this way.

Now let's go ahead and configure your main label document. We will be using the step-by-step wizard in this tutorial to make the learning curve easier.

1. In Microsoft Word, create a new document or open an existing one. The currently opened document will become your label main document.
   Note. If your company already has a package of label sheets from a certain manufacturer, e.g. Avery, then you need to match the dimensions of your Word mail merge document with the dimensions of the label sheets you are going to use.
2. Head over to the Mailings tab > Start Mail Merge group and click Step by Step Mail Merge Wizard. The Mail Merge pane will open in the right part of the screen.
3. Select Labels and click the Next: Starting document link near the bottom of the Mail Merge pane.
   
4. Decide where you want to set up your mailing labels - in the current document, some other existing document or a new document.

   If you want to create a new sheet of mailing labels, select the Change document layout radio button and then click Label options.
   

   Tip. If you do not see the document you want in the Start from existing box, click (More files...) and you will be able to select any other document from your computer or local network.
   
5. The Label Options dialog box will open and you select the needed options such as:
   • Under Printer information, select the printer type.
   • Under Label information, choose supplier of your label sheets.
   • Under Product number, select the number that matches the product number listed on your package of label sheets.

   For example, if you want to print Avery labels from Excel, your settings may be similar to this:
   

   Tip. For more information about the selected label package, click the Details... button in the lower part of the dialog window.
   
6. When done, click the OK button. Then click Next: Select recipients.

Step 3. Connect the address labels to your Excel mailing list

It's time to connect to your Excel address list to merge the address information with your mailing labels.

1. Choose to Use an existing list under Select recipients.
2. Click the Browse button and locate the Excel worksheet you want to use.
   
3. If you have given a name to your address list, as recommended in Step 1. Prepare the address list in Excel, select it and click OK. Otherwise, select the whole table, you will be able to refine, sort or filter the recipients to include in the mail merge later.
   

Those of you who prefer working with the ribbon can connect to an Excel worksheet by clicking Select Recipients > Use an Existing List...

Step 4. Select the recipients to include in the mail merge

The Mail Merge Recipients window will open with all the recipients from your Excel mailing list selected by default.

To exclude some of the recipients, clear a check box next to their names. When done, click OK.

When you are finished refining the recipients list, click Next: Arrange your labels.

Tips:

• To sort the recipient list by a particular column, click the column's heading. The first click sorts in ascending order, the second - in descending.
• To filter the recipient list, click the arrow next to the column heading and choose the option you want, e.g. blanks or non-blanks. You can also filter the list by some other information such as country or state.
  
• For advanced sorting or filtering, click the arrow next to the column name, and then select (Advanced...) from the drop-down list.
• You can find more options under Refine recipient list section in the lower part of the Mail Merge Recipients window, e.g. find a recipient, find duplicate items and validate recipients. The last option is available if you have any validation software installed.

Step 5. Arrange the layout of your address labels

Now you need to define the content of your labels, decide what information to included and specify how the addresses will appear on each mailing label.

You do this by adding the placeholders to your Word document that correspond to the column headers in your Excel worksheet. These placeholders are called mail merge fields. When you perform the label mail merge, the placeholders will be replaced with the information from your Excel's address list.

To arrange your mailing labels, follow these steps.

1. In the main Word document, click where you want to insert a certain field and then click the corresponding link on the Mail Merge pane. Typically, for mailing labels you would need only the Address block merge field.
   

   Another way to add the Address Block is to click the corresponding button on the Mailing tab > Write and Insert Fields group.
   

2. The Inset Address Block dialog window will open. Select the desired options, check the result under the Preview section and click OK.
3. When you are finished with refining your Address Block, click OK, and then Next: Preview your labels.

How to add missing address elements

It may happen that address elements you see under in the Preview section do not match the selected address pattern. Typically, this is the case when the column headings of your Excel sheet differ from the default Word mail merge fields.

For example, you have chosen the following address format for your labels: Salutation, First name, Last name, Suffix, but the preview shows only the First name and Last name, as you see in the screenshot below.

In this case, first off verify whether your Excel source file contains all the required data. If it does, click the Match Fields... button in the lower part of the window to help Word correctly interpret the elements from your Excel mailing list.

Click the arrow next to the required field and select the right match. For Word to remember your choice, check the Remember this matching for this set of data... check box.

Click OK and make sure the address elements are displayed correctly. As, you can see in the next screenshot, the Courtesy Tile does show up now. And since our Excel data source file does not have the Suffix column, missing Jr. or Sr. is fine : )

How to make a custom layout of mailing labels

If none of the pre-defined options in the Address Block is suitable for you, you can arrange a custom layout of your address labels in this way:

1. Click the More items... link on the Mail Merge pane or Insert Merge Field button on the Mailings ribbon group.
2. In the Inset Merge Field dialog, select the desired field and click Insert.
   

Your custom mail merge fields and the printed mailing labels may look similar to this:

Tips:

• To copy the format and layout of the first label to all other labels, click Update all labels on the Mail Merge pane.
• In addition to the mail merge fields, you can add some text or graphics to be printed on each label, e.g. your company logo or return address.
• You can change the format of a particular merge field directly in the Word document, e.g. display dates or numbers in a different way. Select the needed field, press Shift+F9 to display the field coding and add a picture switch to the merge field, as explained in Mail merge with dates, currencies and other numbers.

Step 6. Preview your mailing labels

Well, you are very close to finished : )

1. Click the left or right arrows on the Mail Merge pane or Preview Results ribbon group to see how your mailing labels will look like when printed.
   
2. If you want to change the labels' font type, font size, font color or make any other changes, switch to the Home tab and design the currently previewed label to your liking. The edits will be automatically applied to all other labels.
3. When you are satisfied with the appearance of your address labels, get back to the Mailings tab and click Next: Complete the merge.

Tips:

• To find and preview a certain label, click Find a recipient... link and type your search criteria in the Find Entry box.
• To make changes in the address list, click the Edit recipient list... link and refine your mailing list.

Step 7. Print address labels

You are ready to print the address labels from your Excel mailing list.

1. Simply click Print... on the Mail Merge pane or Finish & Merge > Print documents on the ribbon.
   
2. Decide whether to print all of your mailing labels, current or selected ones.

Step 8. Save the labels for later use (optional)

If you may want to print the same labels at some point in the future, you have two options:

1. Save the mail merge document connected to the Excel address list.

   Click the Save button or press Ctrl+S to save the Word document in the usual way. The mail merge document will be saved "as-is", connected to your Excel source file. If you make any changes in the Excel mailing list, your address labels in Word with be updated automatically.

   The next time you open the document, Word will ask you whether you want to merge the information from the Excel data file. Click Yes to merge labels from Excel to Word.
   

   If you click No, Word will break the connection between the document and Excel data file, and replace the mail merge fields with the information from the first record.

2. Save merged labels as text.

   If you want to save the merged labels as usual text, click the Edit individual labels... on the Mail Merge pane.
   

In the Merge to New Document dialog box, specify what labels you want to merge. When you click OK, Word will open the merged labels in a separate document. You can make any edits there and then save the file as a usual Word document.

Third party add-ins to print mailing labels from Excel

There exist a number of third-party add-ins purposed for printing address labels. Some of them are manufactured by label sheets' suppliers and designed especially for formatting and printing their label packages.

Let's have a quick look at one of such add-ins - Avery Wizard for Microsoft Office. As its name suggests, this tool is intended for printing Avery labels. The wizard will help you print all kinds of Avery labels from Excel, Word, Access and Outlook.

You can download a free trial using the above link. To be able to download, you will need to provide them some basic information such as your name and email address. But this seems to be the only complexity. The installation goes smoothly and once completed, you will see the new AVERY tab on your ribbon. Clicking the Avery Wizard button opens a nice looking wizard that will walk you through the process:

One more example of the printing labels software is Brother P-touch Add-In. This tool might help you print labels using the Ptouch printer. However, the significant limitation of this add-in is that it currently supports only 32-bit versions of Office 2010 and 2013.

And this is the end of our printing labels tutorial. Thanks a lot to everyone who has read to the end! If you have any questions or run into problems when printing labels from Excel, do not hesitate to leave a comment here, we will be happy to help!

You may also be interested in

• How to change case in Excel.
• How to calculate variance – sample & population variance formula
• How to share Excel file for multiple users