Data validation lets you control the data that an end-user can enter into a cell. There are various ways in which a data can be validated such as restricting invalid input keys, showing the error or warning information or revert the value to original on getting an invalid input from the user.

The following image showcases data validation applied to the Excel worksheet. Here, we display an input message to notify the end-users to enter a value ranging between 1-5.

In Excel, data validation can be implemented using the XLDataValidation class. The XLDataValidation class provides various properties such as Error, ValidationType, ShowErrorMessage, ShowInputMessage, and much more for implementing enhanced data validation. The ValidationType property accepts input from the XLValidationType enumeration to set the validation type to one of the following:

To apply data validation in Excel, you can use the following code. In this example, we apply data validation to restrict the end-users to enter a number between 1 to 5 by displaying an input messages using Prompt property and an error message using Error property.

var validation = new XLDataValidation(c1XLBook1);
validation.AllowBlank = true;
validation.Prompt = "Input a value between 1 and 5(included), please";
validation.PromptTitle = "Tips";
validation.Error = "input value does not fall between 1 and 5(included)";
validation.ErrorTitle = "Error";
validation.ShowErrorMessage = true;
validation.ShowInputMessage = true;
validation.Ranges.Add(new XLRange(1, 1, 5, 1));
validation.ValidationType = XLValidationType.Whole;
validation.Operator = XLComparisonOperator.Between;
validation.FirstFormula = "1";
validation.FirstFormula = "5";
c1XLBook.Sheets[0].DataValidations.Add(validation);