Make a Drop Down List Menu in a Cell in Excel

Add to Favorites
Author: | Edits: don

I'll show you how to make a drop down list menu in a cell in Excel. This allows you to have a list appear when you click a cell and you can then select an item from the list to have it appear in the cell.

This is a great way to make it easier for a user to enter a correct value in a cell.

Here is what we want to have:

0fdc7cd372c1c49eddfdab4d04d99432.png

There are two simple ways to create this drop down list and both methods use what is called Data Validation.

Let's start with how to add a list like the one above.

First, create a list of items that you want to appear in a drop down menu or list.  We have this in column A.

Then select the cell where you want the drop down menu to appear and go to the Data tab and click the Data Validation button:

d76f3a224ce0f6fd9aa0799b39a26728.png

In the window that appears, go to the Settings tab and then select List from the Allow field.

55e890fa9dddbb6cde92e0d1fa7a3134.png

In the screen that appears, make sure that the option In-cell dropdown is checked.

e9f73925cc3565b3dc63e088257b6fa7.png

Then we just need to tell the Data Validation what our list should be.  Click in the Source field and then type the range reference where the list is located.  You can also simply click in the Source field and then select the range where the list is like this:

dc677e45c8a2f2b80ecfe691b1af4bf1.jpg

If you click the button on the right of the Source field, it makes it easier to select the list from the worksheet:

4148f74c9a0719cafc29dc90620a91e5.jpg

Another Way to Add the Drop Down List Menu

You can also add a list by just typing the values into the Source field in the Data Validation window.  In this case you do not need a list in the worksheet.

4da05cd1053dc946454560ecff95fbf1.png

In the above example I input text and numbers.  You do not need to use quotation marks, you simply need to make sure the values in the list are separated with a comma.

This gives us this list in the worksheet:

91ff0feefa2fd5682c12cbee6856484d.png

Things to Note

Note that once you create this list, you will, by default, be preventing a user from entering a value that is not contained within the drop down list.  There are ways around this, but this is the default behavior of Data Validation in Excel.

Make sure to download the workbook that goes with this tutorial so you can see these examples in action.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File 1, Excel File 2