Preparing a Spreadsheet for a Microsoft Word Mail Merge

Before merging data, prepare your Excel spreadsheet source

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

Microsoft's Mail Merge feature allows you to 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 its own data source, options for using this data are limited.

Further, if you already have your data in a spreadsheet, it doesn't make much sense to retype all the information into Word's data source.

Preparing Your Data for Mail Merge

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

Here are a few guidelines to observe that will help make the mail merge process go more smoothly.

Organize Your Spreadsheet Data

At the risk of stating the obvious, your 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. (Check out the Excel data-entry tutorial if you need a refresher.)

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 below.

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 will ensure Excel differentiates it from the rest of your data.

Later when you are merging the data with the main document, the labels will appear as the names of the merge fields, so there will be no confusion as to what data you are inserting into your document.

Furthermore, it's a good practice to label your 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 will need to combine the sheets or perform multiple mail merges. Also, make sure the sheets are clearly named, as you will need to be able to select the sheet you intend to use without viewing it.

Once you make sure your data is prepared, you're ready for the next step.

Mail Merge for Other Versions of Word

If your version of Microsoft Word is different, check these resources for help with your mail merge:

  • How to Create a Mail Merge in Microsoft Word 2007
  • How to Create a Mail Merge in Microsoft Word 2010
  • How to Create a Microsoft Word for Mac 2011 Mail Merge

The next step in the mail merge process is 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 dropdown menu labeled "Files of type." 
  1. Double-click on your source Excel source file, or select it and click Open.
  2. In the Select Table dialog box, select the Excel sheet that contains the data you wish to merge with your document.
  3. Make sure the checkbox beside "First row of data contains column headers" is checked.
  4. Click OK.

Now that the data source has been associated with the main document, you can begin entering text and/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 by following these steps:

  1. Click the Insert Merge Field button on the mail merge toolbar. The Insert Merge Field dialog box will appear.
  2. Highlight the name of the field you wish to insert from the list and click Insert.
  3. The box will stay open, allowing you to insert more fields. If you insert more than one field in succession, Word will 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.
  1. When you are done, click Close

Inserting Address Blocks and Greetings—Use Carefully

Microsoft recently added a mail merge feature that allows you to insert address blocks and greeting lines. By clicking the respective button on the toolbar, Word will allow you to 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.

Further, when you click on either button, Word displays a dialog box which gives you some options on which fields you would like inserted, how you would like them to be arranged, what punctuation to include and others. While this sounds straightforward enough—and it is if you are using a data source created in Word—it can get confusing if you are using an Excel worksheet.

Remember when the recommendation about adding a header row in your worksheet on page 1 of this article? Well, if you named a field something other than what Word uses as a field name for similar data, Word might match the fields incorrectly.

What this means is if you use the insert address block or insert greeting line buttons, the data might appear in a different order than you specify—simply because the labels don't match. Fortunately, 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 Match Fields dialog box, you will see a list of Word's field names on the left. On the right side of the box, you will see a column of dropdown boxes. The name in the each dropdown box is the field that Word is using for each respective field in the Address block or Greeting line block. To make any changes, simply select the field name from the dropdown box.
  3. Once 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.

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

Applying Special Formatting from Source Spreadsheet

If you want to apply special formatting such as italics, bold or underline, you must do so in Word. If you are viewing the document with fields, you must 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, simply highlight the text you wish to change.

Remember that any change will carry throughout all the merged documents, not just the individual one.

Previewing the Merged Documents

To preview your 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.

You can 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 RecordLast Record.

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

When you are ready to merge your 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 will be sent to the printer without any modification. You can merge to the printer simply 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; if you merge to a new document, the mail merge main document and data source will remain intact, but you will have a second file containing the merged documents.

To do this, simply click the Merge to New Document toolbar button.

Whichever method you choose, you will be 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 desired choice and then click OK.

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

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