Learning About Lists in Excel

While a database is generally the smart way to keep track of complex data, sometimes your needs are much simpler. In situations where your data is more like a list or table, Excel has an excellent list management tool designed for the task. Working on lists in Excel offers advantages over using a database application in that you can see your data very clearly, and you still have all the familiar Excel tools for working with them.

We’ll take a look at how lists work in both Excel 2003 and Excel 2007.

Lists in Excel 2003
List management tasks received a boost with the new list tool in Excel 2003. Using this tool you can define an area of a worksheet as a list so. This gives the area special properties such as an insert row that you can click to insert a new data row into the list. In addition, you can position lists side-by-side in the same worksheet, and they act independently of each other. This lets you add or delete a row in one list and the other list will remain unaffected by the changes.

Lists in Excel 2003 also have automatic totals built into them so that you can select the calculation to apply to a list column and Excel will automatically create the formula for you.

Use the Data > List option to convert data in a worksheet into an Excel 2003 list. (Click for larger image).

See for yourself how easy it is to work with lists in Excel 2003. Open a worksheet containing a table of data. Click somewhere inside the data area and choose Data > List > Create List. Excel makes a guess as to the range containing the data and gives you the option of specifying whether or not your list has headers.

Select OK if the data in the Create List dialog is correct, otherwise make your changes and click OK. If your list doesn’t have column headers, deselect the checkbox. Excel will create column headers automatically for you and name them Column1, Column2, etc.

Your list will appear on the screen bounded with a blue line, and it will have the Filter option enabled so that filter arrows appear to the right of each column heading. From these dropdown lists you can select an element with which to filter or sort the column. To undo the filter, choose Data > Filter > Show All.

By default Excel lists have the Filter option enabled although this can be disabled, if desired. (Click for larger image).

To create totals for the columns in your list, click the Toggle Total Row icon on the List toolbar. Click in the cell below the target column (the one you want the calculation to affect), and a small arrow will appear to its immediate right. Click this arrow and select the calculation you want from the drop-down list that appears.

The calculation options include: Average, Count, Count Numbers, Max, Min, Sum, StdDev or Var. Excel will create a formula for that calculation and show the results for only the visible values in the list. This is because Excel creates its formulas using the Subtotal function so the results reflect only visible cells in the column and not all the cells in it.

Excel creates calculations for data in your list columns using the Subtotal function. (Click for larger image).

An additional benefit of using the List tool in Excel 2003 is that if you create a chart based on the data in the list, and if you later add data to the list, the chart expands automatically to display the new data. Prior to Excel 2003, to create a chart that expands when additional data is added to a list was extremely difficult requiring a complex workaround solution.

You can add and delete rows from your list by right clicking and choose Insert > Row or Delete > Row. To add a column to your list, simply type a value into the empty column to the right of the list, and the list expands automatically to include the new column.

If at any time you no longer want to use the List functions in Excel 2003 you can disable this. Click inside the List to activate it and then from the List toolbar click List > Convert to Range and click Yes when prompted to do so. The List will convert back to regular data in an Excel worksheet.

Lists in Excel 2007
In Excel 2007 tables have replaced lists. Their effect is similar but the process of creating them is a little different. To format a list as a table, select it and from the Home tab select the Format as Table option in the Styles group. Select a table style to apply to your data.

In Excel 2007, the table column headings appear in place of column headings when you scroll down the worksheet. (Click for larger image).

If you have a large table, when you scroll beyond the first screen, you will notice that the column letters A, B, C. D, etc. change to display the table headings. This ensures that you can easily identify the data you are looking at without having to resort to using the Freeze Panes command even when your worksheet is very large.

You manage the heading feature by using Table Tools > Design > Header Row. If the Header Row is enabled, the first row will be formatted differently and appear as column headings, if it’s not, the regular column letters appear.

The AutoFilter is enabled by default for all Excel tables, but you can disable this if you like, by selecting the Home button and dropdown the Sort and Filter list and deselect the Filter. The column headings continue to work even if filtering is disabled.

In Excel 2007 you can select an automatic calculation from the AutoSum option on the Home tab. (Click for larger image).

If you select the cell below a column of numbers or text in the table you can click the down-pointing arrow to the right of the AutoSum function on the Home tab’s Editing area. From here you can select a calculation to apply to that column of values. When you do, the calculation works as a Subtotal function so it adjusts to show the result for all visible cells and not all cells in the table column.

In Excel 2007 you have more attractive table formats than you’ll be used to using in Excel 2003 but apart from these, the lists function very much the same in Excel 2007 as in Excel 2003.

Whether you’re working with Lists in Excel 2003 or Tables in Excel 2007, you will find the tools that Excel provides for managing data in a table-like format make Excel a viable alternative to using a database application.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com