So much time can be saved when we merge from Excel to Word. We can create and send letters and emails with a large mailing list in a shorter span of time with the mail merge.
- Convert excel to word by copy and paste. Convert excel to word by insert as object. Convert excel to word by copy and paste. The most direct and easiest way is to copy the worksheet contents in Excel, then paste them in the Word document. Open the Excel worksheet you need, and select the contents range you need to convert to Word document.
- Copy and paste the email addresses from Excel to Word document as Keep Text Only. See screenshot: 2. Select the emails and click Home Replace to display Find and Replace dialog. See screenshot: 3. In the Find and Replace dialog, enter ^p into Find what textbox, and; into Replace with textbox. See screenshot: 4. Click Replace All.
Create your Excel Spreadsheet. The most important step in the mail merge process is to set up. Mail Merge - Create an Excel Spreadsheet with Column headings, add data, and merge the data into a MS Word Document. Very easy and fun. Create a personal l.
In order to mail merge from Excel to Word, we need two files:
- Microsoft Word file with the letter template
- Microsoft Excel file with the list of recipients, where we allocate one line for each recipient
Mail merge Excel to Word letter
The MS Word file will look like the example below.
Figure 1. Mail merge Excel to Word letter
Excel mail merge template
The MS Excel mail recipient file will look like this:
Figure 2. Excel mail merge template
Note that for each recipient, we have entered the First Name, Last Name, Address and Country of residence.
Save both files in the local drive for easy reference.
Mail merge from Excel to Word
In order to mail merge Excel to Word, we will make use of the Mail Merge Wizard.
Merge Excel Into Word Form
Step 1. Open the MS Word file with the letter template
Step 2. Click Mailings tab, then Start Mail Merge button and select Step by Step Mail Merge Wizard from the drop-down menu.
Figure 3. Mail Merge Wizard in Excel
The Mail Merge dialog box will appear, featuring 6 steps to mail merge Excel to Word.
Merge Excel In Word
Step 3. Select Letters as document typeand click Next: Starting document at the bottom right corner of the window.
Figure 4. Mail Merge Wizard Step 2 of 6: Starting Document
Step 4. Tick Use the current document and click Next: Select recipients for Step 3 of 6
Figure 5. Mail Merge Wizard Step 3 of 6: Select Recipients
Step 5. Minimize the Word file and open the Excel mail recipients file from the local drive
Step 6. Click Browse and the Select Data Source dialog box will appear.
Figure 6. Browse for existing recipients list
Step 7. Browse and select the Excel mail recipients file.
Figure 7. Browse and select the Excel mail recipients file to merge
Step 8. The Select Table dialog box will pop-up. Click OK.
Figure 8. Select table to merge data from Excel to Word
Step 9. The Mail Merge Recipients list will appear. Let us go through the list and check or uncheck recipients that we want to add or remove. Click OK afterwards.
Figure 9. Selecting mail merge recipients
Step 10. Click Next: Write your letter
Figure 10. Mail Merge Wizard Step 4 of 6: Write your letter
Step 11. It is time to write the letter and complete the details by inserting the recipients. Click the space under the date in the Word document and click Mailings tab, Insert Merge Field.
Figure 11. Insert Merge Field in Mail Merge Wizard
We will see that the columns in the Excel file First Name, Last Name, Address and Country are already merged into our Word document.
Step 12. Next, we insert the necessary merge fields into our Word document to complete our letter. Insert the fields as shown below in bold letters.
Figure 12. Inserting the mail merge fields from Excel to Word
Step 13. Click Next: Preview your letters as Step 5 of 6.
Figure 13. Mail Merge Wizard Step 5 of 6: Preview your letters
Step 14. We will now be able to preview our letter with the first recipient. Click on the right arrow to scroll to the next recipients.
Figure 14. Merge Excel to Word recipient #1
Step 15. After scrolling through the recipients, we can now move on to the last step to complete the merge. Click Next: Complete the merge.
Figure 15. Mail Merge Wizard Step 6 of 6: Complete the merge
Step 16. Congratulations! The merge is now completed. We can either print the letters or edit individual letters.
Figure 16. Output: Mail merge Excel to Word
When we click Edit individual letters, this pop-up window will appear. Click All to merge all letters into one new document.
Figure 17. Merge letters
Word will then display a document containing all the letters with each letter showing a different recipient. Save the merged Excel to Word document to finalize the changes.
Figure 18. Final result: Mail merge Excel to Word
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Performing a Mail Merge is a great way to generate personalized letters or emails.
There are three documents involved in the mail merge process:
- Your main document
- Your data source
- Your merged document
NOTE: The examples shown below contain sample screen shots from Microsoft Word 2016. If you have a different version of Word, there may be some slight differences however, the same basic program flow should apply.
Step 1: Create your Excel Spreadsheet
The most important step in the mail merge process is to set up and prepare your data. You’ll use your Excel spreadsheet as the data source for your recipient list.
Here are some tips to prepare your data for a mail merge:
- Make sure the column names on your spreadsheet match the field names you want to insert in your mail merge
- All data to be merged is present in the first sheet of your spreadsheet
- All data entries with percentages, currencies, and zip codes are correctly formatted in the spreadsheet so that Word can properly read their values
- The Excel spreadsheet to be used in the mail merge is stored on your local computer
- Changes or additions to your spreadsheet are completed before it’s connected to your mail merge document in Word
Step 2: Create a mail merge document
When creating a mail merge you have the ability to use an existing document/letter.
In Word, open the existing file and press the ‘Mailings’ tab in the main menu.
On the Mailings tab, choose the ‘Start Mail Merge’ button, a list of different types of documents will drop down (i.e. Letters, E-mail Messages, etc.). choose the kind of merge you want to run.
Now choose the ‘Select Recipients’ button and choose ‘Use an Existing List’
Merge Excel Into Word Table
Browse to find your Excel spreadsheet you previously saved, and then choose ‘OK’.
NOTE: Now the Excel spreadsheet is connected to the mail merge document you’re creating in Word.
Step 3: Inserting the merge fields
You will notice menu items are now active that were not previously
You can insert one or more mail merge fields that pull the information from your spreadsheet into your document.
On your Word document, highlight the field you want to populate with the data from Excel.
On the Mailings tab, choose the ‘Insert Merge Field’ button, a list of the column headers on your saved excel document will drop down (i.e. Company Name, etc.). Choose the appropriate field you want to merge and choose Insert.
The highlighted field will be replaced with the merge field (i.e. «Company», etc.)
Repeat step 3 for each of the fields you want to merge and choose Close when done.
Now choose ‘Save’.
Step 4: Previewing the mail merge
After you insert the merge fields you want you can now preview the results to confirm that the document is set-up the way you want.
On the Mailings tab, choose ‘Preview Results’. The merge fields will be populated with the data on the first row of your excel document.
To move through the records in your data source and view how they will appear in the document choose the right arrow to advance or the left arrow for the previous record.
Step 5: Merging and Saving your mail merge
On the Mailings tab, choose the ‘Finish & Merge’ button, a list of different types of merges will drop down (i.e. Edit Individual Documents, Print Documents and Send Email Messages) choose ‘Edit Individual Documents’. A ‘Merge to New Document’ pop-up box will appear, choose ‘OK’.
You will now have a separate letter for each of the rows on the excel document which you can print, email, etc.
Step 6: Saving your mail merge document
When you are done working with your merged documents, save and close it as you would any other Word document. Once closed the mail merge template will still be open. If you plan to reuse it in the future, save it and it will stay connected to your data source.
NOTE: A mail merge can just as easily be an email merge. Add a column to your excel spreadsheet that contains the email address to send each merged letter to. Under 'Finish and Merge', select 'Send Email Messages' and for the TO specify the column name that contains the email address and specify the SUBJECT to use.