Home > Validation > Rules > Criteria Data Validation Criteria Examples Use Data Validation to allow specific entries in cells on a worksheet. For example, show a list, limit the cells to whole numbers, or text of specific length. This tutorial shows the available settings, 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
2. OR, Refer to cells on the worksheet
3. OR, Use formulas to set the values.

For example, in this example, the MAX and MIN functions set the minimum and maximum values, based on values in cells J5:J9.

• Minimum: =MIN(J5:J9)
• Maximum: =MAX(J5:J9)

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
2. OR, Refer to cells on the worksheet
3. OR, Use formulas to set the values

In this example, values are entered for the minimum and maximum.

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

For example, show a drop down list of weekday names

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
2. OR, Refer to cells that contain dates on the worksheet
3. OR, Use formulas to set the dates

See more Date Rule examples

For example, enter a date that is between the 2 dates entered in worksheet cells F1 and F2.

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, the start and end dates are entered in cells E1 and 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. With the following formula, the date range will automatically adjust, based on the current date.

Follow these steps, to allow dates from 60 days before the current date, to 60 days after the current date.

1. Select the cell where you want the data validation applied
2. On the Ribbon, click the Data tab, then click Data Validation
3. From the Allow drop down, select Date
4. In the Data drop down, select one of the operators, such as Greater Than, or Between
5. Depending on the operator you selected, Start Date and/or End Date boxes will appear
6. Type a formula in the Start Date and/or End Date boxes, using the TODAY function. For example,

To set the earliest start date allowed as 60 days prior to the current date:

=TODAY()-60

To set the latest start date allowed as 60 days after the current date:

=TODAY()+60

7. (optional) Add an Input Message, to explain the data validation rule when the cell is selected
8. (optional) Change the Error Alert setting, so it shows a message, or warns users about invalid data.
9. Click OK, to close the dialog box.

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
2. OR, Refer to cells that contain times on the worksheet
3. OR, Use formulas to set the times

For example, type a start time and end time in the data validation settings. Here, the start time is 12 noon, and the end time is 1:30 PM.

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.

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

Get the Sample File

Get the sample Data Validation Criteria Examples file. The zipped file is in xlsx format, and does not contain any macros.

More Data Validation Tutorials

Data Validation Basic

Data Validation Date Rules

Create Dependent Lists

Use a List from Another Workbook

Data Validation Tips