How to use lists in Excel 2003
How to create a list
1. Select the range of data that you want to make into a list, or just click
inside the data if you want it all.
2. On the Data menu, point to List, and then click Create List.
3. If your data has column headings, make sure that the check box My list has
headers is selected. Click OK.
If your list does not have headers, Excel will create them for you. They'll say
"Column1," "Column2," and so on.
The list is identified by a dark blue border. When you select a cell, row, or
column outside a list, the list becomes inactive and is surrounded by a light
blue border.
Tips
• You can also create a list by pressing CTRL+L, or by right-clicking and
then clicking Create List on the shortcut menu.
• If you prefer to see no border at all when you click outside a list,
right-click inside the list. On the shortcut menu, point to List, and then click
Hide Border of Inactive Lists. To reactivate the border, repeat this process.
This will hide or display the borders of all inactive lists on a worksheet.
How to add or delete list rows and columns
Use any of the following methods:
• Type data in one of the cells with the asterisk. This will automatically
add a row to the end of the list, and move the asterisk to identify the next row
as the insert row.
• Type in an empty row adjacent to the list. The list will automatically expand
to include that row, unless the total row has been displayed, or the last row of
the list is also empty.
• Type in an empty column adjacent to the list. The list will automatically
expand to include the column.
If this method does not work, on the Tools menu, click AutoCorrect Options. On
the AutoFormat As You Type tab, select the Include new rows and columns in list
check box. Or clear the check box to turn off this feature.
• Click in the list where you want to insert or delete. On the List toolbar,
click List, click either Insert or Delete, and then click either Row or Column.
• Or right-click a cell and click Insert or Delete on the shortcut menu and then
click Row or Column.
• Or click and drag the lower-right corner of a list to add or remove rows or
columns in your list. If the columns or rows contain data, it will be included
in the list. You cannot add or remove both rows and columns at the same time.
How to total, count, or average list data
To sum data in the last column, on the List toolbar, click the Toggle Total Row
button. To turn off the total, click the button again. If the last column
contains data that cannot be summed, such as a column of names, Excel will count
the number of items instead of giving a sum.
You can do more calculations than sums. Click in the cell with the sum. An arrow
will appear to the right. Click the arrow and make a selection from the menu.
You can do these calculation in other columns than the last one. After you've
summed that column, click in the total row in another column. An arrow will
appear to the right of that column. Click the arrow and select from the menu.
How to sort and filter data
Click in a list to activate the AutoFilter arrows. Then click any arrow and make
a selection to either sort or filter data in that column.
Create a custom filter
1. Click the AutoFilter arrow in the column you want to filter.
2. Click Custom to open the Custom AutoFilter dialog box. Then make your
selections in the dialog box.
Create a Top 10 filter
1. Click the AutoFilter arrow in the column you want to filter.
2. Click Top 10 to open the Top 10 AutoFilter dialog box. Choose a top or bottom
number or a percentage.
Convert a list to a range
This simply means that you'll remove the special list functionality. Your data
will still remain on the Excel worksheet.
1. If necessary, click in the list to activate the List toolbar.
2. On the List toolbar, click List.
3. Click Convert to Range.
4. You'll see a message asking whether you want to convert the list to a normal
range. Click Yes.
The data no longer has any of the special list functionality. It's just normal
data in Excel.
Note: all our examples are offered as is
with no warranty. J & R Excel Solutions accept no responsibility for any loss
arising from your use of them