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
HI
I am trying to mail merge from excel to word but I need my information to run in snaking sequence. I am doing it for a seating arrangement plan according to their index numbers. I need the index numbers to run in a snake. please help me. Thanks
I have an excel document with a list of users and their assigned line managers. I want to send each line manager 1 email with all their associated users. How can I do this in mail merge, with a rule? Or do I need to concatenate the users into one cell for each line manager? Help!
Hi, I'm merging an excel doc to word, but after I merge the information it changes color. So I'm trying to figure our how to prevent that. Please help!
Hi, I have done my mail merge however when I click Preview Results is not work. I can't see any preview. If I click edit individual letters then I only can see the output. Pls advise.
I have an excel spreadsheet with all my info on there is one mailmerge that I can not get right. in cell A1 I have a time as 7:00 am in cell B1 I have end time as 8:00 pm in cell C1 should be the total of 13 hrs. cell A and cell B are formatted as time 1:30 cell C is formatted as custom h:mm and shows as 13:00 but when I merge this info to my word document the 13:00 shows as 1 hr. it works with anything under 12 hrs but over 12 hrs it only come out with 1 hr for 13 2 hrs for 14 hrs. like it does not recongnize anything over 12 hours. help please
I have an advanced question regarding a e-mail merge with an excel spreadsheet. I created the e-mail merge document with a table that is showing purchases from multiple suppliers down the left side of the table and the two columns are broken down by customer purchase amounts. I have been able to bring all of the column information into the mail merge, however I want each customers mail merge table to total can you help me?
If some of my excel sheet cells formatted with different colors can I transferred such data with same color in Word through Mail Merge? Is that possible?
Regards
Shehbaz
Is there a possible to import the contents of a large excel sheet into a word document table? The word doc has 2 columns in layout.
How do you get the mail merge function to pick all the other row data after populating the first row? The <> seems to publish into a new second page and not the second row of the created table
I am having 1065 details of my 45 branches and I need to send mails for 45 branches with the details in single mail for single branch with multiple lines. Currently I am sending around 1065 mails in a month. Help me to solve this. Expecting your reply.
I have completed the mail merge as per your instruction. However, there seems to be a constriction on the number of columns and I am not able to increase the number of columns. Moreover, when I choose the option Insert Merge Fields it reflects ""Automergefield and this is restricted to a maximum of 8 fields.My question is how to increase the number of fields for that corresponding excel sheet.
I am preparing a mail merge for a mailing to parents concerning their child's fees and fines. Each child' list is unique to them and some contain several different items. I am able to address the letter and insert the first line of fees but need to have all the fees listed and the total.
Thank you!
I TRY TO MAKE MULTI APPLICATION FORM IN WORD WITH USING EXCEL DATA BASE,
I confuse in one format / formula
i want to write name in table box (in word) from data base source (excel)
i use this formula =mid(A1,2,1) but this taking tomuch time and not see proper.
problem exp.
in excel (source) :
A B
1 CODE : CUST. NAME
2 00001 : RAJESHKUMAR PATEL
3 32540 : RAJ KUMAR PATEL
and i want to write in word (but in name box, like [R|A|J|E|S|H|K|U|M|A|L| |P|A|T|E|L|
So tell me how to do this
i waiting for your reply....
Your information is so helpful, thank you. I am working on a merge document using Office Home & Student 2013 & Windows 8.1. Why do only 255 characters appear in my Word document text field when there is double that amount of data in my Excel spreadsheet. The data is formatted as "text" in Excel. Is there a maximum amount of data in either Word or Excel, and if so, can I override it? Thanks.
Hello, Linda,
Unfortunately this is a limitation set for Text in Excel. Please try to split your data to several cells and join them again after Mail merging.
Thats not the whole truth:
"Word looks at the data in the first record in the datasource in order to decide what you need. I suspect that some of your fields are merging OK because they have more than 256 characters in the first record. Edit the first record so that it has more than 256 characters in any fields that are long for some records." Kimberly from MSOfficeForum
Hi Svetlana,
I'm trying to create a 2007 Word mail merge document from a 2007 Excel file to use to print labels on an Avery 8160 label sheet which contains 30 labels (3 columns & 10 rows). But I can only get the top row of data and the bottom row of each page of the label to display the excel list data. All rows and columns of the entire excel file display as I go through the mail merge setup prompts, but just will not fully merge and display as expected on the label. Any thoughts?
Thank you.
Dave
Hi. Thank you so much for tutorial. I mail merge infrequently and find your website very helpful.
I have to mail merge an agenda with different breakout groups.
In my excel, I have the breakouts in different colours (eg: Red (in red font), Blue (in Blue font) etc)
How can I ensure the merge keeps their actual colours eg: Reg is in red, Blue is in blue colour?
obviously each person has a different breakout group schedule to another person. So it is not possible to change font colour individually ...it will drive me crazy (6 breakouts x 70 people).
Many thanks
Sandy
Hello, Sandy,
The point is that mail merge works with the Data source and doesn't import the formatting from Excel.
I am trying to pull an amount such as AUD 190,000 over from an excel sheet using mail merge but it is showing AUD190,000 without the space between the AUD and the 190,000. Is there a switch I should use? It is showing properly in the excel cell. Thanks!
Hello, Paige,
Please try to use a custom formatting. Format Cells -> Custom.
Hi Svetlana,
congratulations tutorial.
when using mail merge fields in Word I appear in many places. Database excell values are 2 decimal places in my Word file appears with 13 decimal places . This appeared today use much mail goes but there were no problems . thank you
Hi,
Is it possible for mail merge to pick up formulas like vlookups?
Hello, Andrew,
You can use Excel tables with VLOOKUP columns for Mail merge.
I am stumped! I bought a mailing list (in an excel spreadsheet) and I'm mail merging with Word on to labels--and everything looks great EXCEPT I have 5 rows (+1 label) blank in the middle of every page.
It doesn't seem to matter which spreadsheet I use as my data source, I still get the blank labels. Any ideas what I'm doing wrong?
I have an excel database from which I produce numerous different documents in word. When I select certain records from the recipient list in excel to use in a merge in a word document (usually not in consecutive order), what I end up with is the last record in my selection. I then have to scroll back through the end result in order to print the records I initially chose. If I'm given the option to select certain records from the recipient list, why do I get the extra records in my merge result and not the specific ones I chose?