Data Validation

Updated on Jun 9, 2026

Data validation controls the type of data or the values that users enter into a cell. Compared to Protection, data validation does not restrict user input but ensures that the entered data complies with certain rules. For example, you can restrict input to a certain range of dates, whole numbers, decimal numbers, or a list of predefined values.

Data Validation Rules

The data validation rules help you restrict user input. You can also set a message that appears when the cell containing the rule is selected in a spreadsheet UI control.

You can specify how the user is notified if the rule is not satisfied. There are three types of notifications:

• Error: If the user enters not valid data, a message box offers the following choices:
  • Retry: Try again.
  • Cancel the change.
• Cancel: If the user enters not valid data, a message box offers the following choices:
  • Yes: Override the warning and apply the change.
  • No: Try again.
  • Cancel: Revert the change.
• Information: If the user enters not valid data, a message box offers the following choices:
  • OK: Override the warning and apply the change.
  • Cancel: Revert the change.

Data Validation Rule Types

The following data validation rule types are available:

• Any value
• Whole number
• Decimal
• List
• Date
• Time
• Text length
• Custom

To create data validation rules, pass parameters to the rule through data validation rule contexts. There are several context types with different parameters for the different types of data validation rules.

AnyValueDataValidationRuleContext

The AnyValueDataValidationRuleContext class exposes the following properties:

• ShowInputMessage: Specifies whether to show the input message.
• InputMessageTitle: The title of the input message.
• InputMessageContent: The content of the input message.
• ShowErrorMessage: Specifies whether to show the error message.
• ErrorStyle: Specifies the style of the error message. The possible choices are Error, Warning, and Information.

SingleArgumentDataValidationRuleContext

SingleArgumentDataValidationRuleContext exposes the properties of the AnyValueDataValidationRuleContext class and extends them with the following:

• IgnoreBlank: Specifies if the validation ignores blank values, considering them as valid values.
• Argument1: The argument needed for the validation.
• CellIndex: The cell index of the cell based on which the validation rule is created.
• Worksheet: The worksheet in which the data validation rule is created.

NumberDataValidationRuleContext

NumberDataValidationRuleContext exposes the properties of the SingleArgumentDataValidationRuleContext class and extends them with the following:

• ComparisonOperator: The comparison operator used by the data validation rule.
• Argument2: The second argument needed by the data validation rule.

ListDataValidationRuleContext

ListDataValidationRuleContext exposes the properties of the SingleArgumentDataValidationRuleContext class and extends them with the following:

• InCellDropdown: Specifies if a dropdown containing the list values of the data validation rule is shown.

Any Value Rule

The any value data validation rule is the default rule. It applies to all cells and does not perform any data validation. You can specify an input message. The error message is never shown because all values are considered valid input in this rule.

Example 1 shows how to create an any value validation rule and set it to a cell.

Example 1: Apply Any Value Rule

C#

AnyValueDataValidationRuleContext context = new AnyValueDataValidationRuleContext();
context.InputMessageContent = "Sample input message content";
context.InputMessageTitle = "Sample input message title";
AnyValueDataValidationRule rule = new AnyValueDataValidationRule(context);

worksheet.Cells[0, 0].SetDataValidationRule(rule);

The result from the code snippet in Example 1 is shown in Figure 1.

Figure 1: Any value rule

Whole Number Rule

The whole number data validation rule allows you to restrict user input to whole numbers in a certain range. The range is specified using two arguments and a comparison operator. In some cases (for example, when restricting input to the range between 0 and 100) both arguments are used. In other cases (for example, restricting input to numbers greater than 100) only one argument is used. An option to ignore blank values is turned on by default.

The code snippet in Example 2 shows how to create a whole number data validation rule that restricts user input to the range between 0 and 100 and considers blank values as not valid.

Example 2: Apply Whole Number Rule with Two Arguments

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values between 0 and 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are in the range between 0 and 100!";
context.IgnoreBlank = false;
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "0";
context.Argument2 = "100";

WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

The result from Example 2 is shown in Figure 2.

Figure 2: Whole number rule

The result from entering value "test" in the cell containing the data validation rule is shown in Figure 3.

Figure 3: Whole number rule not valid result

The code snippet in Example 3 shows how to create a whole number data validation rule that restricts user input with one argument to numbers greater than 100.

Example 3: Apply Whole Number Rule with One Argument

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values greater than 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all whole number values greater than 100!";
context.ComparisonOperator = ComparisonOperator.GreaterThan;
context.Argument1 = "100";

WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

You can enter any valid formula as a rule argument that returns a number. Example 4 shows how to restrict user input to the values less than the sum of the values in cells A1 and B1.

Example 4: Apply Whole Number Rule with Formula

C#

//The value of A1
worksheet.Cells[0, 0].SetValue(60);
//The value of B1
worksheet.Cells[0, 1].SetValue(40);

CellIndex dataValidationRuleCellIndex = new CellIndex(1, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to whole number values greater than 100";
context.ErrorStyle = ErrorStyle.Warning;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all whole number values greater than 100!";
context.ComparisonOperator = ComparisonOperator.LessThan;
context.Argument1 = "=Sum(A1+B1)";

WholeNumberDataValidationRule rule = new WholeNumberDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Figure 4: Whole number rule based on formula

Note that the cell index passed to the constructor of the NumberDataValidationRuleContext is the cell for which the rule is created. Consider the case in which an area from A2 to C5 is selected through the UI and the active cell is A2. You create the same rule as in Example 4 but apply it for the cell range A2:C5. The formula "=SUM(A1+B1)" contains two relative cell references – A1 and B1. If you select the cell C5 and open the data validation dialog box, the formula that specifies the minimum value is "=SUM(C4+D4)" instead of "=SUM(A1+B1)". The relative references in the formula are translated relatively to the cell passed in the constructor of the data validation rule context.

Decimal Rule

The decimal data validation rule allows you to restrict user input to decimal numbers in a certain range specified using two arguments and a comparison operator. The difference from the whole number rule is that the decimal rule allows both whole and decimal numbers in the cells.

Example 5 demonstrates how to create a decimal data validation rule that restricts user input to values outside the range between 0 and 100.

Example 5: Apply Decimal Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to decimal number values out of the range between 0 and 100";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all decimal number values out of the range between 0 and 100!";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "0";
context.Argument2 = "100";

DecimalDataValidationRule rule = new DecimalDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

List Rule

The list data validation rule allows you to restrict user input to a predefined set of values. Use the InCellDropdown property to specify if a dropdown list containing the values is shown next to the cell.

Example 6 shows the creation of a list data validation rule that restricts user input to a day of the week.

Example 6: Apply List Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

ListDataValidationRuleContext context = new ListDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to the week days.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are the week days!";
context.InCellDropdown = true;
context.Argument1 = "Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday";

ListDataValidationRule rule = new ListDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Figure 5 shows the result from Example 6.

Figure 5: List rule

Date Rule

The date rule allows you to restrict user input to a certain range of dates specified using two arguments and a comparison operator.

Example 7 shows how to restrict user input to the dates in the range between 12 February 2013 and 22 May 2017.

Example 7: Apply Date Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to dates in the range from 12 February 2013 to 22 May 2017.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all dates between 12 February 2013 and 22 May 2017";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = new DateTime(2013, 2, 12).ToShortDateString();
context.Argument2 = new DateTime(2017, 5, 22).ToShortDateString();

DateDataValidationRule rule = new DateDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Time Rule

The time rule allows you to restrict user input to a certain range of times specified using two arguments and a comparison operator.

The code snippet in Example 8 shows how to restrict user input to the range between 10:25 AM and 3:45 PM:

Example 8: Apply Time Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to times in the range from 10:25 AM to 3:45 PM.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are all times between 10:25 AM to 3:45 PM.";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = new DateTime(2015, 2, 2, 10, 25, 0).ToShortTimeString();
context.Argument2 = new DateTime(2015, 2, 2, 15, 45, 0).ToShortTimeString();

TimeDataValidationRule rule = new TimeDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Text Length Rule

The text length rule allows you to restrict user input to text with a length in a certain range specified using two arguments and a comparison operator.

Example 9 shows how to restrict user input to text with a length between 5 and 10 symbols.

Example 9: Apply Text Length Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(0, 0);

NumberDataValidationRuleContext context = new NumberDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to text with length between 5 and 10 symbols";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. It is allowed to enter only text with length between 5 and 10 symbols.";
context.ComparisonOperator = ComparisonOperator.Between;
context.Argument1 = "5";
context.Argument2 = "10";

TextLengthDataValidationRule rule = new TextLengthDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Custom Rule

The custom rule allows you to restrict user input with a custom condition specified through a formula that results in a Boolean value.

The code snippet in Example 10 shows how to restrict user input to values greater than or equal to the sum of the values in cells A1 and B1.

Example 10: Apply Custom Rule

C#

CellIndex dataValidationRuleCellIndex = new CellIndex(1, 0);

SingleArgumentDataValidationRuleContext context = new SingleArgumentDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to values that are greater or equal to the sum of the values in the cells A1 and B1.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. It is allowed to enter only values that are greater or equal to the sum of the values in the cells A1 and B1";
context.Argument1 = "=A2=Sum(A1, B1)";

CustomDataValidationRule rule = new CustomDataValidationRule(context);

worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

Evaluate Rules

To check if a cell value satisfies a rule, evaluate the rule using the desired cell value. Each data validation rule implements the IDataValidationRule interface, which exposes the Evaluate() method. The method receives as parameters the worksheet in which the value is located, the cell index in which the value is placed or contained, and the value itself.

Example 11 demonstrates how to evaluate a rule using the Evaluate() method.

Example 11: Evaluate Rule

C#

double value = 125;
ICellValue cellValue = value.ToCellValue();
if (rule.Evaluate(worksheet, 0, 0, cellValue))
{
	//the rule is satisfied, the value 125 is valid
}
else
{
	//the rule is not satisfied
}

See Also

• Protection
• Formulas
• Adding a ComboBox to an Excel File Using RadSpreadProcessing
• Setting List DataValidation Rule to Reference Cell Range in SpreadProcessing