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 List in Excel
To 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.
Once you click OK, you will notice several things about this List.
- There is a thick blue border around it that becomes a thin blue border if the active cell is outside the List range.
- 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.
- There is a default Header row formatted in boldface and has AutoFilter turned on.
- There is a List toolbar showing (unless you’ve dismissed it previously).
The List toolbar gives you several options from the drop-down menu when you create list in Excel. 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.
These functions all utilize the SUBTOTAL function, which take into account any filtering you may have done in the List.
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.
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.
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.