Apply data validation to Excel cells @BrainUpp
introduction
Data validation is an Excel feature that lets you set restrictions on what data can or should be entered into a cell. For example, you may want to restrict data entry to a specific range of numbers, or limit the choices using a list. This tip shows how to programmatically add data validation to a cell using the free Excel API with C#.

This Excel library provides a class called CellRange that contains the DataValidation property that allows programmers to directly perform validation in a specific cell or range. The following sections show:

How to add numeric validation
How to create a list validation
Dealing with a data validation warning

One of the great features of MS Excel is the ability to restrict (validate) input into certain cells. The name of this feature in MS Excel is data validation.

Some examples are restricting the values of cells,

Text of a certain length
Figures in the area
a selection from a list of possible values,
and many more…
The data validation feature can be found on the Data tab as shown below.

Figure 1: Data Validation menu option

To illustrate the different types of data validation options, we will use the following data entry form:

Figure 2: Sample form

The data validation types shown in this post are:

Only integer values are accepted
Only decimal values are accepted
Only accept valid data
Accept values from lists
Only accept text of a certain length
Validating entries with formulas: Accepting plain text values
Validate entries with formulas: How to avoid duplicate entries in a column
How to identify invalid data
ACCEPT ONLY INTEGER VALUES

Sometimes it is required that users can only enter integer values, like in cell B8 in our example form. To enforce this restriction, we can follow these steps:

Click in cell B8.
Click on “Data”, “Data Validation”, “Data Validation…”
Select “Integer” under the “Allow:” option.
Select the criteria from the Data option, such as "between", and enter minimum and maximum values. Your screen should look like the image below.
Figure 03 – Data validation rule (integer)

Figure 3: Data validation rule (integer)

Click OK'.
Now users are only allowed to enter whole numbers in B8. If you enter anything other than whole numbers or a whole number outside the specified range, you will receive an error message like this.

Figure 04 – Standard error dialog box

Figure 4: Standard error dialog box

We can customize this window to display a more meaningful message by following these steps:

Click in cell B8.
Click on the tab “Data” “Data Validation” “Data Validation…”
You see the previously defined validation rule
Click the Error Alert tab.
Select “Stop” from the “Style:” combo box.
In the Title text box, enter meaningful text such as "Invalid data entered."
In the text box under Error Message, enter the message you want to display when invalid data is entered.
Your window should look like the image below.
Figure 05 – Custom error alarm

Figure 5: Custom error alarm

Click OK".
If you now enter invalid data in cell B8, you will receive an error message as shown in Figure 6 below.

Figure 06 – Error warning (integer)

Figure 6: Error message (integer)

ACCEPT ONLY DECIMAL VALUES

Sometimes, in our sample form, we may require that the user is only allowed to enter decimal values as specified in cell B10. To enforce this restriction, we can follow these steps.

Click in cell B10.
Click on the tab “Data” “Data Validation” “Data Validation…”
Select “Decimal” under the “Allow:” option.
Select criteria from the Data option, such as “greater than or equal to”, and enter “10000” as the “Minimum” value, as shown in Figure 7 below.
Click OK'.

Figure 7: Data validation rule (decimal number)

Now, if the user enters anything other than a decimal number in the acceptance criteria in B10, an error dialog box will appear as shown in Figure 8 below. This error message is created by following the same steps as shown above in Figure 5.

Figure 08 – Error message (decimal number)

Figure 8: Error message (decimal number)

ACCEPT ONLY VALID DATA

You can also use data validation to verify data, as required in cell B12 in our sample form. To enforce this constraint, we can follow these steps.

Click in cell B12.
Click on the tab “Data” “Data Validation” “Data Validation…”
Select “Date” from the “Allow:” option.
Select criteria from the "Dates" option, so in our case, "between". Enter "Start Date" and "End Date". The values specified in "Start Date" and "End Date" must be valid dates. We can also use a formula if needed. For example, we can restrict the maximum date to the current date, as shown in Figure 9 below.
Click OK'.

Please take the opportunity to connect with your friends and family and share this video with them if you find it useful.