Go beyond the mail merge basics. Import lists from Excel, and use Excel's data and number tools. Customize your mail merge with personalized messages.
Mail merge using an Excel spreadsheet
The merge runs more smoothly if all the information you want to include is ready—so, make sure:
-
The columns in your spreadsheet match the fields you want to use in your merge. For example, to address readers by their first name, make sure you have separate columns for first and last names.
-
All the data you want to merge is in the spreadsheet.
-
Number formatting won’t change. If your spreadsheet includes dates, times, currency values, or postal codes that begin or end in 0, see Format mail merge numbers, dates, and other values.
When the data’s ready, start the mail merge
-
In Word, open a new document.
-
Click Mailings > Start Mail Merge, and then click the kind of merge you want to run.
-
Click Select Recipients > Use Existing List.
-
Browse to your Excel spreadsheet, and then click Open. If Word prompts you, select Sheet1$ and click OK. Now the Excel spreadsheet’s connected to the mail merge document you’re creating in Word.
-
Next, you can insert mail merge fields that pull the information from your spreadsheet into your document. For example, to add a greeting line to an email message or a letter, click Mailings > Greeting Line. Or add addresses from your list by clicking Mailings > Address Block.
-
When your document’s ready, click Preview Results and click the arrows to see each specific copy of the document.
-
To finish the merge, click Finish & Merge, and then click Print Documents or Send E-mail Messages.
Want more?
In Introduction to Mail Merge you learned the basics: how to set up mail merge with the wizard, create a list of recipients, add basic mail merge fields, and print envelopes.
But there is a lot more you can do.
First let's take a look at recipient lists.
In the first course, we created a list from our Outlook contacts and then created one from scratch in Word.
But you can also import lists from other sources, like Microsoft Excel files.
If you use Excel, you can take advantage of all the tools for working with data and numbers.
For example, this list is pretty small, but you could create a workbook with numerous lists in tables that contain thousands of entries.
When you add a table in Excel, the columns become the merge fields that you use in your mail merge document.
With your document open, click the MAILINGS tab and Start Mail Merge.
The current document is a letter, but let's click E-mail Messages to change that to an email message.
Next, click Select Recipients and Use an Existing List.
You have a lot of choices for the type of data you can use.
We'll open the Excel workbook.
Then, select which table to use.
Make sure First row of data contains column headers is checked.
This tells Word to use the table column headers as the names for the merge fields.
Click OK, and the data is added.
To check the data, click Edit Recipient List, and there is the list from the Excel table and the Excel column header.
This is where you can refine the list before you run the mail merge.
For example, you can locate Duplicate entries, Search, Sort and Filter. For now, we'll assume the list is good and click OK.
And let's see what happens when we add a merge field.
Select the greeting and delete it. Then, click Greeting Line.
The default greeting should be in this format: Dear Mr. Randall .
So why does the Preview have this odd format? Well, problems like this are most often caused by mismatched fields, and you can fix that by clicking Match Fields.
What happened is Word automatically matched the Courtesy Title field to the Title column in the Excel table.
But the Title column contains job titles, not courtesy titles.
We don't have a good match for Courtesy Title, so let's select (not matched). Then, OK.
Now, because there's no more Courtesy Title field, Word chooses a different greeting line format.
Let's Preview a few names to make sure the greeting line looks the way we want it to.
And then click OK.
Click Preview Results to see the greeting in the document.
The email is almost ready to send.
Up next, we'll add some custom merge fields.
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
