How to create a dropdown list in Excel

Table of contents:

Steps
Advice

How to create a dropdown list in Excel

A drop-down list in Microsoft Excel can improve data entry efficiency, but at the same time restrict data entry to a specific set of items or to the data in the drop-down list.

Steps

Method 1 of 2: Excel 2013

Create a Drop Down List in Excel Step 1

Step 1. Open the Excel file in which you want to create the dropdown list

Create a Drop Down List in Excel Step 2

Step 2. Select blank or create a new sheet

Create a Drop Down List in Excel Step 3

Step 3. Enter the list of items to be displayed in the drop-down list

Each item is entered in a separate cell on each new row. For example, if you create a drop-down list with sports names, enter baseball in A1, basketball in A2, football in A3, and so on.

Create a Drop Down List in Excel Step 4

Step 4. Select the range of cells that contains all the entered elements

Create a Drop Down List in Excel Step 5

Step 5. Go to the "Insert" tab

Select "Name" and then select "Set".

Create a Drop Down List in Excel Step 6

Step 6. Enter a name for the items in the Name field and click OK

This name is for reference only and will not appear in the table.

Create a Drop Down List in Excel Step 7

Step 7. Click on the cell where you want to create the dropdown list

Create a Drop Down List in Excel Step 8

Step 8. Go to the Data tab and select Data Validation from the Data Tools group

The "Validate Input Values" window opens.

Create a Drop Down List in Excel Step 9

Step 9. Go to the "Options" tab

Select "List" from the "Data Type" drop-down menu.

Create a Drop Down List in Excel Step 10

Step 10. In the line "Source" enter the equal sign and the name of your drop-down list

For example, if your dropdown is called Sports, enter = Sports.

Create a Drop Down List in Excel Step 11

Step 11. Check the box next to "List of acceptable values"

Create a Drop Down List in Excel Step 12

Step 12. Check the box next to "Ignore empty cells" if you want users to be able to select zero items from the drop-down list

Create a Drop Down List in Excel Step 13

Step 13. Click on the "Error Message" tab

Create a Drop Down List in Excel Step 14

Step 14. Check the box next to "Display error message"

This option prevents users from entering incorrect data.

Create a Drop Down List in Excel Step 15

Step 15. Click OK

The dropdown list appears in the spreadsheet.

Method 2 of 2: Excel 2010, 2007, 2003

Create a Drop Down List in Excel Step 16

Step 1. Open the Excel file in which you want to create the dropdown list

Create a Drop Down List in Excel Step 17

Step 2. Select blank or create a new sheet

Create a Drop Down List in Excel Step 18

Step 3. Enter the list of items to be displayed in the drop-down list

Each item is entered in a separate cell on each new row. For example, if you create a drop-down list with fruit names, enter "apple" in cell A1, "banana" in cell A2, "blueberries" in cell A3, and so on.

Create a Drop Down List in Excel Step 19

Step 4. Select the range of cells that contains all the entered elements

Create a Drop Down List in Excel Step 20

Step 5. Click in the Name field to the left of the formula bar

Create a Drop Down List in Excel Step 21

Step 6. In the "Name" field, enter a name for the drop-down list describing the entered items, and then press Enter

This name is for reference only and will not appear in the table.

Create a Drop Down List in Excel Step 22

Step 7. Click on the cell where you want to create the dropdown list

Create a Drop Down List in Excel Step 23

Step 8. Go to the Data tab and select Data Validation from the Data Tools group

The "Validate Input Values" window opens.

Create a Drop Down List in Excel Step 24

Step 9. Go to the "Options" tab

Create a Drop Down List in Excel Step 25

Step 10. Select "List" from the "Data Type" dropdown menu

Create a Drop Down List in Excel Step 26

Step 11. In the line "Source" enter an equal sign and the name of your drop-down list

For example, if your dropdown is called "Fruit", enter "= Fruit".

Create a Drop Down List in Excel Step 27

Step 12. Check the box next to "List of acceptable values"

Create a Drop Down List in Excel Step 28

Step 13. Check the box next to "Ignore blank cells" if you want users to be able to select zero items from the drop-down list

Create a Drop Down List in Excel Step 29

Step 14. Click the Error Message tab

Create a Drop Down List in Excel Step 30

Step 15. Check the box next to "Display error message"

This option prevents users from entering incorrect data.

Create a Drop Down List in Excel Step 31

Step 16. Click OK

The dropdown list appears in the spreadsheet.

Advice

Enter the items in the order you want them to appear in the dropdown list. For example, enter items alphabetically to make the list more user-friendly.
After you finish creating the dropdown, open it to ensure that all of the items you entered are present. In some cases, you will need to expand the cell in order to display all the elements correctly.

Popular by topic