Two ways to add a drop down list in Excel

Sometimes you have a sheet that you need other people to fill in. And, you have a column or range of cells that need to have values from a specific set of values. You don’t want to have to deal with multiple variations of “yes”, for example. You might wind up with “Yes”, “1”, “True”, “Y”, “T”, and who knows what else. You need people to stick to the menu of choices to make your analytic life much easier. The solution: add a drop down list in Excel.

You’ve seen drop down pick lists in other people’s spreadsheets and want to do the same in yours. The process is pretty straightforward. I’ll describe a couple of common ways below. The option you pick would depend on the number and length of the items to pick from. A 60-second demonstration video is included at the end of this page as well.

Option 1, directly type the values in

If you want a simple pick list this might be the way to go for you. Think of having a list such as “Yes”, “No”, “Maybe” for example. To create a list with this option do the following.

1. Select the cells you want to have the drop down list to pick values from.
2. Select Data from the Excel ribbon.
3. Select Data Validation from the Data Tools group.
4. Select Data Validation…
5. In the Validation criteria section, change the Allow drop-down box from Any Value to List.
6. In the Source text box, enter the following:
   • “Yes”, “No”, “Maybe”
7. Click OK

At this point the cells you had selected will only allow one of the values you specified in the Source field (“Yes”, “No” or “Maybe” in this example).

Option 2, set values based on a range

Option 1 above is a bit of a hassle if you have a longer list or if the values themselves are longer. In a case like this Excel makes it easy to have a list that you keep in a set of cells. These can be either on the same sheet or on another sheet. For example, imagine having the following values in cells A1 thru A10:

Amarillo
Banana
Citrus
Daffodil
Dandelion
Goldenrod
Hilighter
Post-it
Sunflower
Tulip

In order to easily make this list control the values in your pick list do this following.

1. Select the cells you want to have the drop down list to pick values from.
2. Select Data from the Excel ribbon.
3. Select Data Validation from the Data Tools group.
4. Select Data Validation…
5. In the Validation criteria section, change the Allow drop-down box from Any Value to List.
6. In the Source text box, enter the following:

   • =$A$1:$A$10

   • Alternatively, you can simply highlight the block of cells and the cell range above will automatically be put in the Source box.
7. Click OK

At this point, just as in Option 1 above, the cells you had selected will only allow one of the values you specified in the Source field (Some form of yellow in this example).

Demo

Here’s a short video example to see how easy it can be to add a drop down list to Excel.

There you go.

Here are some related posts:

How to quickly and easily detect duplicate values in an Excel list How to drag and drop columns in Excel How to pick a random value from a range of cells in Excel Return Random Value from List in Excel How to enable the Analysis ToolPak Add-in in Excel