Create or delete a custom list for sorting and filling data

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

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

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'll need to create a custom list, since 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

A custom list can correspond to a cell range, or you can enter the list in the Custom Lists dialog box.

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

Enter values directly

Follow these steps to create a custom list by entering values:

1. Click File > Options > Advanced > General > 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 the Enter key after each entry.

3. When the list is complete, click Add.

   

   The items in the list that you have chosen will appear in the Custom lists panel.

4. Click OK twice.

Create a custom list from a cell range

Follow these steps:

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. Select the range of cells you just entered, and follow the previous instructions for displaying the Edit Custom Lists popup window.

2. In the Custom Lists popup window, verify that the cell reference of the list of items that you have chosen appears in the Import list from cells field, and then click Import.

3. The items in the list that you have chosen will appear in the Custom Lists panel.

   

4. Click OK twice.

Follow these steps:

1. Follow the previous instructions for displaying the Edit Custom Lists dialog.

2. In the Custom Lists box, choose the list that you want to delete, and then click Delete.

Once you create a custom list, it is added to your computer registry, so that 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 popup window 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 prefer, 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 popup window in Excel Options. From the Sort popup window, in the Order column, select Custom Lists to display the Custom Lists popup window, then select the custom list, and then click Add.