The data validation feature in Excel is a great time saver for repetitive data entry as well as an effective way to control data quality being entered into an Excel work sheet.

By using data validation on a cell you indicate to the user the type of data you want them to enter in to the work book.

As part of restricting data entry to a number (which can be a whole number, decimal, date, or time), you also will specify the permissible values for that type of number (a whole number between 10 and 100 or a date between January 1, 2012, and December 31, 2012, for example as in this example.

To set a specific date range restriction to users-

  • Select your cells to apply validation to
  • Data Tab- Data Validation
  • From the Allow Drop Down select Date
  • In the drop down select your operator such as between, greater than etc

Depending on the choice of operator, the start date and/or end date boxes will appear.

  • Type in your start and/or end date or you can refer to another worksheet that contains this information
  • Enter and optional message to explain your date requirements such as ‘Please enter a date between 01 January 2015 to 31 January 2015
  • Enter any custom error or warning messages to your users in the Error Alert settings. This could be something like ‘You need to enter a date’
  • To specify an alert message, click the Error Alert tab of the Data Validation dialog box, where you can select the kind of warning from the Style drop-down list:
  • Stop (the default, which uses a red button with a cross in it)
  • Warning (which uses a yellow triangle with an exclamation point in it), or
  • Information (which uses a balloon with a blue I in it).

After selecting the type of alert you want, you then enter the title for its dialog box in its Title text box and enter the text of the alert message in the Error Message list box.

You can then copy the restriction to a range that is not formatted as a table by using the Data Validation feature to set up the type of entry and permitted range in the first cell and then use the Fill handle to copy that cell’s Data Validation settings to subsequent cells in the same column or row.

If you have a table a cell range formatted as a table, then you can apply the restrictions you are defining in the Data Validation dialog box to all the other cells that are formatted as a table. Just click the Apply These Changes to All Other Cells with the Same Settings check box before you click OK.

This will help with data entry speed as well as data quality in your Excel work book.