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.
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 2021, Excel 2019, Excel 2016, Excel 2010, and very similar in Excel 2007.
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:
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.
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:
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).
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.
(Or you can go to the Mailings tab > Start Mail Merge group and click Start Mail Merge > Labels.)
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.
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.
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.
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:
When the recipient list is all set, click Next: Arrange your labels on the pane.
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:
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.
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).
When you are satisfied with the appearance of your address labels, click Next: Complete the merge.
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.
If you may want to print the same labels at some point in the future, you have two options:
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.
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.
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:
Here's an example of how your custom labels may eventually look like:
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!
Table of contents