How to print labels by using Excel data in a Word mail merge
By creating a mail merge in Word, and then linking to a list in Excel, you can turn worksheet data such as the addresses of your customers into printable mailing labels.
Open Excel and set up the data to use in the mail merge – this you will probably have done already otherwise you would not be reading this!
Make sure the data is in the form of a list and that and there are no blank rows or columns within the list.
Each column should have a clear label at the top – you will need these column labels to specify what data goes where in the mail merge. Labels such as Name, House Number, Street are better than simply Column 1, Column 2, Column 3.
Use Window, Freeze Pane to make Row 1 into a Header Row
Decide exactly what you want in each line in the mail merge. For example, if you're creating labels and want to use each recipient's first name in address, make sure you have a separate column that contains first names, rather than a single column with both first and last names. Alternatively, if you want to refer to each recipient by both title and last name, such as Mr. Smith, you can use a single column containing both titles and last names OR you could use one column for title and another for last name.
Use File, Save As to save the list so that it is easy to find when you select it for the mail merge make sure that it has a distinct name and it is in a folder where find it again. To do this:
Save and close the workbook that contains the data.
Close Excel and open a new Word document in same folder and save it with a suitable name.
On the Tools menu, point to Letters and Mailings, and then click Mail Merge which is a “wizard”. Then follow the instructions in the column on the right, using “next” (at the bottom of the column) to move on and “previous” to go back if you need to.
Step 1 – Select document type
In this case, select Labels in menu at top right
Then go to bottom right and select:
Step 2 – Select starting document
In this case, it says Change document layout which is fine, then choose Label Options
The accepted practice is to use the reference numbers of Avery brand labels – other brands often have the Avery numbers on them – otherwise just try each option in turn until the little diagram shows that you have chosen the one you want. Click on it to select it.
If it is not visible already, go to View, Toolbars, Mail Merge to bring the toolbar up on the screen. This is a very useful toolbar – your first “port of call” in a storm, so to speak!
Step 3 – Select recipients
In this case, it says Choose an existing list, which is fine, then Browse, then choose the Excel file you want to use, by double-clicking on it, then on Sheet1$ (or similar).
Make sure the small Column Headers box is ticked, then OK, then OK again in next box.
If you want to include only some of the selected names in the mail merge rather than all of them, choose Edit Recipient List, follow instructions, then choose OK
Step 4 – Arrange your labels
Choose More items, then choose every item (known as fields) that you want to include on the label, then Insert. For example, you may want Title – Insert or Forename – Insert. Then Close.
At this point if you move to Step 5 you will find that everything on your labels is closed up really tight, for example MrsJaneSmith2AnyRoadAnytownWirral, soon the actual Word document, click in-between each pair of double arrows >>|<< (the grey highlighting will show you that you are in the right place) then press Enter key. This will move each item to a new line.
Then click on Update all labels. This will copy the formatting of the first label to all the labels. This is a very useful function, as if you find in Step 5 that they look wrong, you can come back to Step 4, change the first record, then choose Update all labels again, in one easy move. (If using toolbar, choose the PROPAGATE LABELS icon.) |
Step 5 – Preview your labels
If you need to change a recipient, follow the instructions on the right – this is unlikely to be necessary.
Step 6 – Complete the merge
You can also change individual labels at this stage – this is unlikely to be necessary. Choose Print and follow instructions in box. Note that the sheets of labels should be put into the feed-by-pass tray on the right of the printer, face up, as they are rather thicker than paper and can jam.
Note: You can’t open the data in both Word and Excel at the same time, so if you need to see the data and its column labels, use Previous and then Edit recipient list – this is unlikely to be necessary.
Hint: If you are using the same labels each time, i.e. it’s NOT list which is likely to be updated, then set up and save a separate file. |
