Search Contextures Sites

Excel Data Validation Examples Criteria

Whole Number Decimal List Date Time Text Length

Use data validation to allow specific entries in cells on the worksheet. For example, you can limit the cells to whole numbers, or text of specific length. Listed below are the different settings that you can allow, with an example of each setting.

Whole Number If you allow Whole numbers, you can set or exclude a range of numbers, or specify a minimum number or maximum number. To set the allowed values, you can Type the values into the Data Validation dialog box, OR Refer to cells on the worksheet, OR Use formulas to set the values. For example, in the screen shot shown here, the MAX and MIN functions are used to set the minimum and maximum values.
Decimal If you allow Decimals, you can set or exclude a range of numbers, or specify a minimum number or maximum number. Type values into the Data Validation dialog box, OR Refer to cells on the worksheet, OR Use formulas to set the values
List You can create a drop down list of items to select. See the examples here: Data Validation
Date If you allow Dates, you can set or exclude a range of numbers, or specify a minimum number or maximum number. Type the dates into the Data Validation dialog box, OR Refer to cells that contain dates on the worksheet, OR Use formulas to set the dates In this example, the TODAY function sets the current date as the Start date, and 7 days from the current date is the End date:

Time If you allow Times, you can set or exclude a range of numbers, or specify a minimum number or maximum number. Type the times into the Data Validation dialog box, OR Refer to cells that contain times on the worksheet, OR Use formulas to set the times The TIME formula in this example uses the current time as an end time, so all entries have to be before the current time:

=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))

Text Length If you allow Text length, you can set or exclude a range of lengths, or specify a minimum number or maximum length. Type the textlength into the Data Validation dialog box, OR Refer to cells that contain a value on the worksheet, OR Use formulas to set the text length

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