How to create a drop-down list in Excel
MS Excel or Microsoft Excel is currently the most powerful spreadsheet software with various versions with distinctive features and works in offline and online modes. It enables users to record large amounts of data within cells in different worksheets of a workbook. When dealing with massive data sets in Excel, we always focus on simplifying our work, reducing time, and maintaining the accuracy of the data. A drop-down list is one such element in Excel that comes in handy for meeting our needs, saving time in inputting information while eliminating the possibility of errors.
In this tutorial, we discuss the various step-by-step methods to create a drop-down list in an Excel worksheet. Before we move on to the methods, let us introduce the drop-down feature/tool in Excel.
What is Drop-Down List in Excel?
Excel's drop-down list refers to a pre-defined list of various items that enable users to select any desired item as input data quickly. Drop-down lists prevent the insertion of data other than the ones in the list. Thus, it restricts the typing of manual data entries, reducing the chances of misspellings, incorrect data input, and the occurrence of garbage values in the cell.
Drop-down lists are commonly used on many websites or applications that accept data from users. Similarly, we use them in excel sheets so that users can fill in form details or provide/select other data types. Also, the drop-down lists are user-friendly, easy to use, and attractive.
For example, the following sheet shows a simple drop-down list that only accepts user input as 'yes' and 'no'. Users do not have the option to type anything but only choose between these two listed options. Using a drop-down list ensures that the input is correct and to the point.
Steps to create a drop-down list in Excel
There are several methods in Excel to help us create drop-down lists in our worksheets. However, the most common method to insert or create drop-down lists involves using Excel's 'Data Validation' tool/feature. Other methods include using a 'Form Control' combo box and an 'ActiveX Control' combo box. But, they are comparatively complex.
Let us now discuss the creation of different types of drop-down lists using the 'Data Validation' tool:
Creating a Static Drop-Down List
When we create a static drop-down list in Excel, our drop-down list does not update based on the newly added item at the end of the given range. This means no new entries will be added to the drop-down list created, even if we include them in the source data range. Lists and their items will remain static or fixed until we edit the entire drop-down list via Data Validation rules. However, we can add items in the static drop-down list by inserting the item (s) in the middle of the range.
Consider the following tables as an example data set where we have some names of cities in column A, and the next column B contains the number of vacancies available in the respective cities. Suppose we want to create a drop-down list of given cities in cell E3, as shown below:
To create a static drop-down list like in the above image, we need to perform the following steps:
Creating a Dynamic Drop-Down List
Unlike the static drop-down list, a dynamic drop-down list extends the possibility of adding or inserting new items based on the changes in the source range. This means if we want to add new items in our dynamically inserted drop-down list, we can simply add the individual item (s) in our source table.
For example, let's retake the previous example sheet, where we have several names of the cities in cells from A2 to A11. If we insert two new cities in the cells below (A12 and A13), they will not automatically reflect in our static drop-down list inserted in cell E3.
However, when we have a dynamic drop-down list, the items get updated as soon as they are inserted in the source range or table.
Therefore, we need to create a dynamic drop-down list in a cell E3 by following the below steps:
Adding Items to created Drop-Down List
We can add new items in a created drop-down list, whether static or dynamic. However, there are some differences. In a static drop-down list, we can only insert a new item (s) by adding a new value(s) in the middle of the source range or table. If we add it at the end of the source range, it will not reflect in the drop-down list. However, a dynamic drop-down list gets updated based on given values in the source range, either in the middle or at the end.
For Static Drop-Down Lists
As stated before, we can only insert a new item somewhere in the middle of the source range when working with a static drop-down list. This is because the range selection in our drop-down list is limited by the first and last selected cells. However, when we insert an item in the middle of the range, Excel dynamically updates the range's selection in the data validation rules. Further, it expands the range according to the number of cells we add in the middle of the source range.
We can perform the below steps to insert one or more new items in our static drop-down list:
Note: To insert an item at the end of the static drop-down list, we must go to Data Validation settings and enter a new range in the Source box.
For Dynamic Drop-Down Lists
When dealing with the dynamic drop-down lists, we can follow the same procedure as discussed above for the static drop-down list and insert one or more new items in the middle of the list. Additionally, a dynamic drop-down list enables us to directly enter/ type an item at the end of the source range. As soon as we enter the item name at the end, it immediately reflects in the drop-down list.
Therefore, it is always recommended to use a dynamic drop-down list when there is a need for adding or inserting new item (s) in the future.
Deleting Items from created Drop-Down List
To delete any item from the drop-down list, we can simply delete the corresponding cell or row, and it works for both static and dynamic drop-down lists.
Dependent Drop-Down Lists
Sometimes, we may need to insert more than one drop-down list in our Excel sheet where one drop-down list depends on the selected entry on another one. We get different items in the second drop-down list based on the entry selected in the first drop-down list for each selected item in the first drop-down. It is called the dependent drop-down list or conditional drop-down list.
For example, suppose we have two drop-down lists in our sheet where the first drop-down contains the names of some countries while the second drop-down list displays the names of cities based on the country's name selected in the first drop-down list.
If there is a chain of dependent drop-down lists controls, it is called cascading drop-down list. In that case, each drop-down list is dependent on the previous (or parent) drop-down list or selected entry.
We need to follow the below steps to create a dependent drop-down list in Excel:
In this example, the conditional drop-down list (created in cell F3) refers to =INDIRECT(E3). When we select the 'Country' in cell E3, the drop-down list in cell F3 refers to the named range for the listed countries via the INDIRECT function. So, the corresponding function lists all the items (cities) in that specific category.
Important Points to Remember