Microsoft Excel / Word How-to: Mail Merge Setup
So you have a document that you need to send to more than a couple of people. Maybe hundreds, or even thousand of people. Or you want to print the labels for your holiday greeting cards, so that you don't have to put pen to paper to get all of the addresses on the envelopes. Getting married? When we did, we put the names and addresses of all of the invited guests into an Excel spreadsheet, along with phone numbers, the RSVP, dinner dish selection, and after the wedding the gift received. Invitations and thank you cards went out using the same mail merge, from Excel to the label sheets using Word for the layout. It may sound complicated, but it really is a great time saver once you've figured out how to set it up. It takes a few steps to walk through the wizard, and the first time you do it you'll need to pay attention, but it really is easy!
Here's the basic idea:
You start with a source of data, typically this will be some sort of contact list. You can use almost any kind of list, a comma separated file (CSV), a text file with the fields separated by tabs, an Outlook address book, lists in Excel, Access, or any ODBC-compliant database. You then pull that data into Word using the mail merge capability and format the document, which could be a letter, envelope, labels, or even e-mail. Once you've done that you simply print the document and Voilà!, mail merge.
To get this going we're going to set up a very simple address list in Excel. I've kept this very simple, you would normally have more information in your data set than what is shown here. However, you select which pieces of the information you want to include in your merged document, so it doesn't really matter how much information is in there:
Now that you have your source data set, let's open Word to configure the mail merge document. We'll be looking at Word 2003, but the mail merge wizard is pretty much the same (with a few exceptions) as older versions of Word. This wizard will open as part of the Task Pane on the right side of the window, where previous versions used a pop-up dialog box. Also, the wizards will differ depending on the source data, so you can expect different options if you are using a data source different than Excel. This should, however, give you a pretty good idea on how the process works. You access the wizard by going to the main menu and selecting Tools -> Letters and Mailings -> Mail Merge:
You will see the the Task Pane will either appear or change to show the Mail Merge wizard. It starts out by asking you what kind of document you would like to use for the merge. In this case we're going to be creating mailing labels, so I've selected that option. At the bottom of the task pane you see that you're at step 1 of 6, and where to click to move to the next step:
Here you can decide if you want to change the layout of the current document or start from an existing document. We're going to use the current document, so we'll just click on the Label options... link. This will bring up a dialog which will allow us to select from a large selection of predefined labels, or create a new one of our own. I've been doing mail merges for years, and I think I've only created new labels once or twice, and that would have been years ago when there weren't so many defined. Now there are so many label types and sizes which are pre-defined I can't imagine not finding one that will work for your particular needs. In this case I've selected a standard Avery layout, with the small standard size labels on a full sheet:
You will see that you now have a document that has the layout configured for the type of labels you've selected. Go to the bottom of the task pane and select the next step to select the recipients:
In this step we need to tell it where to find the data source we're going to use for the merged information. We're going to use an existing list, and will browse to find the Excel file on our computer. Select browse to open up the Select Data Source dialog, then go to the directory where you've put the spreadsheet, highlight the file and click on Open:
Once you've done that you will get another dialog box that will ask you what part of the spreadsheet to use. One thing to notice here in my example - I've defined a range (you can look here to see how to do that) in my spreadsheet that encloses that data that I want to include. You can see from the dialog that there are 2 worksheets in the file, and also the range that I've named MailMerge. This can come in very handy if you've got an extensive spreadsheet, and you only need to include a section of it for the merge. By defining the range in advance you can limit the amount of information you will have to choose from in the upcoming steps. Also notice the check box at the bottom of the dialog that you can select if the first row of your sheet (or range) contains descriptive headers. You should try to include these headers if possible, it will cut down on confusion when you're trying to select the date for your merge:
After you've selected the sheet or range you want to use for the data you will be given the opportunity to select which entries to include in the list. The drop down arrows allow you to filter the data by any of the column headings, which makes it considerably easier when you're working with large lists. In this case I'm going to include all three records in my sample spreadsheet, so I'll just click on OK:
You'll see that you have <Next Record> in each of the labels, don't worry about that right now. Again go to the bottom of the task pane and select the next step, arrange your labels:
In this step we're going to format the labels. This step is a little different from older versions of Word, where formatting the labels was a bit of a trickier process. Now what we're going to do is format the first label the way we want it, then tell the wizard to replicate that format to all of the other labels. We want our labels to be a generic address block, so we're going to select the first option:
The address block dialog has some nice options on it, which allows you to customize the formatting based on international requirements. This is new with Word 2003, the older versions didn't have this capability. As our list is pretty small, and only has US addresses none of these will come into play. However, if you live or are mailing outside of the US take a close look at these, as they will customize the addresses based on the country of origin. We will, however, need to map our fields to this block template, which is very easily accomplished by clicking on the Match fields button in the bottom left corner of the dialog:
This will bring up another dialog which will allow you to align your data with the fields required for the merge. Word will go a long way towards filling this out for you, providing you've used a descriptive header that will give a clue as to what's in the field. Basically, the information on the left side is what Word is looking for. The drop down list on the right side shows the header descriptions from your your file. All you need to do is match up the information that you're providing on the right side with the needed information from the left side:
In my case I needed to make 2 changes. The first was mapping First Name (on the left side) to Name (from my list). Had I left it as Last Name it would have left a blank space before each name, because there was nothing to enter in the first name. The second change was to map Address 1 (on the left side) to Street (from my file), as it couldn't figure this one out. If you use mail merge frequently you will learn how to name your headers so the matches are done automatically, without any corrections needed at this point. The properly matched form looks like this:
Click on OK on this dialog, then click OK again on the Address Block dialog. This will update the first label, you will now need to copy that formatting to the rest of the labels. Simply click on Update all labels, and you should end up with a document that looks like this:
You're almost done, stay with me! Now click on the Next: Preview your labels link at the bottom of the task pane. This will bring up the next page of the wizard, which will show your completed document with the merged data:
Woohoo! If the formatting isn't correct you can simply back up through the wizard by selecting the previous steps at the bottom of the task pane, and make the necessary corrections. You can also tweak your recipient list from here. Assuming everything is OK, the last thing to do is to complete the merge by selecting that link from the bottom of the task pane, which will take you to the completed document:
Now just put the labels into your printer and click on the Print... link, and you've got your printed labels. I think maybe next time we'll take it up a notch and show how to merge directly from a database.....