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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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...
Dependent ComboBox Drop Down Menus
Tutorial: How to create UserForm drop-down menus that change based on what was selected in another d...
Highlight, Sort, and Group 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 allows...
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...
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course