Create or delete a custom list for sorting and filling data

You can use a custom list to sort or fill in a user-defined order. Microsoft Office Excel provides day-of-the-week and month-of-the year built-in lists, but you can also create your own custom list.

What do you want to do?


Learn more about custom lists

To understand custom lists, it is helpful to see how they work and how they are stored on a computer.

Comparing built-in and custom lists

Office Excel provides the following built-in, day-of-the-week, and month-of-the year custom lists.

Built-in lists
Sun, Mon, Tue, Wed, Thu, Fri, Sat
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
January, February, March, April, May, June, July, August, September, October, November, December

 Note   You cannot edit or delete a built-in list.

But you can also create your own custom list, and use them to sort or fill. For example, if you want to sort or fill by the following lists, you need to create a custom list, because there is no natural order.

Custom lists
High, Medium, Low
Large, Medium, and Small
North, South, East, and West
Senior Sales Manager, Regional Sales Manager, Department Sales Manager, and Sales Representative

You can base the custom list on a cell range, or you can enter the list in the Custom Lists dialog box.

 Note   A custom list can only contain text or text mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text.

How custom lists are stored

Once you create a custom list, it is added to your computer's registry, so it is available for use in other workbooks. If you use a custom list when sorting data, it is also saved with the workbook, so that it can be used on other computers, including servers where your workbook might be published to Excel Services and you want to rely on the custom list for a sort.

However, if you open the workbook on another computer or server, you do not see the custom list that is stored in the workbook file in the Custom Lists dialog box that is available from Excel Options, only from the Order column of the Sort dialog box. The custom list that is stored in the workbook file is also not immediately available for the Fill command.

If you want, you can add the custom list that is stored in the workbook file to the registry of the other computer or server and make it available from the Custom Lists dialog box in Excel Options. From the Sort dialog box, under the Order column, select Custom Lists to display the Custom Lists dialog box, select the custom list, and then click Add.

Top of Page Top of Page

Create a custom list

There are two ways to create a custom list. If your custom list is short, you can type the values directly in the dialog box. If your custom list is long, you can import it from a range of cells.

Create a custom list by typing in values    

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
  2. In the Custom Lists box, click NEW LIST, and then type the entries in the List entries box, beginning with the first entry.

Press ENTER after each entry.

  1. When the list is complete, click Add.

The items in the list that you selected are added to the Custom lists box.

  1. Click OK twice.

Create a custom list from a cell range    

  1. In a range of cells, enter the values that you want to sort or fill by, in the order that you want them, from top to bottom. For example:
A
1 High
2 Medium
3 Low
  1. Select the range that you just typed. In the preceding example , you would select cells A1:A3.
  2. Click the Microsoft Office Button Button image, click Excel Options, click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
  3. In the Custom Lists dialog box, verify that the cell reference of the list of items that you selected is displayed in the Import list from cells box, and then click Import.

The items in the list that you selected are added to the Custom Lists box.

  1. Click OK twice.

 Note    You can only create a custom list based on a value (text, number, and date or time). You cannot create a custom list based on a format (cell color, font color, and icon).

Top of Page Top of Page

Delete a custom list

  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. Click the Popular category, and then under Top options for working with Excel, click Edit Custom Lists.
  2. In the Custom Lists box, select the list that you want to delete, and then click Delete.

Top of Page Top of Page

 
 
Applies to:
Excel 2007