Excel Drop Down Lists

Create a drop down list of items in a cell, with Excel's Data Validation feature. This will make data entry easier, and reduce input errors and typing mistakes. Video tutorials and step by step instructions are below

What is Data Validation?

In Excel, the data validation feature helps you control what can be entered in your worksheet. For example, you can:

  • create a drop down list of items in a cell
  • restrict entries, such as a date range or whole numbers only
  • create custom rules for what can be entered

In this tutorial, you'll see how to create a drop down list of choices in a cell, like the list of employee names shown below.

list of options for drop down

Create a Drop Down List in a Cell

To create a drop down list in Excel 2010, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below.

Watch the steps in this short video, and the written instructions are below the video

How to Create a Drop Down List

With Data Validation, you can create a dropdown list of options in a cell. There are 3 easy steps:

1. Create a Table of Items OR Create a List

2. Name the List

3. Create the Drop Down

Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab.

1. Create a Table of Items

The easiest way to create and maintain the list of options, is to type them on a worksheet. You can do this on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists.

NOTE: A data validation list can show up to show 32,767 items from a list on the worksheet.

  1. Type a heading for the list -- Employees in this example
  2. Immediately below the heading cell, in single column, type the entries you want to see in the drop down list. Do not leave blank cells between the entries.

    list of options for drop down

  3. Select a cell in the list, and on the Ribbon's Insert tab, click Table

    list of options for drop down

  4. Add a check mark in "My table has Headers" and click OK

    list of options for drop down

The table is now a Named Excel Table.

2. Name the List

Next, you will create a named range that does not include the heading cell in the table. This named range will be dynamic -- it will adjust automatically if items are added to or deleted from the list.

  1. Click at the top of the heading cell, to select all the cells in the list (the heading will not be selected).

    list of options for drop down

  2. Click in the Name box, to the left of the formula bar
  3. Type a one-word name for the list, e.g. EmpNames.

    list of options for drop down

  4. Press the Enter key, to complete the naming process. (After you press Enter, the name will disappear, and the Table name will appear in the name box.)

3. Create the Drop Down List

Now that you have created a named range, you can use that to create a drop down list in one or more cells

  1. Select the cells in which you want the drop down list
  2. data validation command

  3. On the Ribbon's Data tab, click Data Validation.

    data validation command

  4. From the Allow drop-down list, choose List

    data validation on ribbon

  5. Click in the Source box, and type an equal sign, and the list name, for example:
    OR, press the F3 key, to see a list of names, click on a name, and click OK

    data validation on ribbon

  6. Click OK to close the Data Validation dialog box.
  7. Click on one of the cells, and click the drop down arrow

    data validation on ribbon

  8. Click on an item in the drop down list, to enter it into the cell.

data validation on ribbon

List on Different Sheet

If you prefer not to create a named Excel table, you can create a named range, and use that as the source for a drop down list. The drop down lists can be on the same sheet as the source list, or on a different sheet. Watch this video to see the steps.

Use a Delimited List

Instead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas. For example:



  1. This method of Data Validation is case sensitive -- if a user types YES, an error alert will be displayed.
  2. Space characters can be typed before or after the valid items, and no error message is displayed, e.g. " Yes   " would be allowed. (Thanks to Peter for this tip.)

create delimited list

Allow Entries Not in Drop Down List

To allow users to also type items that are not in the list, remove the check mark to turn off the Error Alert.

turn error alert off

Download the Sample File

You can download the sample Drop Down Lists file here: Drop Down List Workbook. The zipped file is in xlsx format, and does not contain any macros.go to top

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.


Search Contextures Sites

More Tutorials

Create Dependent Drop Down Lists

Hide Used Items in Drop Down

List from Another Workbook

Data Validation Criteria Examples

Data Validation Tips

Data Validation With Combo Box

Create a Drop Down Excel 2003

Custom Search

30 Excel Functions in 30 Days





excel tools




Excel Data Entry Popup List




Update Your Excel Skills


Excel Data Entry Popup List



 Get Excel News

Last updated: October 20, 2016 1:05 PM