Applies to
Microsoft Office Excel 2003

Lists. You can't escape them. From buying groceries to compiling sales reports, lists can help organize almost every type of data. Excel 2003 provides several tools that help you create and manage lists. The newest tools help you group related data and analyze that data. Other tools can help ensure that data is entered accurately—and even save you some typing.

These tools include:

• Excel and XML lists  The new tools for grouping and analyzing data.
• Drop-down lists  The tool for helping users enter data accurately.
• Pick From Drop-down List command  A tool that can save some keystrokes.
• Custom lists or custom fill series  Another tool that saves you some keystrokes.

This article provides a broad overview of the list tools and the best ways to use them. For more information about how to use each tool, see the related links at the end of each section.

Excel lists

Note  The Excel list is new in Excel 2003.

An Excel 2003 list is a section of a worksheet that you can manipulate separately from the larger worksheet. You use an Excel list when you need to analyze parts of the data in a large spreadsheet, and do so independently of any surrounding data. For example, you can add data, sort data, and rearrange the rows in a list without affecting the surrounding cells.

Excel lists also help you work faster because they make a set of data manipulation tools, including the AutoFilter buttons and a set of aggregate functions, available from one place.

Here is a typical Excel 2003 list:

 AutoFilter buttons  The buttons appear automatically, and they provide the same functionality as in older versions of Excel. You can quickly sort and filter your data, find high or low values, and so on.

 Border  The border distinguishes the cells in your list from the surrounding data.

 Insert row  The asterisk denotes the row. You can type data directly in the cells in this row, and the list will automatically resize to accommodate the new data.

 Total row  Click a cell in this row to select from a list of aggregate functions. You use the List toolbar to display or hide the Total row. When you display the Total row, Excel automatically adds the data in the right-hand column if that column contains numeric data.

 Aggregate functions  Select a function to insert it into the selected cell in the Total row. You don't have to enter the function manually, so you can add calculations in less time and with more accuracy.

 Resize handle  Modify the size of your list by dragging the handle.

More information

These links take you to more information about Excel 2003 lists:

• Create an Excel list
• Add or delete rows or columns to a list
• Convert a list to a range  (Converting a list to a cell range has the same effect as removing a list.)
• Troubleshoot lists
• About Excel lists

XML lists

Note   XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 or Excel 2003.

XML lists have the same look and functionality as Excel lists, but they take their data from an XML data source instead of a range of cells. When you open an XML file in Excel, an XML list is one of your options for viewing and working with the data. In an XML list, you can quickly sort, filter, total, and analyze the data.

Here are some other ways to view or work with XML data in Excel:

• Open the XML file as a read-only workbook. Use this option when you just want to browse the data.
• Use the XML Source task pane to map cells in a worksheet to elements in an XML schema. (A schema is the structure of the data in an XML file.) This is the most flexible option because it allows you to use just the data you need. It also allows you to save your data in the XML data file format, which makes your data easier for others to use.

More information

To start, search on "xml list" in Excel Help. Also, these links take you to more information about Excel 2003 XML lists:

• Import XML data
• Using XML in Excel

Drop-down lists

Note  Drop-down lists are available in Microsoft Excel 97 and later.

When many people think of a "list" they think of drop-down lists like this one:

You create drop-down lists when you need to limit the choices users can make, and when you want to ensure that a piece of data, such as a part number or a diagnostic code, is entered accurately.

Note  Because a drop-down list helps ensure that users enter valid data, it's often referred to as a data-validation tool. To find information in Excel Help about drop-down lists, search on "drop-down list" and "data validation."

More information

These links take you to more information about drop-down lists:

• Create a drop-down list
• Designate valid cell entries
• Remove data validation
• Find cells that have data validation
• Troubleshoot data validation
• Overview and samples of data validation

Pick From Drop-down List command

You use the Pick From Drop-down List command to quickly insert a value into an empty cell. The command saves you having to retype an existing value that resides in the same column. To try it, follow these steps:

1. In a blank column of a worksheet, enter a list of items. "Apples," "oranges," "peaches," and "pears" will do if you can't think of a list off-hand.
2. Right-click the cell directly below the last item, and click Pick From Drop-down List.
3. Select an item from the list.

Notes

• In Excel 2002, 2000, and 97, the command is called Pick From List.
• Values must reside in columns. The command does not work with rows of data.

Custom list or custom fill series

Note  Custom fill series are available in Microsoft Excel 97 and later.

You create a custom list (also called a custom fill series) when you want Excel to do the typing for you. If you enter the first value from a custom list, and then fill down or across a range of cells, Excel writes the remaining items of the list in the selected cells.

Try it. Find an empty column or row in a worksheet. In the first cell, type January. Next, grab the fill handle and drag down or across 11 empty cells. Excel recognizes the first item in the list and fills in the remaining cells with the other values in the list, like this:

While Excel recognizes many fill series automatically (for example, times, dates, months, and consecutive numbers), you can enter custom lists of items in the Custom Lists tab of the Options dialog box (Tools menu). Follow the on-screen instructions.

More information

To start, search on "custom list" and "custom fill series" in Excel Help. This link also takes you to more information about custom lists:

• Create, change, or delete a custom fill series