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
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
Macro: Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a grea...
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...
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 ...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Quickly Combine a List of Values and Put a Delimiter Between Each Value in Excel
Tutorial: How to combine a list of data into one cell while putting a delimiter between each piece ...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
Tutorial: In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as...