New to Kendo UI for Angular? Start a free 30-day trial

Angular Spreadsheet Data Validation

The Kendo UI for Angular Spreadsheet provides a built-in data validation tool that allows you to restrict the type of data that can be entered into a cell or range of cells. You can also predefine validation rules from the Spreadsheet data collection.

The data validation tool is similar to the data validation feature in Microsoft Excel, allowing you to define rules for various data types.

If a user enters data that doesn't meet the criteria, you can choose to accept or reject the value. Accepted invalid values show a red indicator in the cell corner, while rejected values display a custom error message.

The following example demonstrates how to use the built-in validation tool to restrict the input to a specific values.

Change Theme

Theme

Loading ...

Data Validation Tool

The data validation tool can be accessed from the Data menu item of the Spreadsheet. It allows you to define rules for various data types dynamically, ensuring that the entered data meets specific criteria. The tool displays a dialog with various of options to set the validation criteria, error messages, and other settings.

The data validation tool provides a user-friendly interface for defining validation rules, making it easy to set up and manage data validation in your spreadsheet.

Supported data types include:

Criteria	Description
`Number`	Validate numeric ranges (e.g., between 1 and 100). To specify the criteria, choose the criteria and provide the min and max numeric value.
`Text`	Restrict input to specific text patterns or lengths. Enclose the desired text in quotes (e.g., `"Checked"`).
`Date`	Ensure dates fall within a defined range. Specify criteria using valid start and/or end date values (e.g., `DATEVALUE("1/1/1950")` to `DATEVALUE("1/1/2025")`).
`Custom Formulas`	Use formulas to create complex validation rules (e.g., `AND(ISNUMBER(D6),LEN(D6)<14)` for validating phone numbers).
`List`	Simplify data entry by providing predefined options in a dropdown menu. Specify criteria as a comma-separated list (e.g., `Value 1, Value 2`).

Data Validation API

The data validation API allows you to set up data validation rules programmatically. You can use the validation property of the Cell object to define the validation criteria for a specific cell.

The validation property accepts an object with the following properties:

Property	Type	Description
`dataType`	string	The type of data to validate (e.g., `number`, `text`, `date`, `custom`, `list`).
`comparerType`	string	Defines the comparer type that is used to validate the cell value. Supported values are: `greaterThan` `lessThan` `between` `equalTo` `notEqualTo` `greaterThanOrEqualTo` `lessThanOrEqualTo` `notBetween` `custom`
`from`	string	Defines a formula or a value that is used for the comparison process. Used as the only compare value if the comparer type does not require a second argument. Mandatory for validation to work.
`to`	string	Defines a formula or a value that is used for the comparison process. Will be used if the comparer type requires a second argument.
`allowNulls`	boolean	Whether to allow null values.
`type`	string	The type of validation (e.g., `reject`, `warning`).
`showButton`	boolean	Whether to show a button for date selection (for date validation).
`titleTemplate`	string	The title of the error message.
`messageTemplate`	string	The message of the error.

Numeric Validation

The numeric validation allows you to restrict the input to a specific range of numbers. You can set the minimum and maximum values for the validation. For example, you can restrict the input to numbers between 1 and 100. The user will be prompted to enter a number within this range.

{
    value: 45,
    validation: {
        dataType: 'number',
        from: '1',
        to: '100',
        comparerType: 'between',
    },
},

Text Validation

The text validation allows you to restrict the input to a specific text pattern or length. You can specify the desired text in quotes. For example, you can restrict the input to the text Checked. The user will be prompted to enter this specific text.

{
    value: 'Checked',
    validation: {
        dataType: 'text',
        comparerType: 'equal',
        from: '"Checked"',
    },
},

Date Validation

The date validation allows you to restrict the input to a specific range of dates. You can set the minimum and maximum dates for the validation. For example, you can restrict the input to dates between January 1, 1950, and January 1, 2025. The user will be prompted to enter a date within this range.

{
    value: 35991,
    format: 'M/d/yyyy',
    validation: {
        dataType: 'date',
        showButton: true, // Shows the calendar button.
        comparerType: 'between',
        from: 'DATEVALUE("1/1/1950")',
        to: 'DATEVALUE("1/1/2025")',
    },
},

Custom Formula Validation

The custom formula validation allows you to create complex validation rules using formulas. You can use built-in functions to define the criteria. For example, you can use the following formula to ensure that the input is a valid email address.

{
    value: 'first.lastname@mail.com',
    validation: {
        dataType: 'custom',
        from: 'AND(NOT(ISERROR(FIND("@", B3))), NOT(ISERROR(FIND(".", B3))), ISERROR(FIND(" ", J1)), LEN(B3)>5)',
    },
},

List Validation

The list validation allows you to provide a predefined list of values for the user to choose from. You can specify the list as a comma-separated string. For example, you can provide the list Value 1, Value 2. The user will be prompted to select a value from this list.

{
    value: 'Value 1',
    validation: {
        dataType: 'list',
        from: 'ListValues!A1:B1', // Reference to a new sheet with the list values.
    },
},

Custom Error Messages

You can customize the error message that appears when a user enters invalid data. This message can provide guidance on what type of data is expected.

{
    value: 45,
    validation: {
        // ...
        titleTemplate: 'Invalid value',
        messageTemplate: 'Please enter a number between 1 and 100.',
    },
},