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.
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.
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.
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.