Word Mail Merge 2013 Short Assignment

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.

  1. Word mail merge document with codes for the personalized fields.
  2. Excel mail merge source file with information about the recipients, one row for each recipient.
  3. 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 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.

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.

  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.

  3. Select the recipients. On the Mailings tab, click Select Recipients > Use Existing List.

  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.

    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 drop-down 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 newdocument willopen 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 switchFull field codeExampleDescription
\# 0{MERGEFIELD CURRENCY\# 0}3000Rounded whole numbers
\# ,0{MERGEFIELD CURRENCY\# ,0}3,000Rounded whole numbers with a thousand separator
\# ,0.00{MERGEFIELD CURRENCY\# ,0.00}3,000.00Numbers with two decimal places and a thousand separator
\# $,0{MERGEFIELD CURRENCY\# $,0}$3,000Rounded whole dollar with a thousand separator
\# $,0.00{MERGEFIELD CURRENCY\# $,0.00}$3,000.00Dollar 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.

Date/Time picture switchExample
\@"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 : )

ShortcutDescription
Alt+F9Switch between all field codes and their results in a mail merge document.
Shift+F9Expose the coding of the selected field.
F9Update the selected filed. Place the cursor anywhere in the field and press F9 to update it.
F11Go to the next field.
Shift+F11Go to the previous field.
Alt+Shift+eEdit 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+fInsert a merge field from your mail merge source.
Alt+Shift+mPrint the merged document.
Ctrl+F9Insert an empty field.
Ctrl+F11Lock a field. The field results won't be updated when the information in Excel's source file changes.
Ctrl+Shift+F11Unlock a field. The field results will be updated again.
Ctrl+Shift+F9Unlink 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+dInsert the DATE field that displays the current date.
Alt+Shift+pInsert the PAGE field that displays the page number.
Alt+Shift+tInsert the TIME field that displays the current time.
Alt+Ctrl+lInsert 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!

In Word Mail Merge is a powerful feature. With Mail Merge Word documents can be used to produce letters, labels, envelopes, and more.

Introduction

Video: Mail Merge

Watch the video (3:52).

Mail Merge is a useful tool that allows you to produce multiple letters, labels, envelopes, name tags, and more using information stored in a list, database, or spreadsheet. When performing a Mail Merge, you will need a Word document (you can start with an existing one or create a new one) and a recipient list, which is typically an Excel workbook.

Optional: If you'd like to work along with the lesson, you can download the examples below:

To use Mail Merge:

  1. Open an existing Word document, or create a new one.
  2. From the Mailings tab, click the Start Mail Merge command and select Step by Step Mail Merge Wizard from the drop-down menu.
    Opening the Mail Merge Wizard

The Mail Merge pane appears and will guide you through the six main steps to complete a merge. The following example demonstrates how to create a form letter and merge the letter with a recipient list.

Step 1:

  • Choose the type of document you want to create. In our example, we'll select Letters. Then click Next: Starting document to move to Step 2.
    Selecting Letters and clicking Next

Step 2:

  • Select Use the current document, then click Next: Select recipients to move to Step 3.
    Selecting Use the current document and clicking Next

Step 3:

Now you'll need an address list so Word can automatically place each address into the document. The list can be in an existing file, such as an Excel workbook, or you can type a new address list from within the Mail Merge Wizard.

  1. From the Mail Merge task pane, select Use an existing list, then click Browse... to select the file.
    Clicking Browse...
  2. Locate your file and click Open.
    Opening the Address List
  3. If the address list is in an Excel workbook, select the worksheet that contains the list and click OK.
    Selecting a worksheet
  4. In the Mail Merge Recipients dialog box, you can check or uncheck each box to control which recipients are included in the merge. By default, all recipients should be selected. When you're done, click OK.
    Selecting recipients to include or exclude
  5. From the Mail Merge task pane, click Next: Write your letter to move to Step 4.
    Clicking Next

If you don't have an existing address list, you can click the Type a new list button and click Create. You can then type your address list.

Step 4:

Now you're ready to write your letter. When it's printed, each copy of the letter will basically be the same; only the recipient data (such as the name and address) will be different. You'll need to add placeholders for the recipient data so Mail Merge knows exactly where to add the data.

To insert recipient data:

  1. Place the insertion point in the document where you want the information to appear.
    Placing the insertion point in the desired location
  2. Choose one of the four placeholder options: Address block, Greeting line, Electronic postage, or More items.
    Clicking Address block...
  3. Depending on your selection, a dialog box may appear with various options. Select the desired options and click OK.
    Adjusting the address block formatting
  4. A placeholder will appear in your document (for example, «AddressBlock»).
    The AddressBlock placeholder
  5. Repeat these steps each time you need to enter information from your data record. In our example, we'll add a Greeting line.
    The GreetingLine placeholder
  6. When you're done, click Next: Preview your letters to move to Step 5.
    Clicking Next

For some letters, you'll only need to add an Address block and Greeting line. Sometimes, however, you may want to place recipient data within the body of the letter to personalize it even further.

Step 5:

  1. Preview the letters to make sure the information from the recipient list appears correctly in the letter. You can use the left and right scroll arrows to view each document.
    Previewing the letters
  2. Click Next: Complete the merge to move to Step 6.
    Clicking Next

Step 6:

  1. Click Print... to print the letters.
    Printing the letters
  2. A dialog box will appear. Click All, then click OK.
    Clicking OK
  3. The Print dialog box will appear. Adjust the print settings if needed, then click OK. The letters will be printed.
    The Print dialog box

Challenge!

  1. Open an existing Word document. If you want, you can use our Practice document (Word document) and Address list (Excel workbook).
  2. Use the Mail Merge Wizard to merge the letter with the recipient list.
  3. Place an Address Block at the top of the page and a Greeting line above the body of the letter.
  4. Print the document.

0 comments

Leave a Reply

Your email address will not be published. Required fields are marked *