Skip to content

Data Validation Examples

Jump to bottom
Mats Alm edited this page Nov 10, 2026 · 4 revisions

This page has moved. The current version of this page is at https://epplussoftware.com/docs/worksheet-and-ranges/data-validation/examples/. The EPPlus documentation is no longer updated in this wiki; please use the EPPlus guides and update your bookmarks.

Data Validation Types

There are 8 types of data validation:

  1. Any
  2. Whole
  3. Decimal
  4. List
  5. Date
  6. Time
  7. TextLength
  8. Custom

Any Validation

Lacks any type of formula but allows for adding tooltips to cells (as do all other types). Can be used like this for example:

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var anyValidation = ws.DataValidations.AddAnyValidation("A1");
   anyValidation.ShowInputMessage = true;
   anyValidation.PromptTitle = "A Popup";
   anyValidation.Prompt = "Put info about what's in this field or fields here without restricting it.";

   package.SaveAs("C:/temp/AnyExample.xlsx");
}

Whole Validation

Validation of whole (int) values as defined by Operator and Formula, and in some cases Formula2.

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var intValidation = ws.DataValidations.AddIntegerValidation("A1");
   intValidation.Operator = ExcelDataValidationOperator.lessThanOrEqual;
   intValidation.Formula.Value = 5;

   intValidation.ShowErrorMessage = true;
   intValidation.ErrorTitle = "Input invalid in Data Validation";
   intValidation.Error = "Value must be 5 or less!";

   package.SaveAs("C:/temp/WholeExampleSingle.xlsx");
}

With Operators Between or NotBetween you have to specify a second value via Formula2:

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var intValidation = ws.DataValidations.AddIntegerValidation("A1");
   intValidation.Operator = ExcelDataValidationOperator.between;
   intValidation.Formula.Value = 5;
   intValidation.Formula2.Value = 10;

   intValidation.ShowErrorMessage = true;
   intValidation.ErrorTitle = "Input invalid in Data Validation";
   intValidation.Error = "Value must between 5 and 10!";

   package.SaveAs("C:/temp/WholeExampleBetween.xlsx");
}

Decimal Validation

Same as Whole but with decimal values e.g. "1.5". Note that the data type is double. To add one, same as above except:

ws.DataValidations.AddDecimalValidation("A1")

List Validation

Defines a list of values with an optional dropdown.

The following code adds a dropdown list of valid options based on an Excel range:

private static void AddListValidationFormula(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list formula");
    sheet.Cells["B1"].Style.Font.Bold = true;
    sheet.Cells["B1"].Value = "Source values";
    sheet.Cells["B2"].Value = 1;
    sheet.Cells["B3"].Value = 2;
    sheet.Cells["B4"].Value = 3;
            
    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    // Alternatively:
    // var validation = sheet.Cells["A1"].DataValidation.AddListDataValidation();
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    // Keep in mind that this is a formula and as such you usually want to use absolute values so it does not change the range
    validation.Formula.ExcelFormula = "$B$2:$B$4";
}

The following code adds a dropdown list of valid options based on a set of values. Note that this option is only for a small amount of values, since Excel has a built-in limitation on the length of the field containing the values. If you want to create a data validation with a larger number of options, reference a formula instead as demonstrated above:

private static void AddListValidationValues(ExcelPackage package)
{
    var sheet = package.Workbook.Worksheets.Add("list values");

    // add a validation and set values
    var validation = sheet.DataValidations.AddListValidation("A1");
    validation.ShowErrorMessage = true;
    validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
    validation.ErrorTitle = "An invalid value was entered";
    validation.Error = "Select a value from the list";
    for (var i = 1; i <= 5; i++)
    {
        validation.Formula.Values.Add(i.ToString());
    }
    Console.WriteLine("Added sheet for list validation with values");
}

Date Validation

Used to limit dates. Datatype is DateTime.

The following example shows how to add a DateTime validation with the greaterThan operator. DateTime like other validations can also have Between and NotBetween operators which require two values:

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");

   var dateValidation = ws.DataValidations.AddDateTimeValidation("B1");
   dateValidation.Operator = ExcelDataValidationOperator.greaterThan;
   DateTime date = new DateTime(1997, 05, 01);
   dateValidation.Formula.Value = date;

   dateValidation.ShowErrorMessage = true;
   dateValidation.ErrorTitle = "Input invalid in Data Validation";
   dateValidation.Error = $"Date must be after {date}";

   package.SaveAs("C:/temp/DateTimeExample.xlsx");
}

Time Validation

Used to add time restrictions. The example below shows how to add a validation that ensures entered time must be later than 14:15. Note that the value of Formula can have properties changed directly here unlike most other validations:

using (ExcelPackage package = new ExcelPackage())
{
  var ws = package.Workbook.Worksheets.Add("test");

  var timeValidation = ws.DataValidations.AddTimeValidation("C1");
  timeValidation.Operator = ExcelDataValidationOperator.greaterThan;
  timeValidation.Formula.Value.Hour = 14;
  timeValidation.Formula.Value.Minute = 15;

  timeValidation.ShowErrorMessage = true;
  timeValidation.ErrorTitle = "Input invalid in Data Validation";
  timeValidation.Error = $"Time must be after {timeValidation.Formula.Value}";

  package.SaveAs("C:/temp/TimeExample.xlsx");
}

Text Length Validation

Same as Whole Validation but Excel will count characters of the text instead with the provided values.

Created with ws.DataValidations.AddTextLengthValidation("A1")

Custom Validation

Allows for custom defined formulas for data validation.

The following example shows an example of adding a custom validation to cells A1 to A5 that only accepts numbers:

using (ExcelPackage package = new ExcelPackage())
{
   var ws = package.Workbook.Worksheets.Add("test");
   var customValidation = ws.DataValidations.AddCustomValidation("A1:A5");

   customValidation.Formula.ExcelFormula = "ISNUMBER(A1)";

   customValidation.ShowErrorMessage = true;
   customValidation.ErrorTitle = "Input invalid in Data Validation";
   customValidation.Error = $"Value in cell must be number!!";

   package.SaveAs("C:/temp/CustomExample.xlsx");
}

See also

EPPlus Software AB - https://epplussoftware.com

Features - Licence overview - Developers

Home

EPPlus wiki

Versions

Worksheet & Ranges

Styling

Import/Export data

Formulas and filters

Charts & Drawing objects

Tables & Pivot Tables

VBA & Protection

Branching strategy and CI

Roadmap

License FAQ

Using our develop nuget feed

Clone this wiki locally
⌂ 换网址