Guidelines For Creating A List On A Worksheet

Microsoft Excel has a number of features that make it easy to manage and analyze data in a list. To take advantage of these features, enter data in a list according to the following guidelines.

List organization

Use only one list per worksheet
Avoid having more than one list on a worksheet. Some list management features, such as filtering, can be used on only one list at a time.

Put similar items in one column
Design the list so that all rows have similar items in the same column.

Keep the list separate
Leave at least one blank column and one blank row between the list and other data on the worksheet. Excel can then more easily detect and select the list when you sort, filter, or insert automatic subtotals.

Position critical data above or below the list
Avoid placing critical data to the left or right of the list; the data might be hidden when you filter the list.

Show rows and columns
Make sure any hidden rows or columns are displayed before making changes to the list. When rows and columns in a list are not showing, data can be deleted inadvertently.

List format

Use formatted column labels
Create column labels in the first row of the list. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the list. Format the cells as text before you type the column labels.

Use cell borders
When you want to separate labels from data, use cell borders — not blank rows or dashed lines — to insert lines below the labels. How to apply borders to cells.

Avoid blank rows and columns
Avoid putting blank rows and columns in the list so that Excel can more easily detect and select the list.

Don't type leading or trailing spaces
Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend list formats and formulas
When you add new rows of data to the end of a list, Excel uses consistent formatting and formulas. Three of the five preceding cells must use the same format or formula for this to occur. How to turn on extended list formats and functions.

About using a list as a database

In Microsoft Excel, you can easily use a list as a database. When you perform database tasks, such as finding, sorting, or subtotaling data, Microsoft Excel automatically recognizes the list as a database and uses the following list elements to organize the data.

• The columns in the list are the fields in the database.
• The column labels in the list are the field names in the database.
• Each row in the list is a record in the database.

Add a record to a list by using a data form

1. Click a cell in the list you want to add the record to.
2. On the Data menu->Form.->New
3. Type the information for the new record.
4. To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.
5. When you finish typing data, press ENTER to add the record.
   When you finish adding records, click Close to add the new record and close the data form.

Notes

• Fields that contain formulas display the results of the formula as a label. The label cannot be changed in the data form.
• If you add a record that contains a formula, the formula is not calculated until you press ENTER or click Close to add the record.
• While you are adding a record, you can undo changes if you click Restore before you press ENTER or click Close to add the record.
• Microsoft Excel adds the record when you move to another record or close the data form.