Create a drop down list from a list of items

A drop-down list is a very efficient way to limit the options in forms, survey or polls. Drop down lists in Excel can be implemented using the data validation feature. Data validation is a handy tool in Excel that allows you to control what can be entered in your worksheet. This is very helpful when auditing Excel files. By using drop-down lists you can prevent unexpected values in your data. In the long run, this helps a lot in having a clean and tidy data set. In this tutorial, you’ll learn how to create a drop-down list from a list of items in Excel.

Create a drop-down list from a list of items

In this example, we will use the employee information worksheet. It contains information about the new employees of Bagpiper INC. The worksheet contains the employee ID, Name and Salary already updated. You’ll need to add the department for the new recruits.

The new recruits will be working in 4 departments which are already listed in A2:A5. You need to add a drop down in the F column for each employee to assign their respective departments. You will make the drop down in the cells using Data Validation.

Click cell G3, click Data > Data Validation > set the drop down in “Allow” to “List” > set the source to A2:A5. This will add a drop-down list to cell G3 containing the four departments in C3:C6. Now, copy the drop-down list to the cells below dragging the little “+” icon on the bottom right of cell G3.

You can select the appropriate department based on the reference in cells B3:B6. After you finish assigning all the departments, the data would look like this:

Change the value according to the drop-down list selection

You can also create a dependent drop down list based on the selections from a previous drop-down list. Let’s consider the inventory records data set. In this example, you’ll create a dependent drop-down list based on the product items based on the categories.

The inventory dataset has records of four categories of products. Each of the categories contains several products. The data also contains the unit and price for each product.

At first, you need to add the category list in cells I5:I11. To do this selecting any cell in the range B5:E11 you will need to click Data > Advanced >Select “Copy to Another Location”. Assign the range “$B$5:$B$11” to both the “List range” and “Criteria range”. Set the source to G5. Click the “Unique Records Only” checkbox and hit OK. This will extract the unique values from categories in cells G5:G7.

Now to add the drop downs in cells I5:I11, you’ll need to select cell I5, click Data > Data Validation > set the drop down in “Allow” to “List” > set the source to G5:G7. This will add a drop-down list to cell I5 containing the categories in G5:G7. Now, copy the drop-down list to the cells below dragging the little “+” icon on the bottom right of cell I5.

Now, on cells J5:J11 you’ll add the dropdowns that are dependent on the entries in cells I5:I11. Selecting J5, click Data > Data Validation > set the drop down in “Allow” to “List” > assign the formula “=OFFSET($B$5,MATCH($I5,$B$5:$B$11,0)-1,1,COUNTIF($B$5:$B$11,$I5))” in the source text box.

This formula extracts only the items that belong to the categories in the corresponding I cell. For example, selecting “Dairy” in cell I10 will extract the values “Milk”, “Cheddar Cheese” and “Margarine” in cell J10. Now you need to copy the drop-down list to the cells below dragging the little “+” icon on the bottom right of cell J5. When you fill it out column J would look something like this:

With the help of Data Validation, we can easily create simple drop downs as well as complex drop downs that are dependent on other values. We can also restrict the values with drop downs. This prevents the user from entering incorrect values which helps a lot to audit the file as well as maintaining a clean and tidy data set.

Above is a simple example of creating a data validation list. If you are working with a more complex data set and need our expert help on creating and/or troubleshooting your drop-down list, save your time by using our 24/7 Excel Chat help service. You will be connected to our experts in a few seconds, share your file and get your problem solved in less than 20 minutes. The first problem is free.