Apply Data Validation To Excel Cells
Apply Data Validation To Excel Cells @BrainUpp
Introduction
Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. For example, you may want to restrict data entry to a certain range of numbers or limit choices by using a list. This tip presents how to add data validation to a cell programmatically using free Excel API with C#.
This Excel library provides a class named CellRange which contains DataValidation property, enabling programmers to handle validation in a specific cell or range directly. Following sections will demonstrate:
How to add a numeric validation
How to create a list validation
How to handle a data validation alert
One of the great features that MS Excel has is the capability to restrict (validate) the input into certain cells. The name of this feature in MS Excel is Data Validation.
A few examples of this is to restrict the values of cells to be
text of a certain length
numbers within range
a selection from a list of possible values,
and many more…
The data validation feature is found in the ‘Data’ tab as shown below.
Figure 1: Data Validation Menu Option
In order to illustrate the different types of data validation options. We’ll use the following data entry form:
Figure 2: Sample Form
The data validation types shown in this post are:
Accepting Integer Values Only
Accepting Decimal Values Only
Accepting Valid Dates Only
Accepting Values From Lists
Accepting Text Of A Certain Length Only
Validating Entries With Formulas: Accepting Text Only Values
Validating Entries With Formulas: How To Avoid Duplicate Entries In A Column
How To Circle Invalid Data
ACCEPTING INTEGER VALUES ONLY
Sometimes it is required that users must type integer values only, as in cell B8 in our sample form. In order to enforce this constraint, we can follow these steps:
Click in cell B8.
Click on ‘Data’ ‘Data Validation’ ‘Data Validation …’
Select ‘Whole number’ from ‘Allow:’ option.
Select the criteria from the ‘Data’ option, e.g. ‘between’ and give minimum and maximum values. Your screen should look like the one shown in the figure below.
Figure 03 - Data Validation Rule (Whole Number)
Figure 3: Data Validation Rule (Whole Number)
Click ‘OK’.
Now users will be not allowed to type anything but whole numbers in B8. If you enter something different from whole numbers or a whole number outside the specified range, then you’ll get an error message like this one.
Figure 04 - Default Error Dialog Box
Figure 4: Default Error Dialog Box
We can customize this window to display a more meaningful message following these steps:
Click in cell B8.
Click on ‘Data tab’ ‘Data Validation’ ‘Data Validation …’
You will see the validation rule previously defined
Click on the ‘Error Alert’ tab.
Select ‘Stop’ from ‘Style:’ combo box.
Type a meaningful text like ‘Invalid Data Entered’ in ‘Title’ textbox.
Type the message to be displayed when an invalid data is entered in text area provided under ‘Error Message’.
Your window should look like as shown in the figure below.
Figure 05- Customized Error Alert
Figure 5: Customized Error Alert
Click on ‘OK’.
Now if you enter an invalid data in cell B8, then you will receive an error message as shown in figure 6 below.
Figure 06 - Error Alert (Whole Number)
Figure 6: Error Alert (Whole Number)
ACCEPTING DECIMAL VALUES ONLY
Sometimes it is required that user must type decimal values only as required in cell B10 in our sample form. In order to enforce this constraint, we can follow these steps.
Click in cell B10.
Click on ‘Data tab’ ‘Data Validation’ ‘Data Validation …’
Select ‘Decimal’ from ‘Allow:’ option.
Select criteria from ‘Data’ option, e.g. ‘greater than or equal to’ and enter ‘10000’ as ‘Minimum’ value as shown in the figure 7 below.
Click ‘OK’.
Figure 7: Data Validation Rule (Decimal Number)
Now, if the user enters something different than a decimal number within the acceptance criteria in B10 then an error dialog box is displayed as shown in figure 8 below. This error message is created by following the same steps shown above in figure 5.
Figure 08 - Error Alert (Decimal Number)
Figure 8: Error Alert (Decimal Number)
ACCEPTING VALID DATES ONLY
You can use data validation for validating dates as well, as required in cell B12 in our sample form. In order to enforce this constraint, we can follow these steps.
Click in cell B12.
Click on ‘Data tab’ ‘Data Validation’ ‘Data Validation …’
Select ‘Date’ from ‘Allow:’ option.
Select criteria from ‘Data’ option, e.g. ‘between’ in our case. Enter ‘Start Date’ and ‘End Date’. The values given in ‘Start date’ and ‘End date’ must be valid dates. We can also use formula if required. For example, we can limit the maximum date to the current date as shown in the figure 9 below.
Click ‘OK’.
Watch video Apply Data Validation To Excel Cells online without registration, duration hours minute second in high quality. This video was added by user Brain Up 27 September 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 8,607 once and liked it 187 people.