How to Do a Microsoft Word Mail Merge From an Excel Spreadsheet

Learn to merge names and addresses or other data from Excel into Word

How to Automate Mail Merge through VBA in Microsoft Excel
Credit: www.exceltip.com / Google Images

Using Microsoft's Mail Merge feature, you can send the same document with slight changes to a large number of recipients. The term "merge" comes from the fact that one document (a letter, for example) is merged with a data source document, such as a spreadsheet.

Word's Mail Merge feature works seamlessly with data from Excel. While Word also allows you to create a data source, options for using this data are limited. If you already have your data in a spreadsheet, it doesn't make sense to retype all the information into Word's data source.

Preparing the Data for Mail Merge

Theoretically, you can use any Excel worksheet in a Word mail merge function without any special preparation. However, you should take some time to prepare your worksheet to optimize the mail merge process.

Organize the Spreadsheet Data

Your Exxcel data should be organized neatly into rows and columns. Think of each row as a single record and each column as a field you are going to insert into your document. If you need a refresher, check out an Excel data-entry tutorial.

Create a Header Row

Create a header row for the sheet you intend to use for the mail merge. A header row is a row containing labels that identify the data in the cells beneath it. Excel can be finicky sometimes about differentiating between data and labels, so make these clear by using bold text, cell borders and cell shading that are unique to the header row. This ensures Excel differentiates it from the rest of your data.

Later when you are merging the data with the main document, the labels appear as the names of the merge fields, so there is no confusion as to what data you are inserting into your document. Furthermore, it's a good practice to label the columns, as it helps prevent user error.

Put All Data on a Single Sheet

The data you intend to use for the mail merge must be on one sheet. If it is spread across multiple sheets, you need to combine the sheets or perform multiple mail merges. Also, make sure the sheets are clearly named, as you have to select the sheet you intend to use without viewing it.

Associating a Data Source in a Mail Merge

To associate your prepared Excel spreadsheet with your Word document.

  1. On the Mail Merge toolbar, click the Open Data Source button.
  2. In the Select Data Source dialog box, navigate through the folders until you find your Excel workbook. If you are unable to find your Excel file, make sure All data sources is selected in the drop-down menu labeled Files of type
  3. Double-click on your Excel source file or select it and click Open.
  4. In the Select Table dialog box, select the Excel sheet that contains the data you want to merge with your document.
  5. Make sure the check box beside First row of data contains column headers is checked.
  6. Click OK.

Now that the data source has been associated with the main document, you can begin entering text or editing your Word document. You cannot, however, make changes to your data source in Excel. If you need to make changes to the data, you must close the main document in Word before you can open the data source in Excel.

Inserting merge fields into your document is easy:

  1. Click the Insert Merge Field button on the Mail Merge toolbar to open the Insert Merge Field dialog box.
  2. Highlight the name of the field you want to insert from the list and click Insert.
  3. The box stays open, allowing you to insert more fields. If you insert more than one field in succession, Word does not automatically add space between the fields in your document; you must do this yourself after you close the dialog box. In your document, you will see the field name surrounded by double arrows.
  4. When you are done, click Close

Inserting Address Blocks and Greetings

Microsoft's Mail Merge feature allows you to insert address blocks and greeting lines. By clicking the respective button on the toolbar, you can insert several fields at once, arranged in common variations.

The insert address block button is the one on the left; the insert greeting line is on the right.

When you click on either button, Word displays a dialog box that gives you some options on the fields you want to insert, how you want them to be arranged, what punctuation to include, and other options. While this sounds straightforward — and it is if you are using a data source created in Word — it can become confusing when you use an Excel worksheet.

When you added a header row in your worksheet, if you named a field something other than what Word uses as a field name for similar data, Word might match the fields incorrectly. Then, if you use the insert address block or insert greeting line buttons, the data might appear in a different order than you specify because the labels don't match. Microsoft anticipated this and built in a Match Fields feature that allows you to match your field names to the ones Word uses in the blocks.

Using Match Fields to Correctly Map Field Labels

To match fields, follow these steps:

  1. Click on the Match Fields button on the toolbar.
  2. In the dialog box is a list of Word's field names on the left. On the right side of the box, is a column of drop-down boxes. The name in each drop-down box is the field that Word is using for each respective field in the Address block or Greeting line block. To make any changes, select the field name from the drop-down box.
  3. When you are done making changes, click OK.

You can also bring up the Match Fields dialog box by clicking the Match Fields button at the bottom of either the Insert Address Block or Greeting Line dialog boxes, both of which appear when you click the respective toolbar button.

Viewing Mail Merge Documents

A note about formatting before you go on to previewing and printing your merged documents: When inserting merge fields into a document, Word does not carry over the formatting of the data from the data source.

If you want to apply formatting such as italics, bold, or underline, you must do so in Word. If you are viewing the document with fields, you select the double arrows on both sides of the field to which you want to apply the formatting. If you are viewing the merged data in the document, highlight the text you wish to change.

Any change carries throughout all the merged documents, not just the individual one.

Previewing the Merged Documents

To preview the merged documents, click the View Merged Data button on the Mail Merge toolbar. This button works like a toggle switch, so if you want to go back to viewing just the fields and not the data they contain, click it again.

Navigate through the merged documents by using the navigational buttons on the Mail Merge toolbar. They are, from left to right: First RecordPrevious RecordGo To RecordNext Record, and Last Record.

Before you merge the documents, preview them all, or as many as you can to verify that everything merged correctly. Pay particular attention to punctuation and spacing around the merged data.

Finalize the Mail Merge Document

When you are ready to merge the documents, you have two choices.

  • Merge to the Printer: The first is to merge them to the printer. If you choose this option, the documents are sent to the printer without any modification. You can merge to the printer by clicking the Merge to Printer toolbar button. 
  • Merge into a New Document: If you need to personalize some or all of the documents (although, you would be wise to add a note field in the data source for personalized notes) or make any other changes before you print, you can merge them to a new document. When you merge to a new document, the main document and data source remain intact, but you have a second file containing the merged documents. To do this, click the Merge to New Document toolbar button.

Whichever method you choose, you are presented with a dialog box in which you can tell Word to merge all records, the current record, or a range of records.

Click the option button next to your choice and then click OK.

If you want to merge a range, you put in the beginning number and the final number for the records you want to include in the merge before you click OK.

If you chose to print the documents, after the dialog box comes up, you are presented with the Print dialog box. You can interact with it the same as you would for any other document.