Create a List in Excel 2003

by Gregory on April 30, 2011

The forerunner of the modern Excel Table is know as a List Object. Excel 2003 has this feature, and I’ll simply refer to it here as a List.

To create a List in Excel 2003, select your existing data table and choose Data > List > Create List…, or use the keyboard shortcut Ctrl+L.

Create a List in Excel 2003

Excel will identify the List range and bring up a Create List dialog box. This box gives you the option to re-select the List range and let Excel know if the List has Headers in the first row.

List Selection in Excel 2003

Once you click OK, you will notice several things about this List.

  1. There is a thick blue border around it that becomes a thin blue border if the active cell is outside the List range.
  2. There is a new row at the bottom of the List that’s used to enter new data and has an asterisk in the first column.
  3. There is a default Header row formatted in boldface and has AutoFilter turned on.
  4. There is a List toolbar showing (unless you’ve dismissed it previously).

List Toolbar in Excel 2003

The List toolbar gives you several options from the drop-down menu. Insert or Delete rows or columns in the List, Sort the List, generate a Form to enter data into the List, Resize the List, or Convert the List to a Range.

You can Publish the List to a SharePoint site, which is something I know nothing about. There are also buttons to Create a Chart, and to Print the List.

You can also Toggle the Total Row which gives you several functions to choose from in each column of the List.

Toggle Total Row in List

These functions all utilize the SUBTOTAL function, which take into account any filtering you may have done in the List.

Give Your List a Defined Name

If you give the List a Defined Name it will automatically expand as the list grows or changes. To do this I select any cell inside the List and use the keyboard shortcut Ctrl+Shift+* to select the List. Choose Insert > Name > Define and give the List a Name then click OK.

Define name dialog box

I can use this TableOne name in the formula =ROWS(TableOne) to see how many rows are in the List, which happens to be 4 at this point.

When I add another row of data to the TableOne List the formula reflects the change, hence Excel is automatically modifying the TableOne Defined Name range. Toggling on the Total Row has no effect on the TableOne range.

List Range Defined Name

This is a nice feature if you want to utilize the imposing INDEX function to refer to a column in the List. I can create a reference to column 2 in this List by choosing Insert > Name > Define and typing a name like ColumnTwoTableOne then typing in the formula =INDEX(TableOne,0,2) in the Refers to box and clicking OK.

Related Posts Plugin for WordPress, Blogger...
P.Sivanpandi February 28, 2013 at 12:07 am

Hi,
Need details about data validation
cell have below list

JMC PROJECTS (INDIA) LTD
VASOO BUILDERS
JMC PROJECTS (INDIA) LTD
BRIGADE ENTERPRISES
JMC PROJECTS (INDIA) LTD
JMC PROJECTS (INDIA) LTD
BRIGADE ENTERPRISES
JMC PROJECTS (INDIA) LTD
BRIGADE ENTERPRISES

i want all name do not repeat again in another list

Gregory March 3, 2013 at 12:11 pm

I’m sure there might be a way to do this with enough time and effort, but I don’t know it. The best I can offer is to remove the duplicates from your list by selecting it and going to Data > Remove Duplicates. Sorry I couldn’t be of more help.

jackie April 16, 2013 at 11:10 am

Hi i am studying how to do create a list in 2007 but it doesnt look familiar like in 2003..there is no list command…is it just called a table in excel 2007? i appreciate your help

Jackie

Gregory April 16, 2013 at 5:41 pm

Yes you have reached the future, Tables, which replace the old Lists.

jackie April 17, 2013 at 2:23 pm

Hi, I would like to ask another question about lists/tables in excel.
Is there any way of knowing if Excel 2007 have the List Toolbar associated with lists.
I assume it doesnt however I would like to be sure,
Thanks again for your help,
Jackie

Comments on this entry are closed.

Previous post:

Next post: