Converting an existing list into an Excel list

The process for turning a standard data list into a dynamic Excel 2003 list couldn't be simpler. Position the cell pointer in a cell of the data list and then choose Data → List → Create List or press Ctrl+L. Excel automatically selects all the data in the list (assuming that its bounded all around either by a worksheet border or a blank column or row) and then displays the Create List dialog box.

This dialog box displays the range address for the selected data table in the Where Is the Data for Your List text box and automatically selects the My List Has Headers check box. If Excel somehow selects the wrong range for your data list, select the cell range in the worksheet while the Where Is the Data for Your List text box is selected. (Excel automatically minimizes the Create List dialog box to this text box as you drag through the cell range in the worksheet.) If your list doesn't happen to have a row of field names at top, clear the My List Has Headers check box before clicking OK. (When a list doesn't have a header row, Excel goes ahead and adds one for you containing generic field names such as Column1, Column2, and so forth.)

When you close the Create List dialog box, Excel automatically adds filter buttons (just like when you choose Data → Filter → AutoFilter) to each of the cells with the field names in the top row of the list. It also displays the List toolbar whose buttons come in very handy when you need to edit the structure of the new list.

You'll also notice that all the cells of the new list are selected and that Excel draws a dark blue line around its borders. (You may only a see a couple on-screen if your list has more columns and rows than Excel can display at one time.) If you scroll down to the bottom of the list, you'll notice that Excel adds a blank row to the new list marked with a blue asterisk in the first cell. (This asterisk isn't really entered in this cell; it just acts as a marker indicating that this cell is where you enter the new list data.)

You can enter new data for the list directly in this row with the blue asterisk, or you can use the data form to have Excel do it for you. If you choose to enter the data directly into the blank row, Excel adds a new blank row (with the blue asterisk) as soon as you make a data entry in the first cell. You can also use the same keystrokes to enter the data and navigate the list as you do when entering data into a preselected data range.

If you want to use the data form to make new entries, display the form for your list by clicking the List button on the List toolbar and then select the Form item on the pop-up menu. After the list's Data Form displays, you can use it to add the new row (record) to the end of the list or even use it to find and edit existing data.

When you select any cell outside of the Excel list, the program not only deselects all the cells in the list but also removes the blank row at the bottom with the blue asterisk and hides the List toolbar. You'll also notice that the heavy blue line around the perimeter of the list turns light blue. As soon as you select any cell in the list, the blue line turns heavy again, and the blank row for new data entry and the List toolbar both reappear.

If, as you add rows to the list, Excel encounters a row that contains existing data that could be overwritten, the program displays an alert dialog box indicating that the list is inserting rows that could cause data in cells below to shift down. You then have to click OK to continue work. To prevent this type of interruption to your data entry in the future, be sure to select the Do Not Display This Dialog Again check box.