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
If you allow Decimals, you can set or exclude a range of numbers, or specify a minimum number or maximum number.
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
If you allow Dates, you can set or exclude a range of dates, or specify a minimum date or maximum date.
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, those dates are entered in cells E1: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.
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
If you allow Times, you can set or exclude a range of numbers, or specify a minimum number or maximum number.
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.
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 Contextures Sites
Last updated: August 3, 2016 7:10 PM
Contextures RSS Feed