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 the information. In this Exploring Office 2007 article we'll take a look at how lists work in both Excel 2007 as well as the older Excel 2003 release.
» 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, which 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.
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.
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.
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 would expand when additional data was 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 functionality. To do so, 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.