How to Make a Drop Down List In Excel

Drop down list is a tool that can help you to force users into selecting a specific value from a predefined set of values. It is always good to have drop down lists for accepting user inputs.
 
This approach is better because it validates each input from user against a predefined set of values and hence users can only input valid and excepted values.

Excel also has a feature that allows you to create drop down lists. And today I will guide you on making a drop down list in Excel.

How to make a drop down list in Excel:

  • First of all open your excel sheet and select the cell on which you wish to create a drop down.

Data Validation Option in Excel

  • Next, navigate to ‘Data’ tab in the Excel Ribbon and then click the ‘Data Validation’ button.

How to Make a Drop Down List In Excel

  • Now, a ‘Data Validation’ window will open. In the ‘Allow’ dropdown, select the “List” option. And in the ‘Source’ textbox enter the values that you wish to have in your dropdown, these values should be separated by a comma (,).  Also, please make sure that the checkbox ‘In-cell dropdown’ should be checked.

Drop Down List Animation

  • After this hit the ‘OK’ button and your dropdown is ready to be used.

Few Important things about Excel Drop downs:

1. You cannot delete a dropdown by pressing delete key. It can only be deleted after selecting the dropdown, click ‘Data Validation’ option and in the ‘Data Validation’ window press “Clear All” button.

2. The width of your dropdown list is not set automatically. You should manually adjust it according to the longest value in your dropdown.

3. Excel dropdowns can have a maximum of 32,767 values.

A better type of data source for dropdowns:

As you have already seen that you can enter the values of a dropdown directly in the ‘Source’ textbox. This is fine if you are planning to create only small list boxes, but you have a large list of values that you want to populate in your drop down list then there is a better method.

You can enter the values that you want to see in your dropdown as a table and then use the range of this table in ‘Source’ column. Below is a step by step procedure to do this:

  • First with an Excel sheet opened, enter the values that you are planning to populate in your dropdown in a table.
  • Next, select the cell on which you are planning to create a drop down.
  • Now, navigate to Data > Data Validation.

Dropdown list with a table as source

  • In the ‘Data Validation’ window select “List” from the ‘Allow’ dropdown, check the ‘In-cell dropdown’ checkbox and in the ‘source’ textbox enter your table range (that we created in step 1). Hit “OK” and your dropdown is created.

Cosmetic Tip: If you are using a drop down with a table of values as source, then try to have this table on a separate sheet which you can hide later. This makes your dropdowns look more natural and no one can meddle with your dropdown values.

Bonus Tip: Instead of directly entering the source range in ‘source’ textbox you can also name your range and then use the same in ‘source’ textbox.

So, this was all about making a drop down list in Excel.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.
You can find Ankit on FaceBook or Google Plus

Privacy Policy | Disclaimer | About