Search Blog

Friday, February 16th, 2007

Excel 2003 lists

Excel 2003 offered a cool new tool for managing data that was in a list format. It made Excel the place of choice for small lists and it simplified the process of charting list data – Excel lists expanded automatically to allow for more data to be entered and charts based on the list data automatically expanded to include the new data – wonderful!

Here’s how to work with lists in Excel:

Turn an existing table of data into a list by clicking on a cell in the range and choose Data, List, Create List. If your list has a heading row, enable the My list has headers checkbox and click Ok. Notice the border around the list.

To add a row, click in the list area and click in the last (Insert) row which has an asterisk in its first cell. It is also possible to add a row in the middle of the list by clicking where the row should appear and choose List, Insert, Row.

When data is created as a list, the AutoFilter feature is enabled. To sort data in the list, click the dropdown arrow to the right of the column (field) to sort on and choose Sort Ascending or Sort Descending as required. To sort on multiple columns, use the Data, Sort dialog.

To create a complex filter for your list, click the Custom option from the dropdown list for the field that you want to create the query on. Set the tests to use and select And or Or depending on what information you need to extract. Click Ok to view the results. To display all records again, choose Data, Filter, Show All.

When you create a chart based on list data it will be automatically updated when you add a new item to the list. To create your chart, click in the list and click the Chart Wizard button on the new List toolbar and proceed through the Wizard as you would for any other chart.

To perform calculations on list data use the Toggle Total Row button. This adds a total row to the list and totals the right most column. To disable this total or create another one, click the down pointing arrow to the right of the total and choose None or a different calculation. Each column has its own down pointing arrow from which you can select the calculation to be made on that column’s data.