The tutorial explains how to do a mail merge from an Excel spreadsheet for labels. You will learn how to prepare your Excel address list, set up a Word document, make custom labels, print them and save for later use.
Last week we started to look into the capabilities of Word Mail Merge. Today let's see how you can leverage this feature to make and print labels from an Excel spreadsheet.
How to mail merge address labels from Excel
If you've had a chance to read our Mail Merge tutorial, a larger part of the process will be familiar to you because making labels or envelopes from Excel is yet another variation of the Word Mail Merge feature. Whatever intricate and intimidating the task may sound, it boils down to 7 basic steps.
Below, we'll take a closer look at each step using Microsoft 365 for Excel. The steps are essentially the same in Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2010, and very similar in Excel 2007.
Step 1. Prepare Excel spreadsheet for mail merge
In essence, when you mail merge labels or envelopes from Excel to Word, the column headers of your Excel sheet are transformed into mail merge fields in a Word document. A merge field can correspond to one entry such as first name, last name, city, zip code, etc. Or, it can combine several entries, for example the «AddressBlock» field.
Microsoft Word will be pulling out the information from your Excel columns and placing it into the corresponding merge fields in this way:
Before starting a mail merge, invest some time in setting up your Excel spreadsheet to ensure it is properly structured. This will make it easier for you to arrange, review and print your mailing labels in Word and save more time in the long run.
Here are a few important things to check:
- Create one row for each recipient.
- Give clear and unambiguous names to your Excel columns such as First Name, Middle Name, Last name, etc. For address fields, use the full words such as Address, City, State, Postal or Zip code, Country or Region.
The screenshot below shows a list of the Address block fields used by Word. Giving identical names to your Excel column will help Mail Merge to automatically match the fields and save you the trouble of mapping the columns manually.
- Split the recipient information into very small pieces. For example, instead of a single Name column, you'd better create separate columns for salutation, first name and last name.
- Format the Zip code column as text to retain leading zeros during a mail merge.
- Make sure your Excel sheet does not contain any blank rows or columns. When doing a mail merge, empty rows may mislead Word, so it will merge only part of the entries believing it has already reached the end of your address list.
- To make it easier to locate your mailing list during the merge, you can create a defined name in Excel, say Address_list.
- If you create a mailing list by importing information from a .csv or a .txt file, be sure to do that right: How to import CSV files into Excel.
- If you plan to use your Outlook contacts, you can find the detailed guidance here: How to export Outlook contacts to CSV.
Step 2. Set up mail merge document in Word
With the Excel mailing list ready, the next step is to configure the main mail merge document in Word. The good news is that it's a one-time setup - all labels will be created in one go.
There are two ways to do a mail merge in Word:
- Mail Merge Wizard. It provides step-by-step guidance which may be helpful for beginners.
- Mailings tab. If you are pretty comfortable with the mail merge feature, you can use the individual options on the ribbon.
To show you an end-to-end process, we are going to mail merge address labels using the step-by-step wizard. Also, we'll point out where to find the equivalent options on the ribbon. Not to mislead you, this information will be provided in (brackets).
- Create a Word document. In Microsoft Word, create a new document or open an existing one.
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.
- Start mail merge. Head over to the Mailings tab > Start Mail Merge group and click Step by Step Mail Merge Wizard.
- Select document type. The Mail Merge pane will open in the right part of the screen. In the first step of the wizard, you select Labels and click Next: Starting document near the bottom.
(Or you can go to the Mailings tab > Start Mail Merge group and click Start Mail Merge > Labels.)
- Choose the starting document. Decide how you want to set up your address labels:
- Use the current document - start from the currently open document.
- Change document layout - start from a ready-to-use mail merge template that can be further customized for your needs.
- Start from existing document - start from an existing mail merge document; you will be able to make change to its content or recipients later.
As we are going to set up a mail merge document from scratch, we select the first option and click Next.
Tip. If the Use the current document option is inactive, then select Change document layout, click the Label options… link, and then specify the label information.
- Configure label options. Before proceeding to the next step, Word will prompt you to select Label Options such as:
- Printer information - specify the printer type.
- Label information - define the supplier of your label sheets.
- Product number - pick the product number indicated on a package of your label sheets.
If you are going to print Avery labels, your settings may look something like this:
Tip. For more information about the selected label package, click the Details… button in the lower left corner.
When done, click the OK button.
Step 3. Connect to Excel mailing list
Now, it's time to link the Word mail merge document to your Excel address list. On the Mail Merge pane, choose the Use an existing list option under Select recipients, click Browse… and navigate to the Excel worksheet that you've prepared.
(Those of you who prefer working with the ribbon can connect to an Excel sheet by clicking Select Recipients > Use an Existing List… on the Mailings tab.)
The Select Table dialog box will pop up. If you have given a name to your mailing list, select it and click OK. Otherwise, select the entire sheet - you will be able to remove, sort or filter recipients later.
Step 4. Select recipients for mail merge
The Mail Merge Recipients window will open with all the recipients from your Excel mailing list selected by default.
Here are some of the actions you can perform to refine your address list:
- To exclude a particular contact(s), clear a check box next to their name.
- To sort the recipients by a certain column, click the column's heading, and then choose to sort either ascending or descending.
- To filter the recipient list, click the arrow next to the column heading and pick the desired option, e.g. blanks or non-blanks.
- For advanced sorting or filtering, click the arrow next to the column name, and then select (Advanced…) from the drop-down list.
- A few more options are available in the Refine recipient list section near the bottom.
When the recipient list is all set, click Next: Arrange your labels on the pane.
Step 5. Arrange layout of address labels
Now, you need to determine what information to include in your mailing labels and decide on their layout. For this, you add placeholders to the Word document, which are called mail merge fields. When the merge is finished, the placeholders will be replaced with the data from your Excel's address list.
To arrange your address labels, follow these steps:
- In your Word document, click where you want to insert a field, and then click the corresponding link on the pane. For mailing labels, you'd normally need only the Address block.
- In the Insert Address Block dialog box, select the desired options, check the result under the Preview section and click OK.
When you are finished with the Address Block, click OK.
The «AddressBlock» merge field will appear in your Word document. Note that it's just a placeholder. When the labels are printed out, it will be replaced with the actual information from your Excel source file.
When you are ready for the next step, click Next: Preview your labels on the pane.
Step 6. Preview mailing labels
Well, we are very close to the finish line :) To see how your labels will look like when printed, click the left or right arrow on the Mail Merge pane (or the arrows on the Mailings tab, in the Preview Results group).
- To change label formatting such as font type, font size, font color, switch to the Home tab and design the currently previewed label to your liking. The edits will be automatically applied to all other labels. If they are not, click the Update all labels button on the Mailings tab, in the Write & Insert Fields group.
- To preview a certain label, click Find a recipient… link and type your search criteria in the Find Entry box.
- To make changes to the address list, click the Edit recipient list… link and refine your mailing list.
When you are satisfied with the appearance of your address labels, click Next: Complete the merge.
Step 7. Print address labels
You are now ready to print mailing labels from your Excel spreadsheet. Simply click Print… on the pane (or Finish & Merge > Print documents on the Mailings tab).
And then, indicate whether to print all of your mailing labels, the current record or specified ones.
Step 8. Save labels for later use (optional)
If you may want to print the same labels at some point in the future, you have two options:
- Save the Word mail merge document connected to the Excel sheet
Save the Word document in the usual way by clicking the Save button or pressing the Ctrl + S shortcut. The mail merge document will be saved "as-is" retaining the connection to your Excel file. If you make any changes to the Excel mailing list, the labels in Word with be updated automatically.
The next time you open the document, Word will ask you whether you want to pull the information from the Excel sheet. Click Yes to mail merge labels from Excel to Word.
If you click No, Word will break the connection with the Excel database and replace the mail merge fields with the information from the first record.
- Save merged labels as text
In case you wish to save the merged labels as usual text, click the Edit individual labels… on the Mail Merge pane. (Alternatively, you can go to the Mailings tab > Finish group and click Finish & Merge > Edit individual documents.)
In the dialog box that pops up, specify which labels you want to edit. 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.
How to make a custom layout of mailing labels
If none of the predefined options in the Address Block is suitable for your needs, you can create a custom layout of your address labels. Here's how:
- When arranging the labels layout, place the cursor where you want to add a merge field.
- On the Mail Merge pane, click the More items… link. (Or click the Insert Merge Field button on the Mailings tab, in the Write & Insert Fields group).
- In the Insert Merge Field dialog, select the desired field and click Insert.
Here's an example of how your custom labels may eventually look like:
- To copy the layout of the first label to all other labels, click Update all labels on the pane (or the same button on the Mailings tab, in the Write & Insert Fields group).
- 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 field directly in the Word document, e.g. display dates or numbers in a different way. For this, select the needed field, press Shift + F9 to display the field coding, and then add a picture switch as explained in How to format mail merge fields.
How to add missing address elements
It may happen that the 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 in your Excel sheet differ from the default Word Mail Merge fields.
For example, you have chosen the Salutation, First name, Last name, Suffix format, but the preview shows only the First name and Last name.
In this case, first verify whether your Excel source file contains all the required data. If it does, click the Match Fields… button in the lower right corner of the Insert Address Block dialog box, and then match the fields manually.
For the detailed instructions, please see How to get mail merge to match fields.
Hurray! We finally did it :) Thanks a lot to everyone who has read our Mail Merge Labels tutorial to the end!