Search Contextures Sites

 

 

Contextures
Excel news
by email

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Data Validation Examples Criteria

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

  1. Type the values into the Data Validation dialog box, OR
  2. Refer to cells on the worksheet, OR
  3. 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.

data validation criteria 01

Decimal

If you allow Decimals, you can set or exclude a range of numbers, or specify a minimum number or maximum number.

  1. Type values into the Data Validation dialog box, OR
  2. Refer to cells on the worksheet, OR
  3. Use formulas to set the values

data validation criteria 02

List

You can create a drop down list of items to select in a cell, or group of cells. See the examples and instructions here: Data Validation

Date

If you allow Dates, you can set or exclude a range of dates, or specify a minimum date or maximum date.

  1. Type the dates into the Data Validation dialog box, OR
  2. Refer to cells that contain dates on the worksheet, OR
  3. Use formulas to set the dates

Date Video

To see the steps for creating data validation for a date range, please watch this short video tutorial. The written instructions are below the video.

Create Date Range Validation

To set up date validation, you can enter a start and end date on the worksheet. In this example, those dates are entered in cells E1:E2.

Next, follow these steps to set up the data validation:

  1. Select the cells where the data validation will be applied cells B2:B6 in this example.
  2. On the Excel Ribbon, click the Data tab, and click Data Validation
  3. From the Allow drop down, select Date
  4. From the Data drop down, select Between
  5. Click in the Start Date box, and click cell E1, where the Start Date is entered.
  6. Press the F4 key, to change the cell reference to an absolute reference -- $E$1
  7. Click in the End Date box, and click cell E2, where the End Date is entered.
  8. Press the F4 key, to change the cell reference to an absolute reference -- $E$2
  9. Click OK, to close the Data Validation window.

Use a Date Formula

Instead of entering dates on the worksheet, you could use a formula to set the start and end dates.

For example, enter the TODAY function as the Start date: =TODAY()

For the End date, use a formula to calculate 6 days from the current date: =TODAY() + 6

Time

If you allow Times, you can set or exclude a range of numbers, or specify a minimum number or maximum number.

  1. Type the times into the Data Validation dialog box, OR
  2. Refer to cells that contain times on the worksheet, OR
  3. 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()))

data validation criteria 04

Text Length

If you allow Text length, you can set or exclude a range of lengths, or specify a minimum number or maximum length.

  1. Type the textlength into the Data Validation dialog box, OR
  2. Refer to cells that contain a value on the worksheet, OR
  3. Use formulas to set the text length

data validation criteria 05

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

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  

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.