Data validation can make sure the right types of data get into rows and columns. Use data validation to restrict the type of data or the values that users can enter into cell. The most common data validation use is to have a drop-down list.

The most common problems encountered with a large amount of data in Excel is validating whether the data in the column was correctly entered based on what is expected type of data. For instance, if the type of data required is date but somehow other rows contain text or number. If we import excels file with inconsistent types of data, it may cause errors on the other end.

How to define a data validation and create a dropdown list?

1. Go to “Data” tab
2. Select “Data validation”

By default any value can be added to the cell but we could limit to just dates

Settings validation criteria

Set the type of validation. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your selected value.

Any value – This option removes any existing data validation.

Whole number – User must enter a whole number. For an instance, you can specify that the entry must be an integer greater than or equal to 20.

Decimal – User must enter a number. For an instance, you can specify that the entry must be greater than or equal to 1 and less than or equal to 10.

List – User must select from a list of entries you provide. You will create drop-down list with this validation. You have to give input ranges then those values will appear in the drop-down list.

Date – User must enter a date. You enter a valid date range from selections in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to March 1, 2017, and less than or equal to November 31, 2017.

Time – User must enter a time. You specify a valid time range from selections in the Data drop-down list. For an instance, you can specify that the entered data must be later than 1:00 p.m.

Text Length – Length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For an instance, you can specify that the length of the entered data be 2 (a single alphanumeric character).

Custom – You must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either TRUE or FALSE).

3. Limit column input to “Dates” only

4. Provide the range of date to restrict the data to be entered.

5. Specify the “Input Message” that you want to be shown when the cell is selected.

6. Error Alert can be specified to provide notification to the user to enter the correct type of data.

As an example shown in the figure below, I have column that requires entering the dates.When I selected column “B”, it automatically shows the notification of the type of data to be entered on this column.

If I entered a date that is not within the range provided in the data validation settings, it will give me an alert notification.

Another example below is adding a List type of data validation to a cell or range

• Select the cell(s) to validate
• Go to “Data” tab
• Select “Data Validation”

• On the “Settings” tab, “Allow” box, select “List”

• In the “Source” box, type the list values separated by commas.

Important: This is only recommended for the list items that are not to ever change. If you have a list that may change, or you need to add or remove items, then it would be better to follow the step below:

Create a list of entries by referring to a range of cells in the workbook. One way is to create a list, then format as an Excel Table (Go to “Home” and select “Format as Table”). Next, select the table’s Data Body Range, and give it a meaningful name in the Name Box.

Instead of typing the list values in the data validation “Source” box, you just need to add the name that you defined preceded by an Equal sign same as figure shown below.

Adding data validation through VBA macro code

Another option to add data validation is through VBA macro programming. This is very useful when you want to be flexible with the validation attributes to be applied for a type of data on particular cell. For an instance, you can set a variable of Formula values, Input message string or Error message string to be displayed when the condition is met or invalid user input.

Syntax:

expression .Add(Type, AlertStyle, Operator, Formula1, Formula2)

expression A variable that represents a Validation object.

Sample Code:

Sub addDataVal()
'Sample code for adding data validation

    With Range("e3").Validation
     .Add Type:=xlValidateWholeNumber, _
     AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, Formula1:="1", Formula2:="10"
     .InputTitle = "Integers"
     .ErrorTitle = "Integers"
     .InputMessage = "Enter a numeric value from one to ten"
     .ErrorMessage = "You should enter a number from one to ten"
    End With
    
End Sub

There are many purpose of using Excel data validation depending on your needs. The most important is you know how to implement it and when to apply it in your Excel data workbook. This Excel feature could simplify the user input on some data restrictions to make sure that the user will only provide the expected type of data. Furthermore, it will minimize the time for analyzing which data is incorrectly entered resulting to unexpected error.