[ Home ] [ Shortcut Keys ] [ Setting the Data Source ] [ Create the Merge Document ]

Creating Mailing Labels in MS Word

Using data from an Excel database

Introduction

You will be using mail merge in MS Word to print mailing labels from a list of addresses maintained and filtered in MS Excel. These are more advanced functions of MS Word and MS Excel, therefore; sometimes this may seem complex. Many examples are included to clarify the process of merging and filtering.

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

Some thoughts about the best way to set up the STATUS field. Since the STATUS is often used to determine whether the record should be printed, STATUS should contain simple and basic information. You are using STATUS as a code to refer to different categories of people. Each code should be a single lowercase letter. Each code should also refer to only the most basic categories and not combinations of categories. As an example of the STATUS code table see Table 1. The codes in Table 1 will be used in many examples

Table 1: Example of a good STATUS code table.
STATUS Code
Corporate donor c
Employee e
Private donor p
Volunteer v

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

Since filtering and merging are complex tasks in MS Excel and MS Word, the tasks are divided into many smaller steps. There are eleven basic steps involved. Most of the steps are short and easy to perform. Unfortunately, step 2 requires learning more about filter criteria.
  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

There are many possible combinations of records to print. You may wish to print labels for anyone who is a private donor (p). Or you may want to print labels for private donors (p) who live in Milwaukee, all employees (e), and volunteers (v) living in Wisconsin. What you choose to print may be very simple or could be very complex. Using the eleven steps you should be able to handle most of these cases.


Figure Out Your Filter Criteria

A filter takes the original data, applies comparisons and joins, and returns only the data wanted. This step is difficult at first, but once you understand some of the concepts and get some practice it gets easier. There are three important concepts required before mastering filters. The concepts are comparison operators, wildcards, and joins. Table 2 shows the comparison operators and their definitions.

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.

The ? wildcard may represent any single letter or number. Now let's combine both wildcards and see what happens. The last concept is joins. Joins are used to combine multiple comparisons. There are two main joins, AND and OR. These joins require further explanation and the logic is often counterintuitive. When one says, "I want to print both A and B", logically that is stated print A OR B. I will try to demonstrate this with circles. In Figure 1, one circle represents all people who like apples (A), and the other circle represents those who like bananas (B), where the circles intersect the people like both apples and bananas (AB).
 
Figure 1: Starting STATUS, some are just A, some are just B, and some are both A and B
AB Space

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
A OR B

Figure 3: A AND B
A AND B

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

Look these over to make sure you understand what the filters are doing. These examples will be used again as real filters for Excel.

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

Start MS Excel. From the menu select File >> Open, see Figure 4. The Open dialog box should appear. Open the source.xls database, see Figure 5. Next, open the temp.xls database in the same way.

[ Home ] [ Shortcut Keys ] [ Setting the Data Source ] [ Create the Merge Document ]

Created and maintained by Gary Dalton