Mail Merge Data

Using Data Directly from Excel

When you create a new merge document, you can use either existing data source or create a new one. If you're using an existing Excel worksheet as the data source, select Open Data Source... to display a dialog like the Open File dialog in Word or Excel. Select the workbook that contains the data you want to use, then click OK.

If you want to use an Excel worksheet as a data source but have not yet created one, you can do so before creating the merge document, or you can click on Create Data Source..., enter your data into a Word table, then either leave it in Word or transfer it to Excel later. Before creating a data source, have a quick look at A Word About Data Fields.

When you use the Mail Merge Helper to create a data source, Word automatically generates a data entry form that simplifies both entering and editing data. To open the data entry form select Edit Data.

If your data is in Excel, the Mail Merge Helper opens and displays the entire Excel spreadsheet. Click on Form... in the Data menu to simplify entering and editing data in Excel.

Use whichever method suits you best; both work equally well.

A Word About Data Fields

Before you create data fields, think carefully about all the ways that you might ever want to use the information, then create fields that will give you the greatest flexibility.

Smaller units are better; they offer more choices. For example, if you use a single name field and enter names like this: Doe, John M., and if that is the only name field you use, then every time you want to write a letter to Mr. Doe, your inside address and salutation will look like this:

Doe, John M.
123 Main Boulevard
Downtown, US 60782

Dear Doe, John M.,

However, if you put the parts of the name in separate fields, you have other options. Your letter can look like this, instead:

John M. Doe
123 Main Boulevard
Downtown, IA 60782

Dear John (or Dear Mr. Doe*),

To use the first name only as the salutation, you would need three fields called, for example, first_name, last_name, and init (initial). For even greater choice, add a salutation or honorific field* and use it to enter the appropriate form of address for each person - Mr. Doe, Ms. Smith, Your Highness, Sir or Madam, etc.

Storing city, state, and zip code information in different fields makes it possible to sort data by any of those items and to do a merge for only those customers living, for example, in CO or MA. If you save all of this information in a field called Address_3, you will never be able to do a merge letter to only your customers in Louisiana, since you don't have a 'state' field and the program most certainly will not be able to determine that Louisiana is represented by LA in the middle of a field called Address_3 - without some fancy programming! If you use three fields - City, State, and Zip_Code - you can specify a mailing to only customers in Nebraska or Chicago or in the 01346 zip code area.

If you are using an existing spreadsheet or table, you may need to add columns and separate some of the data, or add an extra field (e.g., Salutation or Honorific). Doing so will insure that the data can be used in any way that you may ever need to use it.

Creating a Data Source in Word

From the Mail Merge Helper, click on Create Data Source... to open the Create Data Source dialog box. This is where you'll select or create the field names to use in your data source (field names are labels for the kinds of data that will be entered in each column of your table: LastName, FirstName, SS#, etc.).

Use the entries in the Field names in header row: list as a start for your list, removing any that you don't think you'll need by clicking on the name then on the Remove Field Name button. To add a field name, type it into Field name: then press the button with the two right-pointing arrows to add it to the list.

Do not include spaces in the field names. You can use capital letters or the underline character to make the field names easier to read when putting multiple words together. If you enter an illegal name (e.g., a name containing illegal spaces or characters), the Add Field Name field will be grayed out.

To change the order in which the field names appear, select a field name from the list, then use the up and down Move arrows to change the position of the selected field.

To delete a field from the list, click once on the field, then click on the Remove Field Name button.

Use Address Book... You may be able to use data from a GroupWise, Act!, etc. address book in a merge. Click on the Use Address Book... button then select the address book you want to use. Create the merge document as you normally would, using the field names in the Insert Merge Field drop-down list.

Header Options... Lets you save headers in a separate file if you are using data from more than one source. If you'd like more information, use the Find command in Word Help and look for an article called About using a header source for a mail merge (in the Help index, type mail merge, then scroll through the list until you find header sources).

Moving Data From a Word Table to an Excel Spreadsheet

1. In Word, select the table you want to copy to Excel:
• With the cursor in the table, click on Select Table from the Table menu.
• With the mouse pointer in the table, right-click and choose the Copy command.
   
2. Open a new spreadsheet and click on the A1 cell (or another cell: the upper left corner of the copied table will be pasted into the cell you click on when you paste).
    
3. With the mouse pointer in the cell, right-click and select Paste from the pop-up menu. The data from your Word table will be inserted into the Excel spreadsheet.
    
4. Do any necessary reformatting, adjust column widths, etc.

Moving Data From an Excel Spreadsheet to a Word Table

1. Open the spreadsheet and highlight all of the data you want to insert into the Word table. Make sure that you include the row that contains the column headings (these are the field names that Word will use both to identify different kinds of information and to create the data entry and edit form). With the pointer in the selected block, right-click, then use the left button to select Copy from the context menu.
    
2. If Word is already running, click the Word button on the taskbar or the use [Alt+Tab] to move to it. If Word isn't open, open it and create a new document.

   If your spreadsheet has quite a few columns, use Page Setup to change the orientation to landscape and adjust the margins, if necessary. The table appearance isn't really important: once the data is in a Word table, you will normally be working only with the data entry form, not in the table itself.
    

3. Click on the first line of the new document, right-click, and select Paste from the context menu. All of the Excel data will be inserted into the new document in a Word table.

Try selecting the whole table (Edit menu, Select All) and changing the font size to 8 or 9 points to fit more information into fewer lines in the display. Use Zoom to enlarge the display and make it easier to read.

While the entire table is selected, click on the Left Align button on the toolbar or click on the Format menu, then on Paragraph. In the Paragraph dialog box, make sure that Alignment is set to Left or you might get strange results in your merged documents.

Cleaning up Your Data

The amount of 'cleaning up' to be done will depend on how the original data was entered. If the data was entered with a lot of extra spaces, they will need to be removed. If there were inconsistencies in the way the data was entered (for instance, codes or abbreviations typed differently in different records: Post Office Box entered variously as POB, P.O.B., P O Box, P.O. Box, and P. O. BOX), this is a good time to standardize your format. You can do it manually or use Replace... in the Edit menu to automate the process.

Click on the Show/Hide button (¶) on the main toolbar in Word so that you can easily see extra spaces in the table; they need to be removed or they will appear in your merged document, where they will cause inconsistent spacing and can create some fairly unattractive effects.

Use Zoom to enlarge the display to 150% or even 200% so that it's really easy to read, then carefully scroll through the entire table. Use the [Backspace] or [Delete] key to get rid of the extra spaces before, after, or in the middle of any cell entry. If you take out more than you meant to, use Undo (on the toolbar or in the Edit menu). Save your work frequently; this isn't something you want to have to do more than once per table!