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 Excel, the data validation feature helps you control what can be entered in your worksheet. For example, you can:
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.
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
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
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.
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.
The table is now a Named Excel Table.
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.
This is the method shown in the video. You can set a name, scope and comments.
This is a quick way to name a range of cells.
Now that you have created a named range, you can use that to create a drop down list in one or more cells
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 see the written instructions here: List from Another Workbook
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:
To allow users to also type items that are not in the list, remove the check mark to turn off the Error Alert.
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.
This playlist has basic Data Validation videos. Click to start the playlist. Or, at the top left of the video, click the "Playlist" icon, and click on a video thumbnail to start that video.
This playlist has advanced Data Validation videos. Click to start the playlist. Or, at the top left of the video, click the "Playlist" icon, and click on a video thumbnail to start that video
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Create Dependent Drop Down Lists
Dependent Drop Down Lists Video
Data Validation Criteria Examples
Last updated: March 2, 2021 9:07 PM