HOME
PRODUCTS
DOWNLOADS
SUPPORT
BLOG
ABOUT US
PURCHASE
How to mail merge from Excel to Word by Svetlana Cheusheva
May
Excel, Excel tips, Merge data in Excel
91 Comments
8 Mail Merge from Excel to Word can be a real time-saver when it comes to sending large 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
Excel: featured articles Merge multiple sheets into one
Prepare an Excel spreadsheet for Mail Merge
Compare 2 Excel files/sheets for differences
How to mail merge from Excel to Word, step-by-step Mail merge with dates, currencies and other numbers
Сompare 2 columns in Excel for matches and differences
Mail merge shortcuts
Merge 2 columns in Excel
Mail Merge basics
Merge Excel rows without losing data
Doing a mail merge may look like a daunting task, but in fact the process is pretty simple.
Excel INDEX / MATCH functions instead of VLOOKUP
It may help to get the insight, if you think of the mail merge process in terms of 3 documents.
How to identify duplicates in Excel: find, count, filter, and more Change text case to UPPERCASE, lowercase, Proper Case
1. Word mail merge document with codes for the personalized fields.
CONCATENATE in Excel: combine text strings, cells and columns
2. Excel mail merge source file with information about the recipients, one row for each recipient.
How to Vlookup to return multiple matches in Excel
3. The final Word document with the personalized letters, emails, envelops etc.
3 ways to remove spaces between words / numbers in Excel cells
The goal of the mail merge is to combine the data in File 1 and File 2 to create File 3.
Preparing the Excel spreadsheet for Mail Merge When you run a mail merge, your Excel file will get connected to your Word mail merge document, and Microsoft Word will be pulling the recipients' names, addresses and other details directly from your Excel worksheet. So, before starting the merge in Word, make sure your Excel file has all the information you want to include such as first names, last names, salutations, zip codes, addresses, etc. If you want to add more contacts or other information, you'd better make the changes or additions in your Excel sheet now before running the mail merge. Important things to check: The columns in your Excel sheet should match the fields you want to use when doing a mail merge. For instance, if you want to address your readers by the first name, be sure to create separate columns for first and last names. If you want to sort the recipients by state or city, verify that you have a separate State or City column.
Excel VLOOKUP tutorial for beginners - syntax and formula examples How to remove empty rows in Excel COUNTIF in Excel - count if not blank, greater than, duplicate or unique
Outlook: featured articles Merge duplicate contacts in Outlook Remove duplicate emails in Outlook quickly and safely Сreate and use e-mail templates in Outlook
If your Excel file includes dates, times, currency values, or postal codes that begin or end in 0, see how to correctly format mail merge numbers, dates and currency. If you create an Excel spreadsheet by importing information from a .csv or a .txt file, then use the Text Import Wizard, as explained in Importing CSV files into Excel. If you want to export Outlook contacts, the following article may be helpful - How to export Outlook contacts to Excel.
Excel formulas
Excel functions Excel PivotTables
Excel charts
How to mail merge from Excel to Word
1. If you have already composed your letter, you can open an existing Word document, otherwise create a new one. 2. Choose what kind of merge you want to run. Switch to the Mailings tab > Start Mail Merge group, and select the mail merge type - letters, email messages, labels, envelopes or documents. We are choosing Letters.
Excel consolidation Excel time
Google Docs
In the Select Data Source dialog, browse to your Excel sheet and click Open. If Word prompts you to select a table, do this and click OK.
5. If you want to include only some of your Excel entries, then click the Edit Recipient List button in the Start Mail Merge group.
The Mail Merge Recipients dialog opens 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 the Refine Recipients List section. Okay, we are finished with the recipients list and you are ready to start on the letter. Type the text as you usually do in a Word document or copy/paste from an external source. 6. Add placeholders. Now you need to add placeholders for the Address Block and Greeting Line for Mail Merge to know exactly where to add the data. To add a placeholder, click the corresponding button on the ribbon Mailing > Write & Insert Fields.
Depending on the placeholder you are adding, a dialog box will appear with various options. Select the desired options, verify the results under the Preview section and click OK. You can use the right and left arrows to switch to the next or previous recipient's preview.
When done, the corresponding placeholder will appear in your document, as shown in the screenshot below:
For some letters, 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 cases you may wish to place the recipient's data within the letter text to personalize it further. To do this, click Insert Merge Field and choose the data you want to insert from the dropdown list.
7. Preview the letter. To make sure the recipients data correctly appear in the letter, click the Preview Results button on the Mailing tab.
You can use the left and right arrows to view each letter with the recipient's data.
8. 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.
If you want to make some edits before printing / emailing, click Edit Individual Documents. A new document will open and you will be able to make the desired changes in each particular letter. 9. Save the mail merge document. You save the mail merge file as a usual Word document by clicking the Save button or pressing Ctrl+S . Once it is 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 Microsoft Word prompts you to retain that connection.
In addition to the Mail Merge options available on the ribbon that we've just discussed, Microsoft Excel provides exactly the same features in the form of the Mail Merge Wizard. You can start the wizard via Mailings tab > Start Mail Merge > Step-by-Step Mail Merge Wizard...
Once clicked, the Mail Merge Wizard will open on the right of your screen and walk you through the merge process step-by-step.
In my opinion, working with the ribbon is more convenient because you can view all the merge options at once and quickly pick the needed one. However, if you are doing the mail merge for the first time, you may find the wizard's step-by-step guidance helpful.
How to mail merge with dates, currencies and other numbers When doing a mail merge from Excel to Word, you need to pay special attention to numeric values such as dates, currency and numbers. This part of our mail merge tutorial will show you how to format such values properly.
Format zip codes and other values with zeros in Excel To ensure that all of your numbers come through a mail merge without losing any leading zeros, you simply need to format the ZIP code column as text in the Excel worksheet. The same applies to any other numeric values with zeros. 1. Select the ZIP code column, right-click it, and choose Format Cells... from the context menu.
2. On the Number tab, select Text and then click OK.
Mail merge with dates and numbers using Dynamic Data Exchange If your Excel spreadsheet contains dates, decimal numbers, or currencies, you can use Dynamic Data Exchange to make sure these values have the correct formatting after coming through the merge. Before staring the mail merge, perform the following steps in Microsoft Word. 1. Go to File > Options > Advanced. 2. Scroll down to the General section, select the check box "Confirm file format conversion on open" and click OK.
Start your mail merge, as explained earlier in the article - How to mail merge from Excel to Word. Because Dynamic Data Exchange is turned on, you may receive a few prompts, and you just click Yes or OK. The only difference from the usual mail merge will be selecting the Data Source. Please proceed with the following steps. 1. When selecting the recipients, click Mailings > Select Recipients > Use an Existing List, as usual. 2. Browse to your spreadsheet, double-click it, choose MS Excel Worksheets via DDE (*.xls), then click OK.
Note. If the MS Excel Worksheets via DDE option is not available, check Show all box in the lower left-hand corner. 3. Click Entire Spreadsheet, and OK.
Now you can continue with your Excel mail merge in the usual way. Tip. To prevent multiple prompts displayed by Word every time you open the data file, clear the "Confirm file format conversion on open" check box (Word Options >Advanced >General) after connecting to your mailing list.
Format date, time, number and currency during mail merge in Word If you want to have the numbers, dates or currencies formatted in a different way than in your Excel mail merge source file, you can do this directly in a Word document. 1. Select the merge field whose format you want to change. This can be Date, Currency, Percent or some other field. 2. Press Shift+F9 to display the field coding. For example, if you have selected the Currency field, you should see something similar to this: {MERGEFIELD CURRENCY}. 3. Add one of the picture switches provided below to the field. For example, if you want 3000 to appear as $3,000, you add \# $,0 and get the following field code: {MERGEFIELD CURRENCY\# $,0}. 4. Press F9 to update the field. Then press Shift+F9 to view the result.
Format numbers, currency and percent Numeric picture switch
Full field code
Example
Description
\# 0
{MERGEFIELD CURRENCY\# 0}
3000
Rounded whole numbers
\# ,0
{MERGEFIELD CURRENCY\# ,0}
3,000
Rounded whole numbers with a thousand separator
\# ,0.00
{MERGEFIELD CURRENCY\# ,0.00}
3,000.00
Numbers with two decimal places and a thousand separator
\# $,0
{MERGEFIELD CURRENCY\# $,0}
$3,000
Rounded whole dollar with a thousand separator
\# $,0.00
{MERGEFIELD CURRENCY\# $,0.00}
$3,000.00
Dollar with two decimal places and a thousand separator
\# "$,0.00; ($,0.00);'-'"
{MERGEFIELD CURRENCY\# "$,0.00; ($,0.00);'-'"}
($3,000.00)
Dollar, with brackets around negative numbers and a hyphen for zero values (0)
\# 0.00%
{MERGEFIELD PERCENT\# 0.00%}
1.00%
Percent with two decimal places
\# 0%
{MERGEFIELD PERCENT\# 0%}
1%
Rounded whole percent
Tip. Instead of the Dollar sign ($), you can use any other currency signs, e.g. € or £.
Format date and time Similarly to numbers and currency, you add a picture switch to change the Date / Time field format. For example, to display a date as 20, May 2014 you add the following picture switch: \@"d, MMMM yyyy". The resulting field code will be like this: {MERGEFIELD Date\@"d, MMMM yyyy"}. You can find a few more date/time picture switches in the table below.
\@"dd/MMM/yyyy"
20/May/2014
\@"d/MMM/yy"}
20/May/14
\@"d MMMM yyyy"}
20 May 2014
\@"d, MMMM yyyy"
20, May 2014
\@"dddd, d MMMM yyyy"
Tuesday, 20 May 2014
\@"ddd, d MMMM yyyy"
Tue, 20 May 2014
\@ "h:mm am/pm"
10:45 PM
\@ "HH:mm"
10:45
\@ "HH:mm:ss"
10:45:32
Tip. You can use any other d, M, y expressions for date and time, but remember that uppercase M denotes months and lowercase m is used for minutes.
Change the format of the current date and time If you have added the DATE field that displays the current date and the TIME field that displays the current time to your mail merge document, you can change their format in a more visual way. 1. Select the Date or Time field the format of which you want to change. 2. Press Shift-F9 to display the field coding. If you have selected the date field, you should see something like this: {DATE \@ "M/d/yyyy"}. 3. Right-click the field and choose Edit Field... from the context menu.
4. In the Filed dialog, make sure the Field name is set to Date. Then select the desired format under Date formats and click OK.
Tip. If you want to preserve formatting during updates, select the corresponding check box in the lower right-hand part of the dialog window.
Mail Merge shortcuts If you need to do a mail merge from Excel to Word on a regular bases, learning a few shortcuts may save you some more time. All of the below shortcuts work in Microsoft Word 2016, 2013 and 2010. They might probably work in Word 2007 as well, though I have not tested in lower versions and cannot state this with certainty : ) Shortcut
Description
Alt+F9
Switch between all field codes and their results in a mail merge document.
Shift+F9
Expose 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 Excel's 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+p
Insert the PAGE field that displays the page number.
Alt+Shift+t
Insert the TIME field that displays the current time.
Alt+Ctrl+l
Insert LISTNUM field.
Hopefully, this information has been helpful and now you know how to perform mail merge in Excel and Word properly. In the next article, we will investigate how to quickly make and print labels from Excel. Please stay tuned and thank you for reading!
91 Responses to "How to mail merge from Excel to Word" 1
Jeff Pope says: July 24, 2014 at 10:07 pm Is there anyway to bring the colour of the excel cell over.? Reply Svetlana Cheusheva says: July 25, 2014 at 8:02 am 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! Reply
2
Anonymous says: August 9, 2014 at 7:43 pm Hi Jeff I want to personalize a letter with different case numbers. Reply Svetlana Cheusheva says: August 11, 2014 at 10:49 am Sorry, I cannot follow you. Could you elaborate on the task, please? Reply
3
Kuldeep Kaushik says: August 21, 2014 at 12:56 pm 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. Reply Svetlana Cheusheva says: August 22, 2014 at 12:49 pm Hello Kuldeep, Please send me a sample workbook with your data at
[email protected] Most likely, the if/else logic should be implemented in Excel columns, and the result passed to Word. Reply
4
Jo59 says: September 3, 2014 at 11:34 am 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. Reply
5
Jo59 says: September 3, 2014 at 11:38 am I mean having for example "Hi #FirstName, #ObjMesForFirstname" #fields coming from list as #AdressBlock or #GreetingLine mentionned in your article Reply
6
JenJams says: September 4, 2014 at 6:31 am 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 ? Reply
7
MaryBeth says: October 10, 2014 at 4:41 pm 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? Reply
8
beth says: October 17, 2014 at 4:58 pm 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 Reply
9
Serena Grayson says: October 24, 2014 at 1:48 pm How do I get a document (11x8.5), that has cards set 3 up on the page to change personalization records on each card? Reply Svetlana Cheusheva says: October 27, 2014 at 4:08 pm Hello Serena, I am sorry, your task is not quite clear. Please describe it in more detail. Reply Allan says: December 30, 2014 at 12:57 am 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. Reply
10
rinku says: October 25, 2014 at 5:51 pm 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. Reply Svetlana Cheusheva says: October 28, 2014 at 11:21 am 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. Reply
11
Miles says: November 6, 2014 at 1:12 am 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. Reply
12
Jarek says: November 7, 2014 at 8:53 am 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 Reply
13
Amanda says: November 7, 2014 at 3:15 pm 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? Reply
14
Jialin says: November 9, 2014 at 6:10 am 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 mailmerged.(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! Reply Svetlana Cheusheva says: November 14, 2014 at 1:55 pm 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. Reply
15
Lokesh says: November 12, 2014 at 12:03 pm Hi, can u do mail merge from Excel to Word by vba coding also. Reply
16
Maggie says: November 18, 2014 at 4:26 pm 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 Reply Svetlana Cheusheva says: November 20, 2014 at 2:02 pm Hello Maggie, You can fulfill your task using VBA functions. If you can send a sample workbook to
[email protected], our support team will try to help. Reply
17
Rachel says: November 21, 2014 at 10:55 am 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 Reply
18
Don says: November 29, 2014 at 1:48 pm 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. Reply Svetlana Cheusheva says: December 1, 2014 at 4:32 pm Hello Don, You can save the mail merge file as a usual Word document by clicking the Save button or pressing Ctrl+S. Reply
19
Don says: December 3, 2014 at 6:13 pm 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 Reply Svetlana Cheusheva says: December 12, 2014 at 4:31 pm 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
[email protected] and our support team will try to help. Reply Allan says: December 30, 2014 at 12:46 am 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. Reply
20
Henry says: December 11, 2014 at 3:40 pm 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. Reply Svetlana Cheusheva says: December 12, 2014 at 4:19 pm Hello Henry, Please send screenshots of the source data and the result to
[email protected] and our support team will try to help. Reply
21
Saqib says: January 2, 2015 at 10:46 am 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 Reply
22
Rose says: January 15, 2015 at 7:45 am 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 Reply
23
Tova Bud says: January 19, 2015 at 7:55 pm 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 Reply
24
Ian says: February 5, 2015 at 8:05 pm 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. Reply Alexander says: February 12, 2015 at 4:41 pm 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. Reply
25
Anonymous says: February 7, 2015 at 7:15 am 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!!! Reply
26
Lisa says: February 10, 2015 at 4:08 pm 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. Reply Alexander says: February 11, 2015 at 4:17 pm Hello Lisa, Please try to apply the “Percentage” format to your cells in Excel. Reply
27
Ben says: March 4, 2015 at 2:51 am 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. Reply
28
Ben says: March 4, 2015 at 3:21 am how can i re-arrange numbering on the merged document? Reply
29
sherjhe says: March 12, 2015 at 10:01 am 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. Reply
30
Sam says: March 21, 2015 at 10:57 pm The manual is great! There is also Gendo (www.gendo.me) that does it a bit faster ( merges data into docx template). Reply
31
Colleen says: March 25, 2015 at 2:36 pm 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! Reply
32
Bev says: March 31, 2015 at 8:05 pm 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. Reply
33
Susan says: April 1, 2015 at 3:04 pm 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 Reply
34
Yukthi says: April 8, 2015 at 6:53 am 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 Reply
35
Erin says: May 13, 2015 at 3:34 pm 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? Reply
36
Anand More says: May 28, 2015 at 10:40 am 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, Reply
37
Stefan says: June 16, 2015 at 10:31 am 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. Reply
38
Charlotte says: June 22, 2015 at 3:18 pm 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? Reply
39
Emil says: July 7, 2015 at 11:22 am 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! Reply
40
Vinisha Anand says: August 17, 2015 at 3:16 pm Thanks... In this tutorial very easily the whole function have been described. Reply
41
Marlene says: August 18, 2015 at 6:14 pm 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? Reply
42
teresa says: August 29, 2015 at 6:31 pm 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? Reply
43
Andrew says: September 22, 2015 at 4:37 am Hi, Is it possible for mail merge to pick up formulas like vlookups? Reply Maria Azbel (Ablebits.com Team) says: February 16, 2016 at 1:03 pm Hello, Andrew, You can use Excel tables with VLOOKUP columns for Mail merge. Reply
44
Claudiu says: October 22, 2015 at 11:42 am 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 Reply
45
Paige says: November 24, 2015 at 7:16 pm 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! Reply Maria Azbel (Ablebits.com Team) says: February 16, 2016 at 11:32 am Hello, Paige, Please try to use a custom formatting. Format Cells -> Custom. Reply
46
Sandy says: November 30, 2015 at 6:10 am 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 Reply Maria Azbel (Ablebits.com Team) says: February 16, 2016 at 12:07 pm Hello, Sandy, The point is that mail merge works with the Data source and doesn't import the formatting from Excel. Reply
47
Dave Foltz says: December 16, 2015 at 4:39 pm 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 Reply
48
Linda says: January 30, 2016 at 3:07 pm 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. Reply Maria Azbel (Ablebits.com Team) says: February 16, 2016 at 1:02 pm 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. Reply Frank says: January 3, 2017 at 2:22 pm 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 Reply
49
RAJESHKUMAR PATEL says: February 17, 2016 at 9:09 am 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.... Reply
50
peggy says: February 18, 2016 at 7:34 pm 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! Reply
51
Ashiq says: March 3, 2016 at 12:10 pm 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. Reply
52
Prakash M says: March 16, 2016 at 3:28 am 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. Reply
53
Eryq says: March 24, 2016 at 7:34 pm 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 Reply
54
Shehbaz Hussain says: May 9, 2016 at 12:55 am 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 Reply
55
Carrie says: May 11, 2016 at 12:59 am 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? Reply
56
vikki says: May 13, 2016 at 11:11 am 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 Reply
57
JJ says: May 27, 2016 at 3:47 am 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. Reply
58
Jhess says: June 23, 2016 at 3:40 pm 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! Reply
59
Jessie says: June 29, 2016 at 2:55 pm 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! Reply
60
Brindha says: August 19, 2016 at 7:17 am 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 Reply
61
Pat says: August 25, 2016 at 8:11 pm We merge our letters with information from Donor Perfect into a csv file in excel. We include a dollar amount when appropriate. When the excel file was set up, one merge field was Amount_. The DP software will not recognize that heading. It is looking for Amount. I spoke to the tech at DP, and he said that it was a problem exclusive to that file that was set up in our software. How do I correct this merge field? How do I find the merging document to correct it? I have tried everything and cannot find how to correct a merge field. Thanks for any information you can share. Reply
62
Tania says: September 23, 2016 at 2:32 pm I was wondering if you may be able to advise me please? I am attempting to send out a mail merge to companies using a large excel spreadsheet as the data source for the merge. Many of the companies have numerous employees and when I do the merge, it produces separate letters where I would like the individuals names to all be on one letter (save postage). So far I have had to slowly go through and cut and paste employees names from the letters below and then delete that letter (very time consuming when you are sending out hundreds!) I have tried merging the cells for the companies with multiple employees hoping they would all go onto one letter but that did not work. Please would you be able to offer any advice?! Many thanks Reply
63
Ravasab says: October 16, 2016 at 7:12 am Sir i am using microsoft office 2007 and mail merged also done, but actually I ant thousand separator as like = 21,22,85,535.00 but not possible, please help Reply
64
François says: November 14, 2016 at 4:31 pm Hi, I am using Excel and Word 2013... the problem is that making a mailmerge with DDE does work anymore... do not know what to do... and using the OLE DE Database is changing my cell total format (it becomes from 767.73 to 767.73000000000002)....... would you have a way to change the toggle to round the number or respect the format??? thanks François Reply
65
Pam says: November 20, 2016 at 9:07 pm When I do a mail merge I'm inserting greeting line and address block from a spreadsheet in excel from Gift Works. When I get the address block to be correct with first and last name this changes the greeting line to also include last name. If I change greeting line and remove last name by not matching a field to it then the address block automatically changes to only first name. I have had this problem for over a year now. Any help is appreciated. Reply
66
Kevin says: December 15, 2016 at 7:56 pm This is awesome! Thanks so much for the walkthrough. Reply
67
Ashton says: February 6, 2017 at 10:53 pm Hey! If I were needing to make a list in Word with some of the information from my Excel spreadsheet, how would I input the mail merge fields so that I can merge several different recipients over? Reply
68
Liz says: February 8, 2017 at 7:41 am I need to please send a prepared email with/without word document attached. The email will not contain any data to merge from an excel spreadsheet . But just be able to mail merge email addresses to send to all recepients or just selected ones on spreadsheet using windows 10. Reply
69
Ranjit says: February 20, 2017 at 6:25 pm I want to mail merge using data in column C of an Excel file. C has a formula that uses data in column A. I have a hundred rows in the Excel sheet. For some of those hundred, A has not yet been entered, so C shows #N/A. In the Mail Merge, I don't want those records to print for which it finds #N/A in column C. How do I do that comparison? Using Skipif, Equal, and "#N/A"? It didn't work. Reply
70
Ali says: March 14, 2017 at 1:11 pm I have xls file: 1st sheet called (MyData) with multiple rows (A1:A255)(employee list) and columns (A1:P1) (employee details) with data. 2nd sheet called (MyChart), in that sheet I have inserted a drop down menu created for (A1:A255) where I can choose one item (row) and the system shows me that row and graphs. so my goal is to merge that graphs to MS Words file. Number of MS Word files will be identical to number of employees (A1:A255) pls help me Reply
71
yoel says: August 9, 2017 at 2:22 pm thank you for this very helpful indeed useful article... it does really help me with our examination on ICT ... THANKS!!! Reply
72
Joy Hardin says: August 30, 2017 at 2:42 am I have an Excel spreadsheet with some names in red and most in black. I tried to print labels but they all print in black; how can I get the labels to print in the color used in Excel? Reply
Post a comment Name
Name
[email protected]
E-mail (not published)
Send
Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Copyright © 2002 - 2017 Ablebits.com. All rights reserved. Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Excel date
Excel tutorials
Excel tips
Google Sheets
Outlook duplicates
Outlook templates
Log in
4. Connect your Excel spreadsheet and Word document. 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 recipients data.
Excel formatting
Register
3. Select the recipients. On the Mailings tab, click Select Recipients > Use Existing List.
Merge data in Excel
Sign in
Example
Excel Vlookup
Excel duplicates
When your Excel spreadsheet is set up and reviewed, you are ready to run the mail merge. In this example, we will be merging the letter with a recipient list in Word 2010. If you are using Word 2013 or Word 2016, the steps will be exactly the same.
Date/Time picture switch
Video