Contextures

Home > Validation > Drop Downs > Create

Make Excel Drop Down List in Worksheet Cell

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 for data validation dropdown lists are below

list of options for drop down

What is Data Validation?

In Microsoft 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, 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 items will be stored on a spreadsheet 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 new items are added to the list, or existing items are deleted from the list.

You can use the Name Manager, as shown in the video, or use the Name Box. Both methods are described below. The Name Box method is quicker, but you can't add a comment.

Use the Name Manager

This is the method shown in the video. You can set a name, scope and comments.

  1. Select one of the cells in the list of employee names.
  2. On the Ribbon, click the Formulas tab, and in the Defined Names group, click Define Name
  3. click Define Name command

  4. In the New Name dialog box, type a one-word name for the selected range, e.g. EmpNames
  5. Leave the Scope as Workbook
  6. create a new name for the list

  7. Click in the Refers to box (On the worksheet, the cell that is currently listed in the Refers to box will be highlighted)
  8. To select all of the names in the list, point to the top of the heading cell (B1), and when a black arrow appears, click, to select all the names, without the heading cell. (Be sure to click when the arrow is in cell B1, not in the Column B heading button)
  9. click in table hading to select cells

  10. The table name and column name will appear in the Refers to box: =Table1[Employees]
  11. Click the OK button, to complete the name.
  12. create a new name for the list

Use the Name Box

This is a quick way to name a range of cells.

  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 selected cell range, e.g. EmpNames, and then press the Enter key, to complete the name.
  • NOTE: After you press Enter, the name will disappear, and the Table name will appear in the name box.

    list of options for drop down

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

  • Select the cells in which you want the drop down list

data validation command

  • On the Ribbon's Data tab, in the Data Tools group, click the Data Validation button.

data validation command in data tools section of Excel ribbon

  • When the Data Validation dialog box open, go to the Settings tab
  • First, click in the Allow box
  • Then, from the Allow drop down list, select List
    • Tip: Use your mouse to select an item in the list, or use the Down arrow on your keyboard

data validation on ribbon

  • Click in the Source box, and type an equal sign, and the list name, for example:
    • =EmpNames
  • OR, to use a keyboard shortcut, press the F3 key, to see a list of names
  • Then, click on a name, to select it, and click OK

data validation on ribbon

  • Optional: Go to the Input Message tab, and create a custom message with tips for entering a valid item
  • Optional: Go to the Error Alert tab, and create a custom error message, to help people correct any invalid entries
  • Click OK to close the Data Validation dialog box.
  • Click on one of the cells, and click the drop down arrow

data validation on ribbon

  • 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, and the written instructions are below the video, along with the full video transcript.

Video Transcript

If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript

Excel Drop Downs From List on Different Sheet

In this workbook, there's a sheet where people will enter order information. They'll put in a date, and then a product, and the quantity sold.

To make it easier for people, in the Product column, instead of them typing a product name, which could result in errors or invalid products, we're going to create a drop-down list, so they just have to select a product instead of typing it.

List on Different Sheet

On a different worksheet, on the Products sheet here, there's a list of products and we'll use that to create the drop-down list.

Because this is on a different worksheet, we're going to have to name this list first before we can use it for the drop downs.

Name the List

  • I'm going to select all the cells that have product names, and then click in the name box
  • And I'm going to type a one-word name for this. I'm going to call this ProductList
  • And then I'll press the Enter key to finish naming that range.
  • And you can see the name up here now.

If I select a different cell, and select ProductList, it goes to that list right away. So this is a name in the workbook now

Create Drop Down Lists

And I can go back to the Orders sheet

  • And I'll select all the cells where I'd like that drop-down to appear.
  • Then on the Data tab of the ribbon, in the Data Tools section, I'll click, at the top of this Data Validation command, and that opens this window.
  • Under Settings, for Allow, I would like a list.
  • As the Source of this list, I could type an equal sign and the name of the list, but an easier way is on your keyboard, press the F3 key, and that opens up a Paste Name window.
  • I'll click on the list I want to use.
  • Click OK and you can see it now, just as if we had typed it.
  • Click OK

And now each of these cells has a drop-down list.

Arrow in Cell

The arrow only appears when you click on the cell. You can only have one drop-down visible at any time.

You can't make these appear all the time but you can then click that arrow and select one of the products and it'll automatically then appear in the cell.

Excel Drop Downs From List on Different Sheet

If the list of items for your Excel drop-down list are NOT in a named Excel table, you can use the following steps to create the data validation drop downs.

Create a Named Range

The first step is to name the range of cells where your list of items is located.

Here’s the quickest way to name the list of items:

  • Select the items
  • Click in the Name Box
  • Type a one-word name, and press Enter.

one-word name in Name box

Create the Drop Down Lists

After you’ve named the source list, you can add the drop down lists, by following these instructions:

  • Select all the cells where you want the drop downs.
  • On the Excel Ribbon, click the Data tab, then click the Data Validation command.

Data Validation command

  • Next, in the Data Validation dialog box, under Allow, select List from the drop-down menu.
  • Then, click in the Source box, and press F3 on your keyboard, to open the Paste Name box. (Quicker than typing the name)
  • Click on the Name that you want to use in the drop down list, then click OK

paste name dialog box

  • An equal sign and the name will appear in the Source box.
  • Click OK, to create the drop down lists.

name appears in data validation dialog box

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:

Yes,No,Maybe

Notes:

  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

When you add data validation to a cell, the Error Alert feature is automatically turned on. It stops the users from entering invalid data in the cell.

In some worksheets, where there is a drop-down list of valid items, you might want to allow other items to be entered too. For example:

  • In a worksheet order form, show a short drop-down list of the top product names, so it's easy to enter those items
  • But, allow people to type new product names in the cell, when needed

Turn Off Error Alert

The quickest way to allow users to type items that are not in the list, is to change the data validation error alert setting. To change that setting, follow these steps:

  1. Select the worksheet cells where you want to turn off Error Alert
  2. On the Ribbon, click the Data tab, and click Data Validation
  3. In the Data Validation Settings window, click on the Error Alert tab
  4. Remove the check mark from Show error alert after invalid data is entered

turn off error alert

Other Data Validation Error Alert Options

There are other Error Alert options too, if you don't want to turn that setting off completely. For details on setting up the error alerts, and other data validation messages, go to the Show Data Validation Messages page.

For example, you could show a warning message, and ask if they want to continue with the entry.

Error Alert Style - warning

Or, just show an information message, that reminds them the item was not in the drop-down list of valid entries.

Error Alert Style - information

FAQ: How do I make drop down list in Excel?

  1. Make list of drop down items on worksheet
  2. Give list a Name
  3. Add data validation to cell, and allow List, using your named list

drop down list in Excel

Get 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

More Tutorials

Create Dependent Drop Down Lists

Data Validation Index Page

Change Product Name to Code

Dependent Drop Down Lists Video

Drop Down List Types

Hide Used Items in Drop Down

List from Another Workbook

Data Validation Criteria Examples

Data Validation Date Rules

Data Validation Tips

Data Validation With Combo Box

Last updated: July 24, 2023 3:38 PM