Microsoft Word 2019 in 90 Pages Chapter 6 explains how to create a mail merge list in Word. In this post, you will learn how to use an Excel spreadsheet for mail merge.
The Microsoft Word mail merge is a powerful feature for generating personalized documents. For example, I decided to create a campaign to raise awareness about the need for continuing education with a focus on computer literacy and specifically the need for comprehensive, yet concise, materials for Microsoft Word, PowerPoint, and Excel. One aspect of this campaign was mail merge letters that were sent along with complimentary copies of my self-published guides for Microsoft Office applications.
What is Mail Merge?
Mail merge replaces fields that you’ve added to a document with data from a list to create multiple personalized documents. We’ve all received junk mail and email spam that contains our name or other personal information. Well, those are examples of where mail merge was used to customize a document with data stored in a database, Excel list, Outlook Contacts, or other mailing list.
A mail merge document can take several forms. If you’re shipping complimentary copies to prospective customers, then you’ll need a mail merge letter. If you are trying to make readers aware of your website, then you’ll want a mail merge email with a link to your website. Of course, mail merge can also be used to create shipping labels and envelopes.
How Do You Create a Mail Merge Document Using Word and Excel?
A mail merge letter is a carefully crafted document with information that a group of related recipients may be interested in. You personalize the letter (email, flyer, and so on) by inserting fields that represent data from a stored list. Mail merge is a process that is best understood by looking at it step-by-step. For instructional purposes, we’ll look at the steps for a Mail Merge letter:
- Create a list of recipients. A Microsoft Excel spreadsheet is a good way to store prospective and current customers or any other data that you might want to merge into a document. Group related customers into separate sheets in the workbook to make it easy to merge only the data you’re interested in. The top row of data in a sheet should contain descriptive headings (headings are bold in the spreadsheet below). Fill out as much information as you can in the rows below the headings. Empty fields will merge as blanks into the Word document. Here’s an example of what your spreadsheet might look like:
An Excel spreadsheet is preferable for storing data when you’ll have a lot of data to merge. Typing dozens or hundreds of rows of data into a Word list can become tedious and error-prone.
- Start Mail Merge. Start Word and create a new document. Next, click Mailings > Start Mail Merge > Letters.
- Select Recipients. Click Mailings > Select Recipients > Use an Existing List. A dialog box is displayed. Navigate to the location of the Excel spreadsheet containing your customers/prospective customers. Select the spreadsheet and then click Open. Select the worksheet tab with the data you want to Mail Merge, if necessary.
- You can optionally further refine your recipient list by clicking Mailings > Edit Recipient List. In the displayed dialog box, click a checkbox next to a record to remove it from mail merge. Click a tool in the Refine recipient list group to sort, filter (limit recipients to certain criteria), and even deselect duplicates.
- Check to be sure your fields are matched properly. Mail Merge has a set of expected fields. You may have fields that have a different name from that expected but contain the same type of data. Click Mailings > Match Fields. In the displayed dialog box, use the drop-down lists to select fields that match expected fields. In our example, First Name was matched to Contact, Job Title was matched to Title, Company was matched to College, and Courtesy Title, which was originally matched to Title, was set to (not matched):
- Type your document and add merge fields. Like any marketing material, you’ll want to focus on your audience and write a letter that speaks to their specific needs. Or, in some cases, you’ll want to generate a letter that makes the recipients aware of a need they didn’t even know they had! As you type, when you come to information that is stored in your list (Excel file) such as a contact name, click the arrow in Mailings > Insert Merge Field and then click the appropriate field name. For a letter with an address block, click Mailings > Address Block to display a dialog box for customizing the merged address block. You may need to click Match Fields to match your specific field names to those used for the address block. The Mailings > Greeting Line command works similarly. A letter with merge fields could look similar to:
Note: The example above uses individual fields for the address block for demonstration purposes. However, the Address Block command is often a better choice in this situation because missing field data will not leave blanks whereas individual fields without corresponding data will.
- Format your letter. Apply character formats to fields by selecting the entire field, including the chevron marks on either side, before selecting formats such as bold, italic, or fonts.
- Preview the Merge. Click Mailings > Results to see how your letter looks with fields replaced by actual data. Click the forward and backward arrows in the record controls (shown below) to scroll through your documents.
- Complete your letter. Click Mailings > Finish & Merge to display options for completing your letter.
- When using Microsoft Excel to compile Mail Merge data, put descriptive headings in the first row of data and include as much relevant information as is available to you. It’s a big pain to realize too late that you didn’t enter Department Names, for example, when your letter needs to refer to them. Going back into your spreadsheet and adding this data is a lot of work (but, of course, ultimately worth it).
- In Excel, be sure to format ZIP/Postal Code columns as text so that you don’t lose leading zeros.
- An Excel spreadsheet used as a data source is considered a database. Each row is a record, and each column is a field.
- Click Mailings > Rules and then click a rule for advanced Mail Merge options. For example, the If…Then…Else rule inserts text at the current insertion point depending on the value of a field, as specified in the displayed dialog box: