Printing Mailing Labels from Your Excel Database
 
  Back to the List of Topics
   
  Home
It's not practical to print mailing labels from within Excel, however, it is very simple to use the data in Excel and print labels in other programs. In this exercise, we will print them using Microsoft Word. 

The general steps to print the labels are: 

  • Make sure the data in Excel is in Excel's database format 
  • Name the Excel database, Range names are easier to remember than cell ranges 

  • such as C5:K120
  • Save the Excel file
  • Open Word, then open the Mail Merge Helper 
  • Tell Word where the external data is located 
  • Create the mailing label 
  • Perform the Merge
The steps below will result in a screen that appears as follows, afterwhich the labels will be created:
 


This is an Excel exercise showing you how to print mailing labels with your Excel data.  As a result, only the basics of performing the merge in Word are presented.  As you proceed with this exercise,  you will note that you have have several merge options other than what is presented here and may have related questions.  The Word exercises related to Merges will have the necessary detail to answer those questions. 
 

This exercise refers to a specific Excel file, Sort.xls.  If this file is not available to you as you read this, simply refer to any file you have that contains an Excel database, and modify the steps accordingly. 

    1.  Open the file Sort.xls
      This file contains the data for this exercise.
    2.  Highlight the entire database, including the headings, and name it Database
      This is not a required step, however, as we will see later, it makes the merge much easier to perform.  Warning:  if data is added to or deleted from the Excel database, the database must be named again to reflect it's new size. 

      If the range is not yet named,  with the database highlighted, name it by clicking on Insert  Name  Define (in the menu), then type the name: Database and press Enter.  Typing the defined name is always required (do not click on an existing name). The defined name actually used can be any name of your choice.
       

    3. Save the file 
      Word works with the Excel file on the disk, not the file active in memory, therefore, any changes made to the Excel file, must be saved before proceeding to the next step.


    4. Open or switch to Microsoft Word

      Make sure a blank document is open.
    5.  Click on Tools   Mail Merge   (in the Word menu) 
      You will get a window that looks like: 


       

    6. Click on Create  and then click on   Mailing Labels

    7. Click on Active Window

       This finishes the CREATE step.
    8. Click on Get Data  and then click on  Open Data Source
      The source of the data is not in Word, it is in Excel.  This command 
      will open the file on the disk. 
    9. The Open Data Source window appears. 
      Which is similar to the regular open file window. 
    10. Change folder (directory) to the folder containing the data 
    11. Click on the file: Sort.xls   and click on  Open
      You will see the dialog box asking for the Named Range:
      As you can see, without the database range being named, it would very difficult to tell Word where the data is in the Excel file.
    12. Click on OK

    13. Click on Set Up Main Document 

      The  Label Options   window appears: 


 
    14. Scroll through the Product Number   list, find and click on  5160   and
       click on OK

      The Create Labels window appears (at last, we're at the meat of this exercise). We will create a sample label in the white area.  The important thing to remember is that this is what the label will look like, if you want spaces, enter spaces; if you want a comma, type in a comma; if you want something on the next line, press Enter.

     
    15. Click on 
      You should now see a list of the field names in the Excel file.


       
       

    16. Click on First
      The  Label Area  now shows:

       Refer to the first page to what this screen will look like when completed.
       
       

    17. Press the space bar  once 
       To create a space after the first name. 
    18. Click on 
     

    19. Click on Last

      The  Label Area  now shows:

     

    20. Press Enter

      To put the cursor on the next line. 
    21. Continue the exercise until it looks like the sample on the first page 

    22. When done creating the label, click on  OK

      At this point the Mail Merge Helper is still visiible and you can see the formatted labels in the background (the merge hasn't been done yet).
    23. Click on Merge
      You should see the following dialog box:


    The merge will be to a New Document, therefore:
     

    24. Click on

       After a fews seconds, you will see the labels on the screen.
       
This exercise should have provided you with the basic steps to print mailing labels from within Microsoft Word using your Excel data.  From here, you can work with the features within Word to accomplish more specific tasks.  Also, these same basic steps can be used to print your labels or do other merges from data located in Microsoft Access.

This exercise was written using Excel and Word  in Office 97.  The Excel steps are the same in all versions of Excel, the Word steps should be the same, at least in concept, in prior and future versions, however, this was not tested. 

 

 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com