Creating Your Database

An Excel database is a fairly basic worksheet. You type field names or column labels (such as First Name, Last Name , Address, and Home Phone) in the top row, and then you enter records in the rows below it. When creating a database, keep the following rules in mind:

• You must enter column labels in the top row of the data area. (This may or may not be the top row of the worksheet, but all records must be entered in the rows directly below the row that contains the column labels.) Column labels describe and identify the contents of each field.

• Do NOT skip a row between the column labels row and the first record. If you skip a row, Excel loses track of the field entries in each column.

• If the worksheet that contains your database contains other unrelated data, leave a blank column or row between that area of the worksheet and the area that contains your database records.

• Type each record in a separate row, with no empty rows between records.

• The cells in a given column must contain information of the same type. For example, if you have a ZIP CODE column, all cells in that column must contain a ZIP code.

• Keep all records on one worksheet. A database that spans several worksheets is difficult to manage, and Excel may not be able to perform all available database functions, such as sorting records, on data that's entered on separate worksheets.

Tip

Include a column on the far left that numbers the records. If the records become mixed up when you sort them (as explained later in this chapter, in the section "Sorting Your Records"), you can use the numbered column to restore the records to their original order.

Entering Column Labels

The first step in creating a database is to type column labels (field names) in the top row of the worksheet. Excel uses these column labels to locate field entries, sort and filter records, and retrieve data. To format and enter column labels properly and avoid problems later, follow these steps:

1. Click the row heading (the "1") to the left of the top row to select the entire row.

2. Open the Format menu and choose Cells . The Format Cells dialog box appears.

3. Click the Number tab if it is not already up front and then, in the Category list, click Text , as shown in Figure 11.2. Selecting this category ensures that Excel will treat all column labels as text entries rather than values.

   Figure 11.2. Change the cell format for the topmost row to Text.

   

4. Apply any text and cell formatting as desired to designate the entries in this row as column labels. (I recommend making the entries bold and/or italic and adding a cell border to the bottom of the cells.) This formatting helps Excel distinguish between the column labels and the records, and it gives you a visual indicator showing where column labels end and records begin.

5. Click one of the cells in the top row, and type column labels in the cells of the top row from left to right. Figure 11.3 shows a sample row of column label entries.

   Figure 11.3. Type column labels in the topmost row.

   

Caution

When typing column labels or any entry, do not type leading or trailing spaces (spaces before or after the entry). Spaces can cause problems later when you sort or filter your records.

Formatting Database Fields

In the preceding section, you formatted the column labels as Text. You can save some valuable data entry time by formatting the cells in each columnthe cells in which you will type the data entries. For example, if you have cells that will contain phone numbers, you can apply a number format, such as (###) ###-####, which will format the phone number for you. When you begin to enter phone numbers, you just need to type the 10 digits that make up the phone number, such as 1234567890, and Excel displays the number as [phone].

To format the cells that will contain field entries, follow these steps:

1. Select three cells directly below the desired column label. If, for instance, you want to apply a phone number format to the cells in the Phone column, select the three cells below "Phone." DO NOT select the column label.

2. Open the Format menu and choose Cells . The Format Cells dialog box appears.

3. In the Category list, click the desired format category. If none of the categories contain a format you want to use, click Custom at the bottom of the list, as shown in Figure 11.4.

   Figure 11.4. Format the cells that will contain your data entries.

   

4. Click the desired format on the right side of the dialog box, or, if you chose Custom, highlight the entry in the Type text box and type a representation of the desired format. For example, to create your own phone number format, you might type (###) ###-#### or ###.###.#### .

5. Press Enter or click OK .

Note

Excel automatically extends formatting from one cell to the cell below it, as long as three of the previous five cells use the same formatting. So if you apply a format to the three cells directly below a column label, those three cells and any cells below them will use the same formatting. (To turn off this feature, open the Tools menu, choose Options , click the Edit tab, and clear the check box next to Extend List Formats and Formulas .)

Using Data Forms to Enter, Edit, and Delete Data

After you have typed the column labels and formatted at least three cells in any columns that require special formatting, you can begin entering records into your database by using either of the following two methods :

• Type individual entries in cells, just as you add any entries to a worksheet.

• Display a fill-in-the-blanks form, and type your entries into the text boxes to complete the form, as explained next.

Excel can identify the column labels you entered and formatted earlier in this chapter and use them to create the form you need to begin entering records. To display and use the form, follow these steps:

1. Click any cell that contains a column label.

2. Open the Data menu and choose Form . The Microsoft Excel dialog box appears, as shown in Figure 11.5, indicating that Excel cannot determine which row contains the column labels. Excel assumes you want to use the topmost row.

   Figure 11.5. Excel cannot determine initially which row contains the column labels.

   

3. Click OK . The data entry form appears, as shown in Figure 11.6. The form's name matches the name of the worksheet tab. Note that the name of the text boxes (fields) match the column labels you entered.

   Figure 11.6. Enter records with a form.

   

4. Type entries into the text boxes to complete the form. Press the Tab key after each entry to move to the next text box.

5. After you have completed the form, press Enter or click the New button to save the record and display a blank form for entering a new record. The information you entered on the form is transferred to the corresponding cells in the worksheet, as shown in Figure 11.7.

   Figure 11.7. When you click New or press Enter, Excel transfers the form data to the worksheet as a record.

   

6. Repeat steps 4 and 5 until you have entered every record you need to enter.

7. Click the Close button.

Note

If your database already contains records and you want to insert additional records through the use of a form, click any cell in your database that contains a field entry and then open the Data menu and choose Form . Excel immediately displays the form.

If you choose to type field entries in cells, you might notice that as you type entries, Excel automatically completes some entries for you. If, for instance, you select a cell in the First Name column, and that column already contains the entry "Allison," when you type Al , Excel automatically inserts "Allison." To accept the entry, simply press the Tab key to move to the next cell. To type a different entry, such as Albert, just keep typing to complete the entry and then press the Tab key. (To turn AutoComplete on or off, open the Tools menu, click Options , click the Edit tab, and click the check box next to Enable AutoComplete for Cell Values .)

Note

You can use forms to edit records as well as enter them. Click any cell in the database, and then open the Data menu and choose Form . Click the down-arrow button at the bottom of the scrollbar until the record you want to edit is displayed. Enter your changes and then click the New button.