Excel drop down list are used when we want to have a pre-defined set of values to choose from while entering data in any cell.

It’s used for data entry in excel as well as used in various excel dashboards. They help ease the process of data entry & avoiding errors.

In this excel tutorial, we’ll learn how to create a drop down list in excel and how in excel drop down list actually works.

Create an excel Drop Down List

Let’s see how to create drop down menu in excel.

Method 1: Create Drop Down From List

We have a list of data already with us as below:

1. Select the cell where we want to create the drop down list.
   
   
2. Go to Data tab. In data tools select data validation
   
   
   
3. A new dialog box will appear. Within settings select List as the Validation criteria for the drop down list in excel.
   
   
   
4. The source field will appear. In that, enter =$A$2:$A$7 or click on source field and select the cells containing the data when we want to use in drop down.
   Please Note: Check the In-cell dropdown Otherwise you’ll not see the drop down and you’ll have to manually enter data. By default, this option is checked.
   
   
   
5. Click OK

This is how we can create a drop down list in excel. If you want to have the drop down list for multiple cells then just simply copy the cell and paste it in another location. You can drag as well. Let’s look at other methods.

Method 2: Creating Drop Down List Excel By Entering Data Manually

In the above method, we used data from a cell range to create a list. We can do the same with just entering the data manually in the source field.

Let’s see a different type of example for drop down list:

1. Select the cell where we want to create the drop down list.
   
   
2. Go to Data tab. In data tools select Data Validation.
   
   
3. Select list as the validation criteria.
   
   
4. In source field, type the values instead of selecting a range. All the items should be separated by a comma.
   
   
   
5. Click OK.
   

This will make dropdown in excel same as Method 1. Do note that the drop down list is case sensitive. So if someone enters ‘north instead of ‘North’, excel will throw an error.

Edit Drop Down List in Excel

Now let’s say you have already created a drop down, but due to reason now you wish to add more items or maybe just edit lists in excel. It’s fairly easy to do the same, let’s see how excel drop down list works:

1. To add an item in the list, just go the range of cells which the drop down is refereeing from. Select anyone item.
   
   
   
2. Right click and select Insert to add a new row.
   
   
   
3. A new row is added, now just add another item in the cell. Voila you have added a new item. You also go back to the cells and just edit their names to change the list in drop down.
   

Creating Excel Dynamic Drop Down List

We can even create a dynamic drop down list in excel. So when we enter new values, it’ll add the same in the drop down list.

We’ll be using the OFFSET function to do the same. Do learn about OFFSET function to fully understand how it works.

1. Select the cell where we want the drop down list to appear.
   
   
2. Go to Data tab. Select Data validation.
   
   
3. In the dialog box, select List as the validation criteria.
   
   
4. In the source field, input the formula:
   =OFFSET($A$2,0,0,COUNTA($A$2:$A$100),1)
   
   
   
5. Click OK
   

Here in this formula, COUNTIF counts the non-blank cells in the range G2:G100. Which then returns the value to OFFSET function, which then only includes the values in non-blank cells thus ignoring the empty cells in the range.
Now that you have learned how to create drop down list in excel which is dynamic in nature let’s look at another type of drop down.

Allowing Non-List Entries in Drop Down List

Let’s say we want to create a drop down where not only you’ll get an option to choose but you can also enter a different value.

1. First create a drop down list. Now type in a different value, you’ll see an error.
   
   
   
2. Now go to Data tab. Select Data Validation.
   
   
   
3. Go to Error Alert. Uncheck, ‘Show error alert after invalid data is entered’.
   
   
   
4. Click Ok

Now try again, you can now enter different values which are not in the excel drop down list.

Excel Drop Down List Remove

Now let’s see how can we remove a drop-down list in excel. Go through the following steps:

1. Select the cell with the drop down list.
   
   
2. Go to Data tab. Select Data validation.
   
   
   
3. Click Clear All. If you want to delete all the drop down lists with same settings, then check ‘Apply these changes to all other cells with same settings.’
   
   
   
4. Click OK

This is how you can delete drop down in excel.

Creating Excel Dependent Drop Down List

A dependent drop down list is one where the values of one drop down is dependent on the values of another drop down. So if the value of one drop down changes, it automatically changes the options or values in another drop down.

Here’s an example:

Here both lists are dependent and when you select options in one list, it changes the value in the other excel drop down list.

1. Select the cell where you want the first or main drop down list.
   
   
2. Go to Data tab. Select Data validation.
   
   
   
3. In data validation box, within Settings select List.
   
   
   
4. In source, select the cells which should have the value of first or main drop down list.
   
   
   
5. Click OK. Main drop down list is created.
   
   
6. Now select the entire data set (A1:B6 only for this example)
   
   
   
7. Go to Formulas. Select Create from Selection. You can also use the shortcut for Create from Selection (CTRL + SHIFT + F3)
   
   
   
8. ‘Create Named for Selection’ dialog box will appear. Check Top row and uncheck rest. This will create 2 named ranges i.e. ‘Clothing’ and ‘Fashion Accessories’. Clothing named range refer to all names under Clothing and same for Fashion Accessories named range.
   
   
   
9. Click OK.
   
   
10. Now select the second cell, where we want the second or the dependent drop down list in excel. Go to Data tab and select Data Validation.
    
    
    
11. Select List. In source field, enter the formula =INDIRECT(D3). D3 contains the main drop down list. We are using the INDIRECT function.
    
    
    
12. Click OK

Now when you select the options in main drop down box, it’ll change the results in second or other drop down box in excel.

Please Note: While creating excel dependent drop down lists, if you have to be very careful as if you change the main drop down then the value in dependent or second drop down will not change. It will be a wrong entry then. Do check out this wonderful tutorial by Debra who explains how you can clear dependent or conditional drop down lists in excel if the selection is changed anytime.

Conclusion

You will often be working with excel drop down lists one way or another. It’s better to learn and understand how to not only create but edit an excel drop down list.

I hope this article was helpful to you and explained you in depth how excel drop down list creation works, how to create an excel dynamic drop down list and much more.
If you have any questions or tips, I’d love to hear them in the comment section below.