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.
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.
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.
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.
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.
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.
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.
We will see the following drop-down menu when we click it. All items are list which are Turkey, UK, Germany, USA, China
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.
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.
And all other settings and configurations are the same like table data as source.
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.
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/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.
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.
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.
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.