Use Excel data validation to create drop down lists on a worksheet, so data entry is easier. Watch the video, and download the sample file
For newer Excel versions, to get the video and instructions, click here.
Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can:
In this tutorial, you'll see how to create a drop down list of choices in a cell.
Watch this video to see the steps for creating a drop down list in Excel 2003. Written instructions are below the video. The video transcript is at the end of the page.
Use Data Validation to create a drop down list of options in a cell. List items can be typed in a row or column on a worksheet, or typed directly into the Data Validation dialog box.
Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Edit|Clear|ClearAll
If the list of options is more than a couple of items, it will be easier to maintain if you type the list on a worksheet. The list can be entered 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.
If you type the items on a worksheet, and name the range, you can refer to the list from any worksheet in the same workbook.
Note: To create a named list that automatically expands to include new items, use a dynamic range.
Tip: To select a range name, instead of typing it:
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:
To allow users to type items that are not in the list., turn off the Error Alert.
To protect the list from accidental damage, if you have entered it on a different worksheet, you can hide that sheet.
Get the zipped Excel 2003 sample data validation drop down lists workbook
Here is the trascript for the vidwo at the top of this page.
You've created an order form, and instead of having people type the name of the item they'd like to order, you're going to make a drop down list so that people can just select from that list.
That way, you'll prevent typing mistakes and people ordering things that aren't available.
To start, we're going to create a list of items and we've done that on the sheet called Lists.
We've typed the items in column B and now I'm going to select all those cells and we're going to give that group of cells a name.
To do that we'll click in the Name Box and type -- this is going to be FruitList -- the
name has to be all one word
We'll type, and then press the Enter key. And now, with those cells selected, you can see the name
Now we'll go back to be order sheet which is called DataEntry, and we'll select the cells where we want the list
To create the list we'll use data validation, so go to the Data menu, and click Validation
On the Settings tab we'll select List -- that's what we're going to allow
For the source, we start with an equal sign, and then type the name of our list
Or on your keyboard you can press F3, and that will bring up a list of all the names that you've created
So there's FruitList. Click OK and now it's entered for us
We'll click OK, and now you can click on any of those cells and select from the drop-down list
Last updated: July 13, 2021 3:53 PM