Search Contextures Sites

Excel Data Validation -- Introduction

What is Data Validation? Provide a Drop-down List of Options Create a List of Items Name the List Range Apply the Data Validation Using a Delimited List Allow Entries that are not in the List Protect the List To view the steps in a short data validation intro video, click here Download the zipped sample Data Validation workbook

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 Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Edit|Clear|ClearAll

Data validation list

Provide a Drop-down List of Options Use Data Validation to create a dropdown 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. 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 dropdown 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.) 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. 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.

3. Apply the Data Validation 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

In the Source box, type an equal sign and the list name, for example: =FruitList Click OK.
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 steps in a short data validation basics video, click here

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.
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.
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. Select the sheet that contains the list Choose Format | Sheet | Hide Download the zipped sample workbook

Excel Tutorials - Data Validation

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

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish