For many people, an Excel list is the perfect vehicle for managing data, and Excel 2003 offers significant new features that make creating and working with lists easier. A list in Excel isn't, as you might suppose, a single column; it's a group of rows and columns containing related data, like a table in a database. You can now define an area on a worksheet as a list, and when you do, Excel treats it as a unit. A blue border surrounds the range, making the list distinguishable from anything else on the worksheet. You can add or delete rows and sort your data without affecting the rest of the worksheet.

Creating a List

You can turn an existing data range into a list or create one from scratch. For existing data, click on a cell in the range and choose Data | List | Create List. If your columns have headings, enable the My list has headers checkbox and click OK. To create a list from scratch, type the list headings or simply select a number of cells across a row for the headings and repeat the process; if you don't supply your own column headings, Excel names the columns column1, column2, and so on.

A list has a special insert row—marked with an asterisk—that let's you easily add a new row. You start typing and Excel adds the new row automatically. You can also click a row in the list and, from the List toolbar, choose List | Insert | Row to add a row in the middle of the list. You can even add data by typing it in the row immediately below the list or in the column to the right of it. Provided you don't have the Total Row enabled (see below), the list expands to incorporate your data. Once you've selected the list, you can also drag the handle at the bottom right of the list to adjust the number of rows or columns (but not both at the same time).

Working With Your List

When you create a list, Excel enables the AutoFilter for that list automatically. Unlike previous versions of Excel that wouldn't recognize more than one list at a time, Excel 2003 lets you have multiple lists on one sheet, each with its own AutoFilter enabled. You can disable AutoFilter for one list (Data | Filter | AutoFilter), without affecting the setting for any other list.

To sort or filter a list, click the drop-down arrow to the right of the column heading you're interested in and choose either Sort Ascending or Sort Descending to sort by that column, or you can choose an item to filter the data. You can create complex filters using the Custom option and sort on multiple columns using the Data | Sort dialog.

Although you can have two lists side by side on the same worksheet, this isn't a good idea. Sorting, adding, and removing rows from one list doesn't affect data in the second list. But if, for example, you filter one list so some rows are hidden, the same rows will be hidden in the adjacent list—in most situations, an undesirable result. This doesn't happen, however, if one list is below another on the worksheet.

You can show a Total Row at the bottom of your list, which adds a lot of immediate functionality. You enable the Total Row either by clicking the Toggle Total Row button on the List toolbar or from the Data | List menu. The Total Row sums the right-hand column, if that column contains numerical data; if not, a count of the items appears. You can disable this total or choose a different function by clicking the down arrow to the right of the total and selecting either None or another calculation. You can do the same for any of the columns by clicking in the Total Row for that column. You can't, however, add your own calculations to the Total Row options. As you would expect, if you filter the list, the totals reflect data from the visible rows only.

Among the most useful improvements to Excel lists is the new feature that updates charts created from list data automatically when you add a new list item. In the past you could achieve this result only by applying a complicated workaround. To create a chart, simply click in your list, then click on the Chart Wizard button on the new List toolbar. Should you later add other items to your list, they will be automatically included.

Created Date: 11/09/2001  Last Reviewed: 11/09/2001  Rev. Date: