How to Create Custom Lists in Excel?

Excel has a fantastic feature called Custom Lists that lets you create your own lists and quickly apply them in various ways. 

When you create a custom list in Excel, you can use these lists as a basis to sort or autofill data.

Excel already has some built-in lists, like days of the week and months of the year. However, you may want to create your own custom lists to suit your unique needs. 

For example, you might use custom lists for product categories, sales regions, department names, or team members’ names. 

A useful use case would be when you create a custom list for priority, such as High, Medium, or Low, and then use it to sort your data.

In this article, I will show you some methods to create custom lists in Excel.

Method #1: Enter Values Directly Into the Custom List Dialog Box

If your custom list is short, you can enter the values directly into the “Custom List” dialog box.

Consider a scenario where you frequently enter a specific set of values into a column, like High, Medium, and Low ratings, as shown in the following example:

You can create a custom list by entering the values directly into the “Custom List” dialog box to allow you to quickly populate cells with these values using autofill. 

You can create the custom list using the following steps:

1. Click the “File” tab on the Ribbon to open the Backstage window.

2. Click “Options” at the bottom of the left sidebar of the Backstage window.

3. On the “Excel Options” dialog box that appears, click “Advanced” on the left sidebar, scroll down to the “General” section and click the “Edit Custom Lists” button.

4. On the “Custom List” dialog box that appears, select “NEW LIST” on the “Custom lists” list box, type the values on the “List entries” box, and click “Add.”

The custom list appears at the bottom of the “Custom lists” list box, as shown below:

5. Click “OK” on the “Custom Lists” dialog box.
6. Click “OK” on the “Excel Options” dialog box.

Once you’re done with the above steps, a new custom list has been created in your Excel.

Note that once you create a custom list, it is stored as a part of the Excel application, and you will be able to use it in any Excel file on your system.

How to Use the Custom List

To apply the custom list, use the below steps:

1. Enter the first value from your custom list in a cell.

2. Drag the fill handle (a small square at the bottom-right corner of the cell) across the range where you want to fill the values. Excel will automatically fill the cells with the values from your custom list.

Also read: How to Generate Random Names in Excel?

Method #2: Create a Custom List By Importing From a Cell Range

You can create a custom list by importing values from a cell range into the “Customs List” dialog box.

Suppose you regularly input a particular set of values into a column, such as High, Medium, and Low ratings, as shown in the example below:

You can create a custom list by importing the values into the “Custom List” dialog box to allow you to quickly populate cells with these values using autofill. 

You can use the below steps:

1. Enter the values in the order you prefer, starting from the top and moving downwards. After entering the values, select the range of cells.

2. Click “File” on the Ribbon to open the Backstage window.

3. Click “Options” at the bottom of the left sidebar of the Backstage window.

4. On the “Excel Options” dialog box that appears, click “Advanced” on the left sidebar, scroll down to the “General” section and click the “Edit Custom Lists” button.

5. On the “Custom Lists” dialog box that appears, verify the cell reference of the values you selected appears on the “Import list from cells” field and click “Import.”

The values in the list you selected will appear on the “Custom lists” list box as shown below:

6. Click “OK” on the “Custom Lists” dialog box.
7. Click “OK” on the “Excel Options” dialog box.

How to Apply the Custom List

To apply the custom list, use the following steps:

1. Enter the first value from your custom list in a cell.

2. Drag the fill handle (a small square at the bottom-right corner of the cell) across the range where you want to fill the values. Excel will automatically fill the cells with the values from your custom list.

Note: You can only import a list of text values. If you attempt to import a list of numeric values, Excel returns the error, “Cells without simple text were ignored,” as shown below.

How to Use a Custom List to Sort Data

You can implement custom sorting criteria to lists by utilizing a custom list.

Assuming you have a dataset displaying the sales volume of different regions and desire to sort it according to the ratings of High, Medium, and Low in column B.

Using Excel’s default sorting options to arrange sales volume from highest to lowest, the data will be sorted in reverse alphabetical order from Z to A. This means that the order of display will be Medium, Low, and High, as shown below:  

You must use a custom list to sort the list in High, Medium, and Low order. 

You use the steps below:

1. Create a custom list using either Method #1 or Method #2. 

2. Select the dataset, including the headers.

3. On the “Data” tab, click the “Sort” button on the “Sort & Filter” group.

4. On the “Sort” dialog box that appears, select “Sales Volume” on the “Column” drop-down, “Cell Values” on the “Sort On” drop-down, and  “Custom List” on the “Order” drop-down.

5. On the “Custom Lists” dialog box that appears, select “High, Medium, Low” on the “Custom lists” list box and click “OK.”

The custom list is fed onto the “Order” drop-down on the “Sort” dialog box.

6. Click “OK” on the “Sort” dialog box.

The dataset is immediately sorted according to the order in the custom list.

Also read: How to Unsort in Excel (Revert Back to Original Data)

How to Edit or Delete a Custom List

You may need to modify a custom list by adding or removing items. You may also need to delete a list that you no longer use. Both operations can be done easily using the steps below:

To edit a custom list, select it on the “Custom lists” list box on the “Customs Lists” dialog box, make the desired changes on the “List entries” list box and click “Add.” The custom list is updated accordingly.

To delete a custom list, select it on the “Custom lists” list box on the “Customs Lists” dialog box, and click “Delete.”

Click “OK” on the warning message box indicating that the list will be permanently deleted.

Also read: How to Remove Drop-down List in Excel?

Benefits of Custom Lists in Excel

Custom lists in Excel provide several benefits, including:

• Efficiency in Data Entry: You can quickly fill a column or row by creating predefined sequences or patterns, such as frequently used employee names or product codes or department names. Excel will automatically suggest and fill in subsequent entries based on the custom list, saving you time and reducing errors.
• Consistency and Accuracy: By eliminating the need for manual typing, custom lists reduce the likelihood of typographical errors, enhancing data accuracy and consistency.
• Quick Sorting and Filtering: Excel arranges the data based on the specified sequence when you sort data using a custom list. For example, if you have a custom list for the order of sales regions, sorting it will arrange the data accordingly. 

Some Use Cases of Custom Lists

The following are some specific use cases where custom lists in Excel can be helpful:

1. If you frequently need to enter the names of employees or teams into a worksheet, you can simplify the process by creating a custom list containing all the names. 
2. If you deal with product inventory or sales data, creating a customized list of product codes or SKUs can enhance the accuracy of data entry and expedite the process. 
3. Creating a customized list can be useful for geographical data like countries, regions, or cities.
4. You can create a custom list of priority levels to prioritize tasks or issues, such as high, medium, and low. This list simplifies sorting, filtering, and analyzing tasks based on their level of importance.

This tutorial showed two techniques for creating custom lists in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Filter by Color in Excel?
• How to Combine First and Last Name in Excel?