by This end-to-end tutorial will teach you how to effectively mail merge in Word using an Excel sheet as the data source.
Mail Merge can be a real time-saver when it comes to sending mass mailings. It lets you quickly create custom letters, emails or mailing labels in Word by merging the information you already have in your Excel spreadsheet. This tutorial provides an overview of the main features and explains how to do a mail merge from Excel step-by-step.
Mail Merge basics
A mail merge may look like a daunting task, but in fact the process is pretty simple.
To get a grasp of the basics, you can think of it in terms of 3 documents.
- Excel source file with information about the recipients such as names, addresses, emails, etc.
- Word document with codes for the personalized fields.
- The final Word document with personalized letters, emails, labels, or envelopes.
The goal of the mail merge is to combine the data in File 1 and File 2 to create File 3.
Preparing Excel spreadsheet for mail merge
When you run a mail merge, your Excel file will get connected to your Word document, and Microsoft Word will be pulling the recipient details directly from the connected worksheet.
Before running a mail merge in Word, make sure your Excel file has all the information you want to include such as first name, last name, salutation, zip codes, addresses, etc. If you want to add more details, you'd better do this before starting the merge.
Important things to check:
- Your Excel sheet has one row for each recipient.
- The columns in the spreadsheet match the fields you want to use in a mail merge. For instance, if you wish to address the recipients by their first name, be sure to create separate columns for the first and last names. If you intend to sort the contacts by state or city, verify that you have a separate State or City column.
- If your Excel file contains postal codes or any other numbers with leading zeros, format them as text to retain zeros during a mail merge.
- If you create an Excel spreadsheet by importing information from a .csv or .txt file, this tutorial will help you do that right: Importing CSV files into Excel.
- If you plan to use your Outlook contacts, the following article may be helpful: How to export Outlook contacts to CSV.
Here's an example of an Excel sheet that can be used for a mail merge:
How to mail merge from Excel to Word
Once the source data spreadsheet is set and reviewed, you are ready to run the mail merge. In this example, we will be merging letters. For email messages, the steps will be essentially the same.
- Create a Word document. If you have already composed your letter, you can open an existing document, otherwise create a new one.
- Choose what kind of merge you want to run. On the Mailings tab, in the Start Mail Merge group, click Start Mail Merge and pick the mail merge type - letters, email messages, labels, envelopes or documents. We are choosing Letters.
- Select the recipients. On the Mailings tab, in the Start Mail Merge group, click Select Recipients > Use Existing List.
- Connect Excel spreadsheet and Word document. Browse for your Excel file and click Open. Then, select the target sheet and click OK.
By setting the connection between your Excel sheet and the Word document you ensure that your mail merge data source will be automatically updated each time you make changes to the Excel file that contains the recipient details.
- Refine the recipient list. If you want to exclude certain recipients, then click the Edit Recipient List button in the Start Mail Merge group.
The Mail Merge Recipients dialog pops up, and you check or uncheck checkboxes to add or remove the recipients from the mail merge.
Tip. You can also sort, filter and dedupe the recipients list as well as validate the email addresses by clicking the corresponding option under Refine Recipients List.
When the recipients list is finalized, you are ready to start on the letter. Type the text directly in a Word document or copy/paste from an external source.
- Add Address Block and Greeting Line. Now it's time to add placeholders for the Address Block and Greeting Line for Mail Merge to know exactly where to place that data. To add a placeholder, click the corresponding button on the Mailings tab, in the Write & Insert Fields group.
Depending on which merge field you are inserting, a dialog box will appear with different options. Select the ones that work best for you, observe the results in the Preview section and click OK. To switch to the next or previous recipient, use the right and left arrows.
Tip. If Word pulls wrong information from the Excel file, click the Match Fields button to match a specific field.
When done, the merge field placeholders will appear in your document like shown in the screenshot below:
- Insert merge fields. In some cases, adding only the Address block and Greeting line will suffice. When the letter is printed out, all the copies will be identical except for the recipients' names and addresses.
In other situations, you may wish to insert more merge fields to personalize your letters a little further. To do this, place the cursor exactly where the personal information should appear in the letter, click the Insert Merge Field button on the ribbon, and choose the field from the drop-down list.
If a merge field is inserted within the text, make sure there are spaces on both sides of it:
- Preview the results. To verify that the recipient details correctly appear in the letter, click the Preview Results button on the Mailings tab, and then use the left and right arrows to switch between the recipients.
- Finish mail merge. If you are happy with all the previews, head over to the Finish group and click the Finish & Merge button. Here you can choose to print the letters or send them as email messages. To make some edits before printing or emailing, click Edit Individual Documents. A new document will open, and you will be able to make the changes in each particular letter.
- Save the mail merge document. Save the mail merge results as a usual Word document by clicking the Save button or pressing the Ctrl + S shortcut.
Once saved, the file will stay connected to your Excel mailing list. When you want to use the mail merge document again, open it and click Yes when Word prompts you to retain that connection.
Using step-by-step Mail Merge Wizard
In addition to the options accessible on the ribbon, the same features are available in the form of the Mail Merge Wizard.
To start the wizard, go to the Mailings tab and click Start Mail Merge > Step-by-Step Mail Merge Wizard.
Once clicked, the Mail Merge pane will open on the right side of your document and walk you through the process step-by-step.
In my opinion, working with the ribbon is more convenient as it lets you use exactly the feature you need at the moment. When doing the mail merge for the first time, the wizard's step-by-step guidance may come in helpful.
How to get mail merge to match fields
For Word Mail Merge to recognize fields correctly, you need to be very specific with the column names in your Excel file. This is especially true for the Address Block and Greeting Line features.
- For the Name fields, First Name and Last Name work without a hitch. Other column names may cause matching failures.
- For the Address fields, use the full words such as Address, City, State, Postal code, or Zip code.
If your data source in Excel has different column names, you will have to match the fields manually. Here's how:
- In the Insert Address Block or Insert Greeting Line dialog box, click the Match Fields button. (This button is also available on the Mailings tab, in the Write & Insert Fields group.)
- In the Match Fields dialog box that pops up, you will see the list of fields required for a given block. To match a specific field, click the drop-down arrow next to it, and pick the correct field from the list.
In the screenshot below, we've matched the Street column from our Excel source data file to the Address 1:
Mail Merge shortcuts
If you do a mail merge in Word on a regular basis, learning a few shortcuts can save you quite a lot of time and make you more productive.
| Shortcut | Description |
|---|---|
| Alt+F9 | Switch between all field codes and their results in a mail merge document. |
| Shift+F9 | Display the coding of the selected field. |
| F9 | Update the selected filed. Place the cursor anywhere in the field and press F9 to update it. |
| F11 | Go to the next field. |
| Shift+F11 | Go to the previous field. |
| Alt+Shift+E | Edit the mail merge document. Note, this will break the connection between your Excel file and Word document, as a result your mail merge source won't be automatically updated any longer. |
| Alt+Shift+F | Insert a merge field from your mail merge source. |
| Alt+Shift+M | Print the merged document. |
| Ctrl+F9 | Insert an empty field. |
| Ctrl+F11 | Lock a field. The field results won't be updated when the information in the Excel source file changes. |
| Ctrl+Shift+F11 | Unlock a field. The field results will be updated again. |
| Ctrl+Shift+F9 | Unlink a field. The field will be permanently removed from a document, replaced by its current value and from then on treated as normal text. |
| Alt+Shift+D | Insert the DATE field that displays the current date. |
| Alt+Shift+T | Insert the TIME field that displays the current time. |
| Alt+Shift+P | Insert the PAGE field that displays the page number. |
| Alt+Ctrl+L | Insert LISTNUM field. |
Hopefully, this information has been helpful, and now you know how to perform mail merge from Excel to Word correctly. Thank you for reading!
151 comments
Thanks...
In this tutorial very easily the whole function have been described.
Hi,
I'm trying to mail merge two different values to one checkbox so that if the values 1 or 3 is in excel it would check the box in word. Any idea what command to use?
It is easy for one specific value with following command { IF { MERGEFIELD "mergefieldname" } = "True" "symbol for checked box" "symbol for unchecked box" }
Thanks in advance!
Hi - When I go to mail merge on Word from an Excel spreadsheet with multiple worksheets, I go to select recipients, select the excel document and then a box appears to select which worksheet you want to use. I currently have 5 tabs on the excel spreadsheet and yet the box that asks you to chose which sheet you want shows around 30 - different duplicates of the originals. This is really frustrating. Is there any way to remove these either via word or excel, without deleting the originals from the list?
Thanks for all these explanation.
My datasheet (table) contains formulas (=OFFSET(Extract20130404;MATCH(A274;Extract20130404[Delivery customer];0)-1;MATCH($D$1;Extract20130404[#Headers];0)-1;1;1))
Is there a way to merge even if the data is made of formulas?
Up to know I can't merge. The only way I found is to copy/paste special (values only) and then merge. But this way is a waste of time.
Thanks by advance.
Greetings,
Thank you for detailed post.
I have a query,request your expert comment on the same.
Is it possible to append new records in the already mail merged document, without saving it as a separate file?
I have an excel file containing 120 records (rows). I prepared a letter and inserted the fields from the said excel file. Thereafter, I performed mail merge. Now I have two files, the first one with only one letter and a connecting link with excel data and the second one with 120 letters. Now if I add say 10 more rows in the excel file, how can I get it updated in the second file containing 120 letters?
Regards,
When doing a mail merge in MS Office Professional Plus 2013 I set my greeting line format to be Dear Mr. Randall, but the preview shows it as Dear Jack Randall. If I go into Match Fields and change the first name block to "not matched" it goes to the default "Dear Sir or Madam,". Do you know how I can get this to work?
Hi dear,
Many thanks for your great full & clear explanation regard this & hope to with you furthe trouble when obtaining this digital world.
Wish you sucess.
Thanks again,
Maduranga Fernando
When I use the DDE option as indicated I am unable to select a specific sheet for my import ( My file has different sheets , linked to each other so I don't want to paste it in a seperate sheet. It slows down the computer is the sheets are linked to sheets in different files) I do I get around this
I am trying to do a mail merge from Excel to Word in a Chart-type form. I can everything to merge except one field. This field will occasionally pull correctly but typically pulls as 0. It should be in the format ####### or blank. I have tried changing the format of the excel file to many different types but this field does not work.
Please help.
Hey - Thank you so much for these easy to follow instructions. I've tried mail merge before and I wanted to pull my hair out. I'm glad I found your instructions. You rock!
The manual is great!
There is also Gendo (www.gendo.me) that does it a bit faster ( merges data into docx template).
Hi,
We are using mail merge by disseminating email to our member. This features on MS office is very helpful to our association. However, we experience lately that some of our email hasn't successfully received by the recipient. It's weird because when you check the email, it's already on sent items folder.
I'm using MS Office 2013 under Window 7 as operating system. I tried to look for a solution by checking it to internet. I follow and do all instruction they advice but still no avail. I hope you can help us with this issue.
how can i re-arrange numbering on the merged document?
I am trying to format the numbering in the word document after mail merging. There are several documents that need to be numbered.
Please assist.
Everything is very useful on your site so first a big thank you. When I am trying to fix the percentage fields in my WORD document being merged from Excel, they are still not displaying correctly according to your instructions. 0.36734693877551022 needs to be formatted as a percent which in actuality needs to be displayed as 37%. Your instructions either allow it to look like .36% or 0%. Can you please send me the formula to achieve this? I am sure it can be done.
Thanks for your help.
Hello Lisa,
Please try to apply the “Percentage” format to your cells in Excel.
Hey Buddy,
Thanks you very much,
I want to convert the number into % format, tried many solution, some of work sometimes so very disappointed but this trick works very easily.
Thanks a lot!!!
I am using mail merge to create a set of invoices. I then want to be able to email them to the recipients. Can Word mail merge handle that or do I need another piece of software to email out the invoices when created? I have the email addresses in the same excel spreadsheet that gives the other details for the mail merge as we request recipients to validate the email address we have on file for them at the bottom of their invoice.
Hello Lan,
I am sorry, there is no way to fulfill your task using the standard Microsoft Word features. You need special software for this task, please google for it – there are quite many programs that can help you. We can’t recommend you anything as you know better what features you need.
Hi!
I am trying to make a mail merge, from data in excel sheet. One of the columns has this pattern: 000-000-000
When I import the list, enter the field of that column, it comes out on the label like this:000000000
Do you know the reason? How can I save it in the above layout?
Thank you
HI
I need to draft increase letters and using annual amount so I need the salary to appear like this R120 846.00 without me separating the thousand and Hundreds currently even though I have a space between thousands and Hundreds it appears like theses R120846.00
Please help
Dear Svetlana Cheusheva:
I have a problem while acting upon the method you mentioned above. i.e (2. Browse to your spreadsheet, double-click it, choose MS Excel Worksheets via DDE (*.xls), then click OK.)
When i select "MS Excel Worksheets via DDE (*.xls)" it shows an error message. Message from this box is as under:
"Word could not re-establish a DDE connection to MS Excel to complete the current task"
Detail mentioned in this box is here:
"This error message can appear if you attempt to insert a database into a Word document as an object or attach an Access data source to a Word mail-merge main document.
This error message usually occurs if there is a problem communicating via Dynamic Data Exchange (DDE).
Possible remedies are to reboot the system or attach the data source using an alternate method (ODBC, DAO).
For more information, see Microsoft Knowledge Base article 918594."
Dear i am in dire need to retain the original format. Kindly help me.
Blessed
Muhammad Saqib