Slicer settings use custom lists when sorting

Create or delete a custom list for sorting and entering data

Use a custom list to sort or fill in in a custom order. Excel has built-in lists for days of the week and months, but you can also create your own custom lists.

Learn more about custom lists

When it comes to custom lists, it is helpful to understand how they work and how to store them on a computer.

Comparison between built-in and custom lists

The following built-in custom lists for days of the week and months are available in Excel:

Integrated 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.

You can also create your own custom lists and use them to sort or fill in data. If z. For example, if you want to sort or fill in data using the following lists, you will need to create a custom list because there is no natural order.

Custom lists

High medium low

Big, 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 range of cells, or you can use the dialog box list Custom lists enter.

Note: A custom list can contain only text or text that alternates with numbers. For a custom list that contains only numbers, e.g. For example, 0 to 100, you must first create a text-formatted list of numbers.

Create a custom list

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

Direct entry of values

Follow the steps below to create a custom list by entering values:

  1. In Excel 2010 and above click on file > Options > Extended > Generally > Edit custom lists.

  2. In Excel 2007 click the Microsoft Office Button > Excel options > Often used > The most common options when working with Excel > Edit custom lists.

  3. Click in the field Custom lists on NEW LIST, and then type the entries (starting with the first entry) in the field List entries a.

    Press ENTER after each entry.

  4. When the list is complete, click Add.

    The items in the selected list are in the area Custom list displayed.

  5. Click twice OK.

Create a custom list from a range of cells

Follow the steps below:

  1. In a range of cells, enter the values ​​you want to sort or fill in, in the order you want from top to bottom. Select the range of cells you just entered and follow the steps above to display the Edit Custom Lists pop-up.

  2. Check in the pop-up window Custom listswhether the cell reference of the selected list of items in the field Import list from cells is shown. Then click on Import.

  3. The items in the selected list are in the area Custom list displayed.

  4. Click twice OK.

Note: You can only create a custom list according to values, e.g. B. Text, numbers, dates and times. You cannot create a custom list for formats such as. B. cell color, font color or a symbol.

Delete a custom list

Follow the steps below:

  1. Follow the steps above to display the Edit Custom Lists dialog box.

  2. Select in the field Custom lists select the list you want to delete and click Clear.

Save custom lists

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

However, if you open the workbook on another computer or server, the custom list saved in the workbook file will not appear in the pop-up window Custom lists under Excel options displayed, but only in the column sequence in the dialog box sort by. The custom list saved in the workbook file also represents the command To complete not directly available.

If you want, you can add the custom list that is saved in the workbook file to the registry on the other computer or server so that it appears in the pop-up window Custom lists under Excel options is available. Select in the pop-up window sort by in the column sequence the entry Custom lists so that the pop-up window Custom lists is shown. Then select the custom list and click Add.

Do you need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers Community.

See also

Create a list of consecutive dates