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
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.
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
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:
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.
Type a heading for the list -- Employees in this example
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.
Select a cell in the list, and on the Ribbon's Insert tab, click
Add a check mark in "My table has Headers" and click
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.
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.
Select one of the cells in the list of employee names.
On the Ribbon, click the Formulas tab, and in the Defined Names group, click Define Name
In the New Name dialog box, type a one-word name for the selected range, e.g. EmpNames
Leave the Scope as Workbook
Click in the Refers to box (On the worksheet, the cell that is currently listed in the Refers to box will be highlighted)
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)
The table name and column name will appear in the Refers to box: =Table1[Employees]
Click OK, to complete the name.
Use the Name Box
This is a quick way to name a range of cells.
Click at the top of the heading cell, to select all the cells
in the list (the heading will not be selected).
Click in the Name box, to the left of the formula bar
Type a one-word name for the selected 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.
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
On the Ribbon's Data tab, click Data Validation.
From the Allow drop-down list, choose List
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 to close the Data Validation dialog box.
Click on one of the cells, and click the drop down arrow
Click on an item in the drop down list, to enter it into the cell.
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:
This method of Data Validation is case sensitive -- if a user types YES,
an error alert will be displayed.
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.)
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.
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.
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