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!
Is there a way to create page breaks in mailing labels? I have created an address list using Excel with forced page breaks. When I merge the labels using Word, it doesn't recognize the page breaks and prints the labels continuously without breaking. Any help would be appreciated. Thanks.
When creating mailing labels by merging data from an Excel document with a Word document only the first sheet of labels shows up.
1. How do I access the remaining sheets?
2. Assuming I can see the other sheets, how can I print the labels without printing sheet 1 for example?
Thanks for your help.
I recently merged an excel document with a word document. I have 76 lines (records) in excel and the merge did not accept all records. When I reversed the alphabetically order from A to Z to Z to A, the merge did not same number of files from the top of the list, but not 100% of the files. Either A to Z or Z to A left out the last few files in the list which were not the same files because I had reversed them. Is there a limit in the number of lines/records? than can be merged between Excel and Word?
Am wanting to merge and print multiple of same labels defined by a certain excel field.
In my excel sheet, has only 3 columns Part, Name, Count
And I wish to print Part and Name fields only.
When I merge, if the Count has value 3, I wish to print 3 times of the same row (Part, Name) three times and if the Count has 1, move to next row and continue. etc.
Can you help me please?
Thanks in advance,
What you want to accomplish will require some VBA code and creating some buttons on your sheet or a dashboard all of which are beyond the scope of this blog. However, I can point you in the right direction and you can take it from there.
MrExcel has a couple of discussions that deal with this topic. You might want to start at:
Then go to another site extendoffice at:
pls how can i Draw and label Microsoft excel sheet
I copied A4 size label format from Google and type addresses in that but when I take print out it's not coming in order I don't know what is a problem
i am taking printout on laser printer
hello sir i am trying to make labels in ms word its created but when i am taking printout its not come in correct format please tell me what i do
I customise labels on a sheet. The Mail Merge advice is great and in the preview all labels are OK. Trouble is when printing, the first line of the next row creeps on to the previous row!, so it all down to printer set up I guess?
Extremely clear and very useful. Great job.
This was very helpful. Thank you so much
Ok, trying to use this to print name labels for folders for conference. I just need the name and I think it is formatting for address label. I tried to format in excel before the mail merge process and it didn't migrate over correctly... i.e. bold, centered,16 pt. Is there a way to format the label in word during mail merge process?
any help would be appreciate. frustrated in Greenville, SC
I need the solfware to print labels form my excel address list
I'm having a problem that looks like people above are having also. Only the first two pages of labels are printing. There should be 24 pages. Any advice or help would be very appreciated, thanks.
Unfortunately it's hard to say what causes the issue. Please try to check the printer settings on your PC.
I am attempting to produce labels by merging from an Excel spreadsheet into Word 2013. I am using Avery j8162 labels, therefore 16 labels per sheet. I have 34 records to merge. The label layout (not an address block, but a unique layout) is done by inserting merge fields from the spreadsheet, and "update all labels" to transfer the label layout to all records on the page. All goes well until I preview results. The first page has records 1 to 16 on it, the second page has records 2 to 17 and so on, so that I end up with 34 sheets with massive duplication, rather than just 3 sheets with a single label for each record.
Can you help?
Unfortunately it's hard to say what causes the issue. Please try to check the printer settings on your PC.
I have printed labels for years, and not had a problem, but suddenly the labels are not printing within the labels outline from about the middle of page one. This gets progressively worse over the pages.
I have addresses in Excel 2010, use Word 2010 mailmerge wizard, have the Avery codes loaded, and am using L7160.
The Xerox printer (new) was thought to be the issue, but the engineer says it is only printing what is being sent to it. So, I have some old labels from 2013 and I sent them to the printer, and they printed OK!
It's a peculiar one and it has me beat. Does anyone have any ideas?
Sorry it's hard to say what causes the issue. You could try to check the printer settings on your PC.
How can the label be set to print so it misses out blank fields?
We'd recommend to initially exclude rows with blank fields in your address list in Excel.
I can preview all of my labels one by one but can only see and print the first page?
Try pressing the finish and merge button at the end of the ribbon and it should load all pages of labels? :)
Mail merging and printing labels from Excel has gone smoothly until the last step. All the addresses on my Excel spreadsheet are not transferring to the merge. It always stops at line 369 on my spreadsheet. Any suggestions?
Having same problem except no addresses transfer to the merge. Totally blank. Have tried every possible way to make it work and it jus doesn't.
For us to be able to help you better, please send us a sample workbook with the problematic row in Excel to email@example.com.
I cannot get to grips with being able to type an address and have it printed on one of the labels on my label sheet. I found this to be easy previously.
For us to be able to assist you better, please describe the issue in more detail.