Excel > Excel 2003 Advanced > Data Management

Using Excel Lists

An Excel list is a collection of related data organised into rows and columns. It is an incredibly powerful feature that anyone using Excel should be aware of.

Excel will update cell ranges for you when you use Excel lists. When you insert new columns or rows to the list, they are picked up automatically. No need to update your cell references and formulas.

Create a list

1. Select a cell within the list of data
2. Click Data > List > Create List or press Ctrl + L

The Create List dialogue box appears displaying the range of cells to be used for the list.



If this is incorrect, select the range of cells you want to use

3. Click Ok

A blue border appears around the edge of your data identifying the list range, and the List toolbar appears

If the List toolbar does not appear, click View > Toolbars and then List to view the List toolbar

Adding rows and columns to a list

When adding data to a new row or column at the end of a list range, the new row or column is automatically picked up and used eliminating the need to update cell references or formulas.

After Entering data into the empty column at the end of the list range, the list picks up the new column, and a smart tag appears asking if you are happy for the list to do this or not.

A new record row is provided at the bottom of the list range. Simply enter the new data into this row to add records.

When you select a cell outside of the list, this row disappears. It is only visible when working on the list.

Adding totals to a list

Totals can be added to a list quickly and easily without the need to enter any formulas. The function can then be switched from Sum to Average, or Average to Count with just the click of a button.

1. Select a cell in the list
2. Click the Toggle Total Row button on the List toolbar



3. A total row is displayed at the bottom of the list and the last column is totaled
4. Select the cell you want to total and click the list arrow that appears



5. Select the function you want to use from the list

The function used can be changed simply by clicking the list arrow again and selecting a different function

Printing a list

The list can be printed without the need to select a print range.

1. Select a cell in the list
2. Click File > Print
3. Select List in the Print what panel of the Print dialogue box



4. Click Ok

Converting a list to a range

The list can easily be converted back to a range if no longer required.

1. Select a cell in the list
2. Click the List button on the List toolbar
3. Select Convert to Range from the list

A message appears asking if you are sure you want to convert the list back to a range



4. Click Yes and the list is converted back to a range

As we saw earlier this range can easily be converted back to a list if needed.

Database functions Converting text to columns

Guides