Search Contextures Sites
Use Excel data validation to create drop down lists on a worksheet, so data entry is easier. For the Excel 2010 video and instructions, click here.
- What is Data Validation?
- Video: Create a Drop Down List in Excel 2003
- Create a Drop Down List - Instructions
- Download the Sample File
- More Data Validation Tutorials
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.
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.
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.
- In single row or column, type the entries you want to see in the drop-down list. (Note: The list must be in a single block of cells -- e.g. you can use A2:A6, but not A2, A4, A6, A8.)
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.
- Select the cells in the list.
- Click in the Name box, to the left of the formula bar
- Type a one-word name for the list, e.g. FruitList.
- Press the Enter key.
Note: To create a named list that automatically expands to include new items, use a dynamic range.
- Select the cells in which you want to apply data validation
- From the Data menu, choose Validation.
- From the Allow drop-down list, choose List
Tip: To select a range name, instead of typing it:
- In the Data Validation dialog box, under Allow, select List
- Click in the Source box, and on the keyboard, press the F3 key
- From the Paste Name list, select a named range, and click OK.
- Click OK, to close the Data Validation dialog box.
To view the Excel 2003 steps in a short data validation basics video, click here
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.
- Select the sheet that contains the list
- Choose Format | Sheet | Hide
Download the zipped Excel 2003 sample workbook
Contextures Inc., Copyright ©2015
All rights reserved.