Creating a List on a Worksheet

Excel's row-and-column structure makes it an ideal tool for organizing related information into a list. On an Excel worksheet, a list is a group of consecutive rows of related data. Conceptually, an Excel list is identical to a table in Access (or any other database management program). Each column within a list is a field, and each row is a record of data; headings in the top row represent the names of the fields. Within each field, you can enter text, numbers, dates, formulas, or hyperlinks. Excel does not impose any additional restrictions on the type of data that you can enter in a list.

You can sort list data in nearly any order, search for a specific bit of information, or use filters to find groups of data that match criteria that you specify. For complex lists, Excel can automatically create outlines that let you summarize and subtotal groups of records. Large, complex lists are a perfect starting point for PivotTable and PivotChart reports, which let you drag fields on a layout page to perform complex data-analysis tasks without having to construct a single formula.

→ For more details on how to use external data in an Excel list, see "Creating Links to External Databases".

→ For a full discussion of the wonders of PivotTables, see "How PivotTable and PivotChart Reports Work".

Excel uses column labels in the first row of a list (also called the header row) to identify the names of fields. Although you can create a list without a header row, we highly recommend that you include column labels for every list that you create or import. You must have a header row if you want to enter data using forms or use the AutoFilter feature to find groups of records.

When creating a list, follow these basic guidelines:

• Create only one list per worksheet. Many of Excel's list features (including its Auto Filter capability) depend on being able to identify one list per sheet. If you need to create multiple lists in a workbook, put them on different sheets.

→ For more details on how to manage multiple worksheets in a single workbook, see "Working with Multiple Worksheets".

• Create a single header row with a unique label for each column. Apply distinctive formatting to make the column labels stand out from the data area. Use a larger font size with bold attributes, for example, and add a border beneath the header row. Excel uses this formatting to identify the header information in lists when sorting and creating reports.

• Don't leave any blank rows or columns in your list. When you sort or search, Excel ignores data that appears below a blank row or to the right of a blank column. You can safely leave individual cells in a row or column blank, however.

→ To prevent yourself or other users of a worksheet from entering invalid data, including blank cells, create data validation rules; see "Restricting and Validating Data Entry for a Cell or Range".

• Although you can start a list at any cell, you should avoid leaving any blank columns to the left of the list. You might want to leave five or six blank rows above the header row of the list, to create a criteria range or add a title. Remember to leave at least one blank row between any such data and the start of the list.

• To make it easier to enter data, freeze the worksheet panes just below the header row.

→ For instructions on how to freeze worksheet panes, see "Locking Row and Column Labels for Onscreen Viewing".

When you enter new data in an existing list, Excel automatically picks up formatting and formulas from the previous rows, without requiring you to explicitly format cells in the new row. For example, if the first cell in the previous row is formatted in bold italic, Excel automatically applies that formatting as soon as you enter the data into the first cell in the new row. If the last cell in the previous row contains a formula that multiplies the values in the two previous cells, Excel adds that formula as soon as you enter data in the second of the two cells that make up that formula.

This feature isn't foolproof. For some inexplicable reason, Excel won't automatically pick up date formatting from the previous row, although it will consistently copy font formatting and attributes. Likewise, new rows pick up colors and shading consistently, but borders don't always extend as you expect. Although the documentation claims that Excel will pick up formatting and formulas that match three of the previous five rows, we found that this automatic feature works consistently only if the formatting appears in four of the previous five rows.