CALIFORNIA STATE UNIVERSITY, NORTHRIDGE
 
Mail Merge Basics:
1

Creating and Sorting a Data Source Document in Excel
Note: This document provides details necessary for creating a file that can be merged with a main document in Word. It is assumed that readers already know how to use the Excel spreadsheet program.

Creating a Data Source Document

To create a data source document in Excel, type field names in Row 1 (header row) of a worksheet, then, starting with Row 2, enter the data for each record — one record per row (as shown in Figure 1, below).
 
FIGURE 1. Sample Excel Data FileSample Excel Data File
 

Notes and Suggestions:

  • You might want to bold the header row (or change the font color) to make the field names stand out.
  • Use the "Freeze Panes" feature to make sure you can always see your field names and the major identifying field(s) for each record (such as first and last name). Click in a cell immediately below the row to be frozen and immediately to the right of the last column to be frozen (cell C2 in Figure 1, above), then go to the Window menu and select Freeze Panes. A solid horizontal line and a solid vertical line indicate which rows and columns will always be visible (Row 1 and Columns A and B in Figure 1). This means that as you scroll to the right to fill in data or scroll down as you add records, you will always be able to tell what record and field you're working on (see Figure 2, below).
 
FIGURE 2. Sample Excel Window Illustrating Frozen PanesSample Excel Illustrating Frozen Panes
 
  • If you want leading zeros to show in your data (as in zip code), do the following:
    1. Click on the column letter for the column that will contain the numerical data to highlight that column.
    2. From the Format menu choose Cells.
    3. Make sure the Number tab is on top, then choose Custom from the "Category:" list.
    4. Choose 0 (zero) from the "Type:" list, then add zeros until the desired number of digits is reached (such five zeros — 00000 — for a zip code field).
    5. When finished, click OK.

Sorting the Excel Data

To sort your Excel data file, click in any cell in the worksheet, then, from the Data menu, choose Sort. In the dialog box that appears, make the following selections (see Figure 3, below), then click OK.
  
My list has...
  • Make sure Header row is selected (so the field names don't get sorted with the data).
Sort by...
  • Choose from the drop-down list of field names (if you forget to click "Header row" you'll see column labels instead — such as Column A).
  • Choose Ascending or Descending
Then by...
  • Use either or both of these additional sorting selections to specify up to three levels of sorting (you might want to "Sort by" Last Name and "Then by" First Name, for example).
 
FIGURE 3. Sort Dialog BoxExcel 'Sort' Dialog Box
 

Creating a Data Source Document in Word

If you prefer to create your data source document in Word, here's how (briefly):
  1. In a new, blank document click Tools - Mail Merge to get to the Mail Merge Helper dialog box.
  2. Click the Create button, choose the desired format type from the drop-down menu (such as Form Letters or Mailing Labels), and specify (click) Active Window when prompted. 
  3. Click the Get Data button and choose Create Data Source from the drop-down menu.
  4. Add, Delete or Change existing field names, then click OK and Edit Data Source
  5. Enter data in Data Form dialog box (tap TAB or ENTER to move from field to field)
Refer to the "How to Learn More" section for where and how to locate additional information.
Top  | Contents | Data | Form Letters | Mailing Labels | Envelopes | Lists/Catalogs | Select Records | Learn More
Prepared by Gail Said Johnson, User Support Services
March 20, 2001

 

ITR's technology training guides are the property of California State University, Northridge. They are intended for non-profit educational use only. Please do not use this material without citing the source.