Using a List of Cells as a Database | Running Microsoft Office 2000

A list is a collection of rows and columns of consistently formatted data, adhering to somewhat stricter rules than an ordinary worksheet. To build a list that works with all of Excel's list-management commands, you need to follow a few basic guidelines. Figure 22-1 shows a simple sales-history list that has five columns, or fields, and a dozen sales transaction records. When you create a list, it should contain a fixed number of columns or categories of information, but it should have a variable number of rows, reflecting records that can be added, deleted, or rearranged to keep your list up to date. Each column should contain the same type of information, and no blank rows or columns should appear in the list area. If your list is the only information in the worksheet, Excel will have an easier time recognizing the data as a list.

To create a list in Excel, follow these steps:

Open a new workbook or a new sheet in an existing workbook. Using a new worksheet that will contain only your list works best, so that Excel can select your data automatically when you use list-management commands.

Create a column heading for each field in the list, adjust the alignment of the headings, and format them in bold type.

Format the cells below the column headings for the data that you plan to use. This can include number formats (such as currency or date), alignment, or any other formats.

ON THE WEB
The Pivot.xls example is on the Running Office 2000 Reader's Corner page. For information about connecting to this Web site, read the Introduction.

Add new records (your data) below the column headings, taking care to be consistent in your use of words and titles so that you can organize related records into groups later. Enter as many rows as you need, making sure that there are no empty rows in your list, not even between the column headings and the first record. See Figure 22-1 for a sample list of information.

SEE ALSO
For information about sharing a list with other users over a network, see "Managing Shared Workbooks"

Figure 22-1. A list of information in an Excel worksheet, complete with column headings.

Using a Form for Data Entry

To make it easy to manage the data in your list, Excel lets you add, delete, and search for records by using the Form command on the Data menu. When you choose Form, a customized dialog box appears, showing the fields in your list and several list-management command buttons. (See Figure 22-2.) The name of the current worksheet also appears on the dialog box title bar. By default, the first record in the list appears, but you can scroll to other records by clicking the vertical scroll bar. Excel adds new records to the end of the list; to display a blank record, you can scroll to the bottom of the list or click the New button. Although you'll often add records by typing them directly into the worksheet, using the Form command is a useful alternative (for, say, a less-experienced colleague you've asked to help enter data), and in some cases you'll find that it works faster.

Figure 22-2. The Form command gives you another way to enter data into the rows and columns of a list.

Validating Data as You Enter It

If several people are using your Excel list, you might want to control the type of information they're allowed to enter into worksheet cells to minimize typing mistakes. For example, you might want to require that only January or February dates can be entered into the Month column, or that only dollar values in a particular range (say, $0-$5,000) can be entered into the Sale column. With Excel you can enforce input requirements such as these by using a formatting option called data validation. When you use data validation, you protect part or all of your worksheet from erroneous input that might cause formulas or list-management tools to produce incorrect results.

To enforce data validation of a particular range of worksheet cells, follow these steps:

Select the cells in the column that you want to protect with data validation. This should include cells already containing data as well as the blank cells below, where you'll be adding new records. (If you're not sure how long your list will be, you may want to select the entire column.)

Choose Validation from the Data menu. The Data Validation dialog box opens. Click the Settings tab.

In the Allow drop-down list box, specify the input format you want to require for the selected cells. Your options are Any Value (used to remove existing data validation), Whole Number, Decimal, List, Date, Time, Text Length, and Custom (a format you specify by writing your own formula).

When you select a value in the Allow drop-down list box, additional text boxes appear below that let you specify extra input conditions or restrictions, such as the smallest number and the largest number Excel will accept.

Click the Input Message tab, and select Show Input Message When Cell Is Selected to specify a message that will appear when the cell is selected.

In the Input Message text box, type the words you want displayed in the pop-up box that appears when a user selects a cell containing the data validation formatting. (This box is optional, but using it will help your users discover the requirements you have established before they make a mistake.)

Click the Error Alert tab, and select Show Error Alert After Invalid Data Is Entered to specify the type of error message you want Excel to display if a user enters inappropriate information into a cell.

In the Style drop-down list box, select one of the following options: Stop (to block the input), Warning (to caution the user but allow the input), or Information (to display a note but allow the input).

In the Error Message text box, type the words you want displayed in the error message dialog box that appears if the user enters invalid data. For example, a useful phrase might be This worksheet tracks January and February sales only.

Click OK to complete the Data Validation dialog box. If you specified the options shown in step 7, you'll see a gentle error message similar to the following if you enter the wrong type of data in a cell that has active data validation: