How to Mail Merge Address Labels Using Excel and Word Learning to use Mail Merge in Microsoft Word can be a daunting task if you're trying to use Microsoft Help. We'll outline a much more linear process -- creating an address file in Excel, merging in Word, and adding intents and finer details. Re: Mail Merge in Word to create a list of data from multiple rows in Excel Merge_Evaluations1.doc Evaluation Scores.xlsx Hi Pike, I reattached the Word doc and Excel doc. All of the headlines in the word document I am trying to pull from the excel spreadsheet. I am trying to get the mail merge to pull the information plus the Evaluation Rates into the Table in the word doc titled Evaluation Criteria.
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 Doing a mail merge may look like a daunting task, but in fact the process is pretty simple.
It may help to get the insight, if you think of the mail merge process in terms of 3 documents. Word mail merge document with codes for the personalized fields. Excel mail merge source file with information about the recipients, one row for each recipient.
The final Word document with the personalized letters, emails, envelops etc. 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. If your Excel file includes dates, times, currency values, or postal codes that begin or end in 0, see how to correctly.
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. If you want to export Outlook contacts, the following article may be helpful -.
How to mail merge from Excel to Word 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. If you have already composed your letter, you can open an existing Word document, otherwise create a new one. 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.
Select the recipients. On the Mailings tab, click Select Recipients Use Existing List. 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.
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. 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. 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 drop-down list.
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. 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. 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. Select the ZIP code column, right-click it, and choose Format Cells. From the context menu.
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. Go to File Options Advanced. 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 -. 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.
When selecting the recipients, click Mailings Select Recipients Use an Existing List, as usual. Browse to your spreadsheet, double-click it, choose MS Excel Worksheets via DDE (.xls), then click OK. 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. Please stay tuned and thank you for reading! You may also be interested in:.
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 (AnnaMaria). How to overcome this problem? 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.
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. Thanks again for the article! Dear Svetlana Cheusheva: I have a problem while acting upon the method you mentioned above. 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. 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. 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?
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? 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?
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. 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? 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.
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. 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. 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. 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?! I am doing a mail merge from excel to a form. For each person who gets a form, I want the various fields to be located in the same location on the form even though the data may be different lengths. I attempted to do this by making each field the same length for each person, adding spaces and a.
To those that have shorter data. For example, I may have lastname set to 10 characters. I would enter Smith and Johnson as so - Smith.
Smith needing more spaces to reach the total 10. Still the data seems to display after the merge in slightly different locations like on a new line. How can I prevent this?
. In the Mail Merge Manager, click Select Document Type and then choose Create New→Labels. From the Label Products pop-up menu, choose the product.
From the Product Number list, select the correct number for your labels. Click OK to close the Label Options dialog. A table appears. Don’t make any adjustments to the table or click in the table. The insertion cursor should be blinking in the upper-leftmost cell, which will be the only empty cell in the table.
You may have to drag the bottom scroll bar to see the blinking cursor. Section 1 of Mail Merge Manager now displays the name of the Main Document and which type of merge you’re performing. In the Mail Merge Manager, click Select Recipients List and then choose Get List→Open Data Source. Navigate to the Excel (.xlsx) workbook you’re using as the data source and click Open. Select the worksheet or range that has the names and addresses for the data source, and then click OK. Your Word mail merge document is now linked to the worksheet or data range data source in the Excel workbook. The Edit Labels dialog appears.
In the Mail Merge Manager, click Edit Labels. When the Edit Labels dialog opens, you see an empty Sample Label with a blinking insertion cursor. Click the Insert Merge Field pop-up menu and choose the field that will be on the left of the top row of the label. To add more lines to your label, press Return or Enter and then select another field from the Insert Merge Field pop-up menu. Do not click the Insert Postal Bar Code button. Post Office changed how it generates postal bar codes, and Word doesn’t conform to the new specification. Click OK to close the Edit Labels dialog.
You return to your Word document, and your table grid is now filled with a whole bunch of field names in chevrons. Step 3 of Mail Merge Manager opens, but don’t use anything from Mail Merge Manager Step 3 because the Edit Labels dialog takes care of inserting placeholders when making mail merge labels. Step 2 of Mail Merge Manager now shows the filename of the data source document.
(Optional) In the Mail Merge Manager, click Filter Recipients. Filter data and order records. In the Mail Merge Manager, click Preview Results. In the Mail Merge Manager, click Complete Merge.