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:
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.
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.
Let AutoComplete Finish Typing Your Words
Excel's AutoComplete feature will help you to insert repetitive list entries by recognizing the words you type and finishing them for you. To enable this time-saving feature, choose Options from the Tools menu, click the Edit tab, and select the Enable AutoComplete For Cell Values check box. It's important that repeated names and other data (such as January, Midwest Region, and so on) be entered identically from record to record, to enable Excel to recognize the data for grouping, sorting, and calculating.
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.
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:
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.