Click or drag to resize

Data Validation in Excel Spreadsheets with ExpertXls

Data validation restricts the values a user can type in cells when the workbook is edited in Excel. The DataValidator property of a range returns an ExcelDataValidator. Set the allowed data type (ExcelDataValidatorDataType): a list of values, whole numbers, decimal numbers, dates, times or a text length; the operator and the limits; the message Excel shows when a cell is selected; and the error alert Excel shows when the value is not valid.

The error alert style is Stop (the value is rejected), Warning or Information (the user can keep the value).

A Drop-Down List
ExcelDataValidator listValidator = worksheet["G5"].DataValidator;
listValidator.AllowedDataType = ExcelDataValidatorDataType.List;
listValidator.AllowedValues = new string[] { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library" };
listValidator.InCellDropDown = true;
listValidator.InputMessageTitle = "Product";
listValidator.InputMessageText = "Select a product from the list";
listValidator.ShowInputMessage = true;
Numbers, Dates and Text Length
// a whole number between 0 and 10; other values are rejected
ExcelDataValidator numberValidator = worksheet["G7"].DataValidator;
numberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber;
numberValidator.Operator = ExcelDataValidatorOperator.Between;
numberValidator.Value1 = 0;
numberValidator.Value2 = 10;
numberValidator.ErrorAlertTitle = "Whole number expected";
numberValidator.ErrorAlertText = "A whole number between 0 and 10 is expected";
numberValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Stop;
numberValidator.ShowErrorAlert = true;

// a date in 2026
ExcelDataValidator dateValidator = worksheet["G9"].DataValidator;
dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date;
dateValidator.Operator = ExcelDataValidatorOperator.Between;
dateValidator.Value1 = new DateTime(2026, 1, 1);
dateValidator.Value2 = new DateTime(2026, 12, 31);
dateValidator.ErrorAlertText = "A date in 2026 is expected";
dateValidator.ShowErrorAlert = true;

// a text of 2 to 5 characters; the warning lets the user keep a longer text
ExcelDataValidator textValidator = worksheet["G11"].DataValidator;
textValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength;
textValidator.Operator = ExcelDataValidatorOperator.Between;
textValidator.Value1 = 2;
textValidator.Value2 = 5;
textValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Warning;
textValidator.ErrorAlertText = "The text should have 2 to 5 characters. Keep it anyway?";
textValidator.ShowErrorAlert = true;

// decimal numbers greater than 0 in a range of cells
ExcelDataValidator priceValidator = worksheet["G13:G16"].DataValidator;
priceValidator.AllowedDataType = ExcelDataValidatorDataType.Decimal;
priceValidator.Operator = ExcelDataValidatorOperator.GreaterThan;
priceValidator.Value1 = 0;
priceValidator.ShowErrorAlert = true;
See Also