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.
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
For example, in this example, the MAX and MIN functions set the minimum and maximum values, based on values in cells J5:J9.
If you allow Decimals, you can set or exclude a range of numbers, or specify a minimum number or maximum number.
In this example, values are entered for the minimum and maximum.
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
If you allow Dates, you can set or exclude a range of dates, or specify a minimum date or maximum date.
See more Date Rule examples
For example, enter a date that is between the 2 dates entered in worksheet cells F1 and F2.
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.
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:
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.
To set the earliest start date allowed as 60 days prior to the current date:
To set the latest start date allowed as 60 days after the current date:
If you allow Times, you can set or exclude a range of numbers, or specify a minimum number or maximum number.
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:
If you allow Text length, you can set or exclude a range of lengths, or specify a minimum number or maximum length.
Download the sample Data Validation Criteria Examples file. The zipped file is in xlsx format, and does not contain any macros.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: October 25, 2020 3:31 PM
Contextures RSS Feed