About Excel lists

An Excel list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.) provides features designed to make it easier to manage and analyze groups of related data in a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.). When you designate a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) as a list, you can manage and analyze the data in the list independently of data outside the list. For example, using only the data contained within the list, you can filter columns, add a row for totals, and even create a PivotTable report, using only the data contained within the list.

You can have multiple lists on your worksheet, which allows you a great deal of flexibility for separating your data into distinct, manageable sets according to your needs.

 Note   You cannot create a list in a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.). You must remove the workbook from shared use first if you want to create a list.

Excel lists make it easy to share data with other users through integration with Microsoft Windows SharePoint Services. As long as you have the Web address and authoring rights on a SharePoint site, you can share your list so other people can view, edit, and update the list. If you choose to link the list in Excel to a list on a SharePoint site, you can synchronize changes with the SharePoint site so that other users can see updated data.

When you create a list in Excel, the features of the list and the visual elements designed to enhance these features make it easy to identify and modify the contents of the list.

Overview of list elements

Callout 1 Every column in the list has AutoFilter enabled by default in the header row. AutoFilter allows you to filter or sort your data quickly.
Callout 2 The dark blue border around the list clearly distinguishes the range of cells that makes up your list.
Callout 3 The row that contains an asterisk is called the insert row. Typing information in this row will automatically add data to the list and expand the border of the list.
Callout 4 A total row (total row: A special row in a list that provides a selection of aggregate functions useful for working with numerical data.) can be added to your list. When you click a cell within the total row, a drop-down list of aggregate functions becomes available.
Callout 5 You can modify the size of your list by dragging the resize handle found on the bottom corner of the list border.

The benefits of lists

When you specify a range of cells as a list in Microsoft Excel, the list user interface integrates and makes readily available much of the standard functionality that you might want to use on the data within that list.

  • Sort and filter lists     You can sort lists in ascending or descending order or create custom sort orders. You can also filter lists to show only the data that meets the criteria you specify.

One of the common things that you do with data in Excel is to filter the data based on different field values. To make this functionality available to you when using lists, AutoFilter drop-down lists (drop-down list box: A control on a menu, toolbar, or dialog box that displays a list of options when you click the small arrow next to the list box.) are automatically added in the header row of a list when the list is created.

The AutoFilter drop-down list contains new functionality: Sort Ascending, Sort Descending, and other sort options are located at the top of the drop-down list. This functionality sorts the entire list on the field where it is chosen, in the specified order. The remaining drop-down selections are unchanged from the functionality in earlier versions of Excel.

  • Ensure data integrity     For lists that are not linked to SharePoint lists, you can use the built-in data validation features in Excel. For example, you may choose to allow only numbers or dates in a column of a list. For lists that are linked to SharePoint lists, the list validation features of Windows SharePoint Services are automatically applied to the list. For example, when you publish and link the list to a server that is running Windows SharePoint Services, or when you edit an existing SharePoint list in Excel, data type rules are applied to each column in the list to ensure that only one type of data is allowed in each column.
  • Format list objects     You can format cells in a list the same way that you format cells in a worksheet.
  • Insert rows     Another action that is common when you work with a list is to add a new row. To that end, an inserted row is displayed whenever the list is active. This interface element consists of a blank row, displayed directly below the last row of data, with a blue asterisk (*) inside the left-most cell.

When the list is inactive, the asterisk is removed and the list border shifts up to the bottom of the last row of data.

  • Display a total row     To display a total row, click the Toggle Total Row button Button image on the List toolbar. This total row is displayed below the insert row when the list is active, and shifts up to the row just below the last row of data when the list is not active. When turned on, the word "Total" is displayed in the left-most cell, and an appropriate Subtotal formula in the right-most cell.

The total row provides you the opportunity to display some manner of total for all columns in your list. When you click in any a cell in the total row, an arrow appears to the right of the cell. You can then click this drop-down list arrow to display a number of aggregate functions. When you choose one, a subtotal function is then inserted into that cell.

 Note   You cannot manually edit the cells of the total row to add different functionality. You can select only an aggregate from the drop-down for use in the subtotal function that is inserted into the cell by Excel.

  • Compatible with lists in Windows SharePoint Services     When you publish a list to a SharePoint site, you are creating a custom SharePoint list. If you choose to link the list when you publish it, or when you export an existing SharePoint list, you can edit that list offline and synchronize the changes to the SharePoint list at a later time.
 
 
Applies to:
Excel 2003