Creating Mailing Labels in MS Word

Using data from an Excel database

Introduction

All examples in this document use the source.xls database as the source of the data. The mailing label document is merge.doc. You should use the appropriate database source for your needs. The name of the important filtering field in source.xls is STATUS, your field name may be different. We will also use a temporary database named temp.xls to hold the filtered data needed by MS Word.

The STATUS Field

Often a person may fall into more than one category. When a person fits into more than one category, enter each code into STATUS. The codes may be in any order but with no spaces or commas separating them. For example a person who is a private donor and a volunteer has a STATUS of pv or vp. Using the STATUS field this way allows for the most flexibility, extensibility, and power.

Eleven Basic Steps

1. Determine which records you want printed.
2. Figure out your filter criteria based on step 1 and then write it down.
3. Open the databases in MS Excel.
4. Enter your filter criteria into source.xls
5. Perform an advanced filter in source.xls.
6. Copy the results of the filter and paste the results into temp.xls.
7. Exit MS Excel.
8. Open merge.doc in MS Word.
9. Start a mail merge.
10. Set the sort criteria.
11. Perform the merge

Determine Which Records to Print

Figure Out Your Filter Criteria

Table 2: Comparison operator definitions. Examples use the codes shown in Table 1.

Comparison	Meaning	Example	Result
= Equal to	Exactly the same	= v	v and only v
<>  Not equal to	Exactly different	<> v	everything but v, including blanks
<  Less than	For numbers, anything less than. For letters, in dictionary order A is less than B.	< p	c and e
>  Greater than	For numbers, anything greater than. For letters, in dictionary order B is greater than A.	>e	p and v
<=  Less than or equal	Combines the less than and the equal to comparisons.	<= p	c, e, and p
>=  Greater than or equal	Combines the greater than and the equal to comparisons.	>= e	e, p, and v

Wildcards are a very important and powerful tool in filters. You may think of a wildcard as a placeholder for one or many numbers or letters. The two important wildcards are the asterisk (*), and the question mark (?). Review the following examples for greater understanding.

The * wildcard is a placeholder for any number of digits or letters.

• *t = t, it, hurt, or anything else that ends in t
• t* = t, to, trick, or anything that begins with t
• *t* = t, it, to, riotous, or any word that contains t
• *n*t* = not, into, insert, and many others.

• ?t = it, at, and et
• t??? = tree, twin, toes, and other 4 letter words that start with t
• ?n?t = knit, gnat, etc.

• ?t* = it, attune, stone, etc
• ?n*t? = unite, inserts, etc

Figure 1: Starting STATUS, some are just A, some are just B, and some are both A and B

The results, what will print, are the filled in portions in Figures 2 and 3. Figure 2 shows the result of A OR B. Any person who likes apples (A), bananas (B), or apples and bananas (AB) will be selected using OR. Figure 3 shows the result of A AND B. Only persons who like both apples and bananas (AB) will print. If there are no persons with both A and B, no records will print.

Figure 2: A OR B

Figure 3: A AND B

Now, let's pull this together and do some example filters. We'll start easy and proceed to more difficult ones.

• Find anyone who is a private donor.
• The filter is STATUS = *p*.
• Find everyone who has no STATUS.
• The filter is STATUS <> *.
• Find everyone who is a volunteer or an employee.
• The filter is (STATUS = *v*) OR (STATUS = *e*).
• Find only people who are volunteers and private donors.
• The filter is (STATUS = *v*) AND (STATUS = *p*).
• Find private donors who live in Milwaukee.
• The filter is (STATUS = *p*) AND (CITY = Milwaukee).
• Find private donors who live in Milwaukee, all employees, and volunteers who live in Wisconsin.
• The filter is ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin)).
• Find persons with no STATUS living in Wisconsin, and volunteers who are also private donors living in zip codes from 50000 to 60000.
• The filter is ((STATUS<>*) AND (STATE = Wisconsin)) OR ((STATUS = *v*) AND (STATUS = *p*) AND (ZIP >= 50000) AND (ZIP <=60000)).

Now, write down what records you want to print and convert it to a filter using the comparison operators, wildcards, and joins. Now that you have the filter you need to put it into MS Excel.

Open the Databases in MS Excel

Figure 4: MS Excel File >> Open

Figure 5: Open dialog box.

You may have both files open at the same time. Switching between the open files is easy. Each open file is called a window, so all you need to do is switch between the windows. To switch windows, from the menu select Window >> temp.xls or Window >> source.xls. The last items in that menu indicate open windows you may switch to. See Figure 6.

Figure 6: How to switch between open windows.




Enter Your Filter Criteria

The filter you have chosen is applied to the source.xls database in MS Excel. My source.xls contains the sample database shown in Table 3. This is the data all the example filters are performed on. The field names are the titles of the columns; such as LAST, CITY, STATE, and STATUS. The first 10 rows of source.xls should be empty. If necessary insert some blank rows.

Table 3: Sample data in source.xls.

LAST	FIRST	CITY	STATE	ZIP	STATUS
Smith	Bob	Milwaukee	WI	53208	pv
Jones	Ruth	Milwaukee	WI	53205	v
Doe	John	New York	NY	00111	c
Roberts	David	Chicago	IL	43331	e
Worth	Mary	Racine	WI	53123
Johnston	Joseph	Mequon	WI	53299	ev
Gomez	Jesus	Los Angeles	CA	99195	ep
Fish	Herb	Milwaukee	WI	53202	cv
White	Amanda	Shorewood	WI	53212
Clinton	Bill	Madison	WI	53646	e
Thompson	Thomas	Milwaukee	WI	53213	p
Greenspan	Alan	Boston	MA	05264	cpv
Leno	Jay	Miami	FL	26254	pvce
Tsao	Ken	Milwaukee	WI	53288	e
Kim	Mai	Milwaukee	WI	53214
Garcia	Juan	Racine	WI	53132	v
Wojak	Peter	Shorewood	WI	53211
Evans	Dick	Milwaukee	WI	53222	cv
Gato	El	Chicago	IL	43354	vp
Ito	Isato	Minneapolis	MN	68654	pe
Hernandez	Keith	Milwaukee	WI	53275	p
Lopez	Maria	Franklin	WI	53288	e
Chang	Chai	Milwaukee	WI	53206	p

Figure 7: Required filter fields in row 1 for the filter ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin))

The filter should contain at least one of the field names. Copy the field names used in your filter and paste them into the first row of source.xls. The copied names should be exactly the same as the field names used in your database. For example if your filter is STATUS = *p*, then the required field name is STATUS. If your filter is ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin)), then the required field names are STATUS, CITY, and STATE (see Figure 7). If you include unused field names in your filter criteria then the applied filter will not give the expected results.

Now, enter your filter into the field columns. If the comparison is =, don't include the =. For joins, ANDs go on the same row while ORs go on separate rows. See the following tables for examples of various filters.

Table 4: Find anyone who is a private donor. The filter is STATUS = *p*.	Table 5: Find everyone who has no STATUS. The filter is STATUS <> *.	Table 6: Find everyone who is a volunteer or an employee. The filter is (STATUS = *v*) OR (STATUS = *e*).
STATUS	STATUS	STATUS
*P*	<> *	*v*
		*e*

Table 7: Find only people who are volunteers and private donors. The filter is (STATUS = *v*) AND (STATUS = *p*).		Table 8: Find private donors who live in Milwaukee. The filter is (STATUS = *p*) AND (CITY = Milwaukee).
STATUS	STATUS	STATUS	CITY
*v*	*p*	*p*	Milwaukee

Table 9: Find private donors who live in Milwaukee, all employees or volunteers who live in Wisconsin. The filter is ((STATUS = *p*) AND (CITY = Milwaukee)) OR (STATUS = *e*) OR ((STATUS = *v*) AND (STATE = Wisconsin))
STATUS	CITY	STATE
*p*	Milwaukee
*e*
*v*		Wisconsin

Table 10 : Find persons with no STATUS living in Wisconsin, and volunteers who are also private donors living in zip codes from 50000 to 60000. The filter is ((STATUS<>*) AND (STATE = Wisconsin)) OR ((STATUS = *v*) AND (STATUS = *p*) AND (ZIP >= 50000) AND (ZIP <=60000)).
STATUS	CITY	STATUS	ZIP	ZIP
<>*	Milwaukee
*v*		*p*	>=50000	<=60000




Perform an Advanced Filter

The Advanced Filter feature of MS Excel is something you may find useful when entering or modifying data as well as when printing labels. Performing a filter does not change any data in source.xls. The only thing it does is hide all rows that don't match the filter criteria. If a filter returns unexpected results, it may be easily removed.

Before performing the filter, make sure you have already entered your filter criteria in the first few rows of the spreadsheet. Next, select the first field name in your database as shown in Figure 8.

For illustration, the following figures apply the (STATUS = *v*) AND (STATUS = *p*) filter to the sample data from Table 3.

Figure 8: Select the first field name.

Apply the filter by choosing Data >> Filter >> Advanced Filter as shown in Figure 9. You may easily remove the filter by choosing Data >> Filter >> Show All.

Figure 9: Apply filter by choosing Data >> Filter >> Advanced Filter.

The Advanced Filter dialog box should appear, see Figure 10. The Action should be "Filter the list, in-place". The List range is your database and it should already be properly selected. The Criteria range is the only thing you should have to provide. Click on the little white, black, and red box to the right of criteria range, this is shown in Figure 10 by the arrow. If you accidentally click on the List range, return to the dialog box by hitting Enter.

Figure 10: The Advanced Filter dialog box. Click on the Criteria range, where the arrow is shown.

After you click the box, your screen should look similar to Figure 11. Use your mouse to select the filter you entered in the first few rows of the spreadsheet. When the filter is selected, hit Enter. Then the Advanced Filter dialog box, shown in Figure 10, should appear again. Click OK.

Figure 11: Selecting the criteria range for an advanced filter.

You have just performed your filter, congratulations. The number of records shown on the screen should have changed and the row numbers of the filtered records should be in blue.

As an example see Figure 12 for the results of the filter (STATUS = *v*) AND (STATUS = *p*). The next step is getting the data ready for MS Word.

Figure 12: The results of the filter STATUS = *v* AND STATUS = *p*




Copy and Paste the Results

Its time to move the filtered records from source.xls to temp.xls. See Appendix A for shortcut keys used to navigate and select in MS Excel.

Select the filter results by using the shortcut CTRL+SHIFT+*. This shortcut may be used by clicking in the first field name in your database. You Selected the first field earlier as shown in Figure 8. Next, hold down the CTRL key and the SHIFT key and then tap the * key (above the number 8). When your filter results are selected they are highlighted in black. Now, copy your filter results to the clipboard using Edit >> Copy. See Figure 13.

Figure 13: Copying the filter results using Edit >> Copy.

The contents of the clipboard now need to be pasted into temp.xls. Switch to temp.xls. Switching between windows was discussed earlier. Select the entire spreadsheet by clicking on the intersection of the row numbers and the column letters, this location is indicated by the arrow in Figure 13. Delete the contents of temp.xls by hitting the delete key. Next, click in the first cell, A1, of the spreadsheet. Paste the filter results from the clipboard into temp.xls by choosing Edit >> Paste. The results of your filter are now safely in temp.xls.




Exit MS Excel

The filter results in temp.xls need to be saved. Save the file temp.xls by choosing File >> Save. The file source.xls should not be saved because you did not make any changes to the database.

Exit Excel by choosing File >> Exit or clicking on the X box in the upper right of the window, see Figure 14. Remember, it's okay to save temp.xls but not source.xls.

Figure 14: Exit from the program.




Start MS Word

Start your merge document in MS Word. Open the file merge.doc the way you normally would. You may also start Word and then use File >> Open to get a Open dialog box. Opening files in Word is done the same as in Excel. Once the document is loaded in Word you are ready to start a mail merge. The same merge.doc may be used for all of your databases.




Start the Mail Merge

From the MS Word menu select Tools >> Mail Merge as shown in Figure 15.

Figure 15: Selecting Tools >> Mail Merge.

Choosing Tools >> Mail Merge opens the Mail Merge Helper dialog box shown in Figure 16. Most of the information required to perform the merge is saved with your document, so we may ignore most of the buttons. Your Mail Merge Helper dialog box should look similar to Figure 16.

Figure 16: Mail Merge Helper dialog box.

In section 1, Main document, of the dialog box the Merge type should be 'Mailing Labels' and the Main document should be '??????\merge.doc'. In section 2, Data source, the Data should be 'temp.xls!Entire Spreadsheet'. For section 3, Merge the data with the document, the Options in effect should be 'Suppress Blank Lines in Addresses' and 'Merge to new document'.

If the Data source in section 2 is incorrect see Setting the Data Source for information on setting it correctly.




Set the Sort Criteria

The sort criteria is probably already set the way you want. You may check or change the sort by choosing Query Options from section 3 of the Mail Merge Helper dialog box.

You should then see the Query Options dialog box, see Figure 17. Two pages may be selected from this dialog box. You switch pages by clicking on the Filter Records tab or the Sort Records tab. Click on the Sort Records tab.

Figure 17: The Query Options Dialog box on the Sort Records tab.

The most common sort is the one shown in Figure 17, but you may sort the records anyway you choose. The sort is currently set to 'Order by ZIP from lowest to highest, and then by LAST from a to z'. When you are done with the sort criteria click OK. You are now ready to merge.




Perform the Merge

Now you should see the Mail Merge Helper dialog box again. Select Merge from section 3. The Merge dialog box appears as shown in Figure 18. Leave all settings the same and click on Merge.

Figure 18: The Merge dialog box.

The merge is now running, please wait a little, since the merge may take some time. When the merge is complete you should see the new mailing label document. Review this document to make sure it contains what you want. When you are ready, print the document.

Now you can exit the MS Word program by choosing File >> Exit. Word will ask if you want to save changes to Labels, say No. Next, Word asks if you want to save changes to merge.doc, say Yes. Excel now asks if you wish to save changes to temp.xls, say No.




Conclusion

You have completed the eleven steps to better merging with complete success. Thank you for patiently wading through the complexity and arriving at the desired results.

Both Excel and Word have extensive help files that you may access by choosing Help >> Contents and Index. Often the index is the place to start since it helps one learn the terminology of one's question.

It is possible to perform this merge in other ways. The approach followed by this document was chosen for both ease of use and stability. One of the other approaches uses MS Merge to get the data from Excel. Unfortunately, I have found that MS Merge frequently crashes or gives incorrect results. Maybe in its next version MS Merge will be the better solution.