Search Contextures Sites

 

 

Contextures
Excel news
by email

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Data Validation Excel 2003 -- Introduction

For the Excel 2010 video and instructions, click here.

What is Data Validation?

Data validation is a tool that helps you control the kind of information that is entered in your worksheet. With data validation, you can:

--provide users with a list of choices
--restrict entries to a specific type or size
--create custom settings

In this tutorial, you'll see how to create a drop down list of choices in a cell.

Video: Create a Drop Down List in Excel 2003

Watch this video to see the steps for creating a drop down list in Excel 2003. Written instructions are below the video.

Create a Drop Down List - Instructions

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

1. Create a List of Items

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.

  1. 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.)

2. Name the List Range

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.

  1. Select the cells in the list.
  2. Click in the Name box, to the left of the formula bar
  3. Type a one-word name for the list, e.g. FruitList.
  4. Press the Enter key.

Note: To create a named list that automatically expands to include new items, use a dynamic range.

Name Range


3. Apply the Data Validation

  1. Select the cells in which you want to apply data validation
  2. From the Data menu, choose Validation.
  3. From the Allow drop-down list, choose List

Allow List

  1. In the Source box, type an equal sign and the list name, for example: =FruitList
  2. Click OK.

List Source

Tip: To select a range name, instead of typing it:

  1. In the Data Validation dialog box, under Allow, select List
  2. Click in the Source box, and on the keyboard, press the F3 key
  3. From the Paste Name list, select a named range, and click OK.
  4. Click OK, to close the Data Validation dialog box.

To view the Excel 2003 steps in a short data validation basics video, click here

Paste Name dialog box

4. Using 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

Note: This method of Data Validation is case sensitive -- if a user types YES, an error alert will be displayed.

Delimited List

5. Allow Entries that are not in the List

To allow users to type items that are not in the list., turn off the Error Alert.

Show Error

6. Protect the List

To protect the list from accidental damage, if you have entered it on a different worksheet, you can hide that sheet.

  1. Select the sheet that contains the list
  2. Choose Format | Sheet | Hide

Hide the Sheet

Download the Sample File

Download the zipped Excel 2003 sample workbook

More Data Validation Tutorials

Data Validation Basics
Create Dependent Drop Down Lists
Dependent drop downs from a Sorted List  
Dependent Lists With INDEX  
Hide Previously Used Items in a drop down List
Display Data Validation Messages to the User
Display Input Messages in a Text Box 
Use a Data Validation List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation With Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  

Learn how to create Excel dashboards.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2013
All rights reserved.