For up-to-date Handsontable documentation, see handsontable.com/docs.

Handsontable allows you to restrict any input entered into a cell similarly to Microsoft Excel. Cells can be validated against various rules. For example, we can check whether a value is between 10 and 20, or if a date is valid one. We can make sure that the entered inputs are correct, thus keeping our data set consistent.

Data validation is a process, not a single action. We need to set rules, then validate the entries and, last but not least, let the user know what happens when the entry is not valid.

This process can be done in three simple steps:

• Select target cells
• Set validation rules
• Define the result of validation:
  • Do nothing
  • Allow invalid input, and
    • Display an alert,
    • Add a comment to a cell,
    • Apply custom formatting.
  • Prevent from submitting an invalid value
  • Reject an invalid value.

In this post I will guide you through all these steps. I will also show you how to extend Handsontable beyond the capabilities of desktop spreadsheet software.

Select target cells

First off, we have to choose which cells to validate. In Handsontable we can either select a single cell or groups of cells, or even entire columns and rows.

In this example, I have decided to validate column A, which contains a mix of different cell types and values. Column B holds only the description of the criteria used, while column C is an optional, comment field. See how it’s done below:

Set validation rules

I’m assuming that you want to use the exact same set of validation rules that you have in Excel. Well, since you can define a rule as a custom function, the possibilities are endless. In the below examples I will show you recipes on the most popular validation criteria, which are:

• Allow value
  • Between
  • Not between
  • Equal to
  • Not equal to
  • Greater than
  • Less than
  • Greater than or equal to
  • Less than or equal to
• Allow type
  • Number
  • Text
    • Text length
    • Only lowercase
    • Only uppercase
  • Date
  • Time

Let’s start from the grid we have created in the previous step. I’m going to validate only the values in column A. If the value is not allowed, it will change the background color to pale red. I’m doing this by adding a CSS class to each invalid cell – you can read more on this in the next paragraph.

Define the result of validation

It’s very important to let users know what happens when the data is not valid. We can, for example, ask them to correct data, and provide them with some hints on how to do that properly. In Handsontable, we can do that in several ways, listed at the beginning of this article. Let’s get it to work then!

You can see the validation result by changing the value in the column A to anything above 20.

The validation process is asynchronous, and for larger data sets it goes from the bottom right corner to the top left. It is optimized to not slow down the app, but nevertheless, it certainly will have some impact on the overall performance.

I hope you liked this article! Let me know in the comments section if I should add something more to it.