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
How do I use the decimal alignment tab to lineup my numbers by decimal place while performing the merge? Everytime I try to do this after the merge it just pushes every other part of the merge into a different place.
Hello Henry,
Please send screenshots of the source data and the result to support@ablebits.com and our support team will try to help.
Wow! I found multiple articles to learn about the Mail Merge option in Word and yet you clearly explained here what took all those separate articles to explain. Thank you! I have a question though.
My specific application of the mail merge is from an Excel file that is consistently being updated throughout the day. In other words, recipients are being added to the Excel file list periodically during the day. I would like to print my letters (in my specific application they are certificates) periodically throughout the day with the most updated recipient list from the Excel file. It is the same Excel file every time. What I have attempted is to "Refresh" the file in Word after clicking on "Edit Recipient List," then selecting the Data Source, then clicking "Refresh." Yet this does not seem to work. I cannot see the most recently added recipients.
Any ideas?
Thanks again for the article!
Don
Hello Don,
Thank you very much for your kind words. If your Excel data reside in a simple Excel range, try converting it into an Excel Table (select the data and click Insert tab > Table). Then enter the Table Name in the corresponding field on the Design tab and specify this table name as the data source when doing mail merge. If "Refresh" still doesn't work, then you can send a small sample workbook with your data and the Mail merge pattern to support@ablebits.com and our support team will try to help.
Don, if your data is not refreshing in your Word mail merge document, make sure the Excel workbook has been saved. Word cannot see your on-screen Excel data, only data that has actually been saved in the workbook.
If I am producing, say, 40 merge letters, can I run the merge without printing, but save the file of 40 letters to be printed later? Thanks.
Hello Don,
You can save the mail merge file as a usual Word document by clicking the Save button or pressing Ctrl+S.
Hi - I am trying to do a mailmerge and when I try and connect the spreadsheet I need to use to input the fields I get an "error" message which says that it is not in the correct format?
Are you able to help?
Thanks
Hi,
Is it possible to only merge cells that are a specific color on the spreadsheet? I have categories divided by color, and only want to merge one specific category.
Maggie
Hello Maggie,
You can fulfill your task using VBA functions. If you can send a sample workbook to support@ablebits.com, our support team will try to help.
Hi,
can u do mail merge from Excel to Word by vba coding also.
Hi Svetlana,
I need to create a macro in excel that can do a mail merge on a single row of data for me.
More specifically, I would like the macro starts off by asking me which row in excel be mail-merged.(or the macro can start off after I highlighted a row) Then somehow I would like to activate mail merge inside of my excel macro code-up to the last step-print preview of my merged letter. The computers in my company are using word 2003 and excel 2003... Any ideas would be greatly appreciated!
Hi Jialin,
I'm sorry, I have very little experience with macros. You can probably try finding a solution on targeted forums like mrexcel.com or excelforum.com.
After I changed my settings to use the DDE I can no longer choose a different tab on the excel spreadsheet that I choose in mail merge to get my data to go on my letter in word via mail merge. Is there a way to choose the specific tab still?
Hello,
I’m merge excel file with word 2013 to fill a preprinted form. This requires merger letter by letter (letters finally are printed in separate pre-printed squares). I have a problem with names like “Anna Maria” where I have space in the middle.
I separate letters in excel (one letter by column) and merge. When imported letter is a space (in this case fifth) is not included in word. The merged document shows ANNAMARIA. When replace space by “_” everything looks fine (Anna_Maria). How to overcome this problem?
Jarek
Hi, My query is how do I translate a set date from excell over to microsoft word. I have gotten many different dates by "Ctrl ;" a worksheet For example a date being 16 October 2014. When I go the the mailings section it changes it to gibberish: 41931. I have read the "Format date and time" section many times but have been unable to fix this thanks.
how do I mail merge a cell which contains the formula into Ms word.actually I am calculating the EMI in excel through formula but in Ms word it is not functioning. plz help.
Hello Rinku,
It looks strange. Generally, the formula results are copied to Word, not the formulas themselves. You'd better contact Microsoft support service (support.microsoft.com) regarding this issue.
How do I get a document (11x8.5), that has cards set 3 up on the page to change personalization records on each card?
Hello Serena,
I am sorry, your task is not quite clear. Please describe it in more detail.
Serena Grayson, if you have all the merge fields in each of the 3 cards on the page, and you see identical information on all of them, then you must let Word know that this form contains multiple "pages".
1] Place your cursor in the first position in the "new" record
2] Click on the Mailings tab
3] Select Rules then Next Record
4] Repeat this for each new record on the page
This tells Word that there are to be multiple records on a single page, and that the next record will begin at this point.
I have zero dollar amounts that I want to show as zero in my word merge. No matter how I format (currency, text, etc.), the zero value fields in word are blank. Help
I am using Office 2013 at work. I have followed the steps for the DDE Dynamic Data Exchange. Whenever I select the Excel file I get an error message: "Something went wrong". It suggested I repair office, which I did and I still can not complete my mail merge. I am able to complete the same mail merge on my home computer with Office 2010. What can I do to use my Office 2013 to complete my mail merge?
If Excel has words in different colours, is it possible that once you've mailmerged it to Word the different colours will appear on the Word document please ?
I mean having for example "Hi #FirstName, #ObjMesForFirstname"
#fields coming from list as #AdressBlock or #GreetingLine mentionned in your article
Dear Svetlana,
First, Congratulation for your great work !
Just a question : Is there a way to personalize the object of the message when merging as email messages ? I mean having for example "Hi <>, <>" as message object ? I can't find it in dialog box ? Is it possible with a Macro or ???.
Thanks in advance
Jocelyne.
My query is if i m preparing annual letters and few employeesare getting special allowance which i will reflect otjer than the sub heads of salary then while using if-then-else condition how different values can be merged.
Hello Kuldeep,
Please send me a sample workbook with your data at support@ablebits.com Most likely, the if/else logic should be implemented in Excel columns, and the result passed to Word.
Hi Jeff
I want to personalize a letter with different case numbers.
Sorry, I cannot follow you. Could you elaborate on the task, please?
Is there anyway to bring the colour of the excel cell over.?
Hi Jeff,
Sorry, I do not exactly understand your question. If you want to have the color of your Excel cells automatically copied to a Word document during Mail Merge, this is not possible. If you are asking about something different, please explain in more detail. Thank you!
I think he meant the color of the text set in Excel. I found this can't be done. Also the text formatting (italic, bold, underlined) transfer from Excel also is not possible. But you have option to use existing format of the paragraph, or not (using \* charformat to derive).