ODE Interactive Web Center
Procedure for using an Excel file,
downloaded from the ODE Web site, for (mailing)
labels in Word
These steps provide the basic ingredients in creating a Word
Mail Merge document for labels. There are a number of additional
features that can be used and explored at your convenience.
Open a blank document in Word and follow these steps:
Step 1. Click on the
menu option Tools then Mail Merge. You will see the
list of the three steps needed to create labels or other merge
Step 2. Click on Create.
The drop-down list will ask you for the type of document you
want: Mailing Labels, etc. Click on the appropriate type. The
following directions assume that you are choosing Mailing Labels
and you will have to modify some steps if your choose otherwise.
Step 3. You will then
see a message box regarding the document window. If you have just
opened Word and have a blank document, click on Active Window,
otherwise click on New Document Window.
Step 4. Go to the second
step on the Mail Merge Helper screen, Data Source,
and click on Get Data, then Get Data Source.
the Open Data Source dialog box appears, the bottom left
box may be displaying Files of Type Word Documents. Click
on its down arrow, scroll to MS Excel Worksheets or All Files.
Your file name should be displayed; if not, be sure that the path
you have chosen points to the correct directory where you saved
the file. Once it is in view, double click on the file name to
incorporate it into the Word document that you are creating.
Step 6. A dialog box
will appear, highlighting Entire Spreadsheet under Named
or Cell Range. Click the OK button.
Step 7. You will then
see a message telling you that Word needs to set up the Main
Document. Click the Set Up Main Document button.
Step 8. If you chose Mailing Labels in Step 2, you will see
a variety of label types in the drop-down list box. Click on the
correct label type. You may change the Label Products default and
any other information that you need to match the set up with the
type of labels that you want to use. If you chose something other
than Mailing Labels in Step 2, you will be given an appropriate
dialog box that will enable Word to determine what layout to use
for the data.
Step 9. After you click OK in Step 8, you will see a
Create Labels dialog box. Click on the Insert Merge
Fields button. You will see a list of all the column headings
that were on the first line of your Excel worksheet. If you did
not import the header record with the Excel data, you should
modify your spreadsheet so that each column has a heading to use
on the field name that you want on the first line of the label
(Superintendent, Treasurer, Name, whatever). When you are creating
this label, you must enter it as if you were manually typing in
the data, i.e. press RETURN or ENTER to begin a new line where
needed. Type a comma and a space after you chose the field
<<CITY>> and a space before you choose
<<STATE>>. Type a space on the same line before you
choose <<ZIP>>. When you are finished, click OK.
If you want to use a generic title, such as Curriculum
Coordinator or Director of Transportation, type it in the place
where you would ordinarily insert the superintendent, treasurer,
or principal name. Once you do so, it will appear on every label
that is generated. Then add the field names for the school or
district, address, city, state, and zip.
NOTE: If you make a mistake or prematurely exit from this
screen, you have the opportunity to change this format. Next to
the Main Document button on the Mail Merge Helper screen
there is now an Edit button. Click on it and on the name of
the file. You will then see the generic fields on a page and can
place spaces, punctuation, line feeds or other characters any
where on the label. You can click on the Insert Merge Field
and add additional fields. You can delete a field the same way you
delete any other Word text. Return to the Mail Merge Helper screen
by clicking on Tools, Mail Merge again.
Step 11. On the Mail Merge Helper screen, click the Merge
button in Step 3. A Merge dialog box will appear showing the
default values of Merge to New Document, Records to be Merge:
All, When Merging. You may change any of these that you want.
It is always safer to merge to a new document so that you can see
what the labels will look like before you send hundreds of them to
the printer. You can then print the resulting document once the
labels appear acceptable and the blank labels have been placed in
the printer. You can save the file in the formatted text and print
it at a later time. You can save the master document and use it
repeatedly by changing the name of the file chosen in Step 5.
Step 12. If you want to choose only some of the records,
such as a few counties or particular zip codes, you can filter the
labels that are created. Before you click Merge in Step 11,
click on the button on the bottom right, Query Options.
On the Query Options dialog box, there are two tabs, Filter
and Sort. Click on Filter. Using the top drop-down
list box on the left, you need to pick the field for which you
want to select some, but not all, records; it may be County or Zip
or any other field that is in your EXCEL worksheet. The middle
section requires that you select the relationship of the field to
the specific value, such as equals, greater than, not equal, not
blank, etc. The right section requires that you type in the value
for comparison. For example, you can choose COUNTY on the left,
EQUAL TO in the middle, and LAKE on the right. You can type in
only one value in the rightmost box. If you want additional
values, go to the far right of the next line and choose OR. If you
want a second county, COUNTY, then EQUAL TO, then CUYAHOGA.
Continue until you have a statement for every county that you want
to include. The OR infers that a label should be printed if the
county on the spreadsheet matches any one of the values you typed.
An AND connector infers that a label should be printed if the
values on the row of the spreadsheet match all of the values you
typed. For example. if you want to do a mailing to only 43232 zip
codes in Franklin county, you would choose county equal to
Franklin AND zip code equal to 43232. Then you would get a label
if and only if the school/district was in Franklin county and its
zip code is 43232. If any entities had the same zip code but are
in another county, they would not be included. If any entities had
the same county but not zip 43232, they would not be included.
When you are finished click OK on the QUERY OPTIONS page and
again on the Mail Merge page.
ODE will not provide technical
support - this guide is simply a courtesy for Web visitors who
wish to import delimited data into a spreadsheet and Microsoft
Word. ODE does not
endorse the Microsoft product over others, but it happens to be
included in the standard build for computers in the Ohio
Department of Education. This guide assumes the reader has a
basic level of computing experience and is using Microsoft Excel,
Word 97 or higher.
Wednesday, August 23, 2006 09:58 AM