How To Create a Drop-Down List In Excel?


Excel provides the drop-down list in order to provide multiple options to select one. The drop-down list can be created in different ways and options. In this tutorial, we will learn how to create a drop-down list for excel.

What Is Drop-down List?

The drop-down list provides multiple options where these options are selected from a list that works as a drop-down and select. The selected value or item will be set as the value for the drop-down list.

Create Drop Down List

As the drop-down list consist of multiple options or items of list we need some data source to provides as options in the drop-down list. So we will create a list which is countries in this example which contains, Turkey, UK, Germany, USA, China .We will also put a header for this list which is Items . But using the header is optional.

In order to use this list as the drop-down list source, we should convert them into a table. We can convert the list by selecting them and using CTRL+T shortcut. We are free to select the header Items but if we select it for conversion then we should express that the table has a header in the table creation dialog box.

Convert Items To Table

We can see the following screen which will convert given range items into a table. We will check the My table has headers checkbox if we selected the Item header cell.

Create Table From the Items

We can see below that the items are listed inside the table as a table structure where items are colored accordingly. Also the header is colored more dark. We will use the following table in order to create the drop-down list.

Drop-down List Source Table

Now we will start the drop-down list creation process. First we will select a cell where the drop-down list will be shown. In this case we select the D2 cell. Then we will open Data Validation from Data Tools menu.

Open Drop-down list Creation Dialog Box

Be low we can see the Data Validation screen where we will make some configuration. We will set the Allow: settings as List from the Settings tab. Ignore Blank will ignore blank cells of the table which is useful in most of the cases. In-cell drop-down will create the drop-down action and it must be selected. Source provides the range of the items we want to put into the drop-down list.

Configure Drop-down List Options

We can also provides some help information or message about the cell for better user experience. In the Input Message tab we can set some message about the drop down list. We will check the Show input message when cell is selected checkbox and put the Title and Input message values like below.

Drop-down List Configuration

When everything is completed we will click OK which will create our drop-down list in the cell D2. We will see the information title and message when we select the given drop-down list cell which is D2. We will also see a square button that will open the drop-down list and items to select.

Drop-down List with Title and Message

We will see the following drop-down menu when we click it. All items are list which are Turkey, UK, Germany, USA, China

Drop-down List Show

When we select an option which is Germany, in this case, the value will be shown in the drop-down list cell. This can be very useful to select and read values for different multi-choice applications.

Drop-down List Select Menu

Create Drop Down List By Entering Data Manually

Another way to provides options to the Drop-down list is providing the data manually. Generally the drop-down options are provided as a table from other cells contents of the Excel. Alternatively we can provide the drop-down list items from the Data Validation dialog box. The process is the same with the creating drop-down list which is described previously. When we arrive the Data Validation dialog box in the source tab we will provide the data manually by separating them with comma. Comma is the separator to delimit drop-down list items.

Drop-down List with Manual Data

And all other settings and configurations are the same like table data as source.

LEARN MORE  How To Control Python For Loop with Break Statement?

Create Drop Down List By Using Excel Formulas/Functions

Up to now, we have provides the drop-down list items like a table or manually. There is another alternative way to provide items for the drop-down list. We can provide the drop-down list items dynamically. We can use some functions in order to create drop-down list items.

Allow Not Existing Entries

By default a drop-down list provides the given items and only one of these items can be selected. No other value can be selected or entered into the drop-down list cell by default. If we need to accept optional values which are not presented in a drop-down list we should disable Show error alert after invalid data is entered checkbox and feature. This features is located in the Error alert tab of the Data validation dialog box like below. It can be change during a drop-down list creation or after the creation by editing the drop-down list properties.

Allow Not Existing Entries

Below we can see that even Spain is not defined as an item of the drop-down list we have typed it into the cell manually.

Add Manual Data To Drop-Down List

Add/Remove Items

New items can be added into the drop-down list easily in different ways. One of the easiest ways is appending the new item into the end of the current source table of the drop-down list. We will add Spain in this example down China which an item of the table. The table will be automatically extended and Spain is added as an item in the drop-down list.

Add/Remove Items

Remove Drop-down List

We can remove or delete a drop down list from the Data Validation dialog box. First we will select the cell of the drop-down list. Then we will open the data validation dialog box.

Then in the settings tab we will click to the Clear All button which will clear the source. Alternative we can clear the source with the backspace or delete.

Clear Source Content

After clearing the source content it will look like below as we can see that the Allow setting is any value and data setting is disabled. The last step is clicking OK to apply this setting.

Remove Drop-down List

Dependent/Nested/Multi Level Drop-down List

Drop-down list can be created in a dependent or nested or multilevel way. All of the same which is simply selected item of the first drop-down list will change the second drop-down item list. This is a hierarchical way. The fist drop-down list is also called parent drop-down list and the second drop-down list is called child drop-down list. Actually dependent drop-down list can be implemented for more than 2 levels like 5 or 10 levels where every child depends its parent selection.

LEARN MORE  How To Convert List To String in Python?

Leave a Comment