Make a Drop Down List Menu in a Cell in Excel

Add to Favorites

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:

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:

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

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

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:

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

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.

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:

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Highlight and Sort the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allow...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a...
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...
Capitalize the First Letter of Every Word in a Cell
Macro: This macro will make the text of any selected cell in excel proper case. This means th...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Highlight Every Other Row in a Selection in Excel - Table Formatting
Macro: This free Excel macro will highlight every other row in a selection of cells with a co...