Simple Steps to Validate Data in a JavaScript Spreadsheet
Tags: javascript, webdev, spreadsheet
Published: 2021-09-03 11:45:22

---
title: Simple Steps to Validate Data in a JavaScript Spreadsheet
published: true
date: 2021-09-03 10:00:24 UTC
tags: javascript, webdev, spreadsheet
canonical_url: https://www.syncfusion.com/blogs/post/simple-steps-to-validate-data-in-a-javascript-spreadsheet.aspx
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u1ax8kilmuwg1ew5i3le.png
---

Validating data is the process of ensuring the accuracy of the data. It ensures that the data meets certain requirements necessary for further data analysis. After validation, you can trust the data you use to be accurate, clean, and useful. Assuring the accuracy of the data you utilize is a proactive strategy to protect your most valuable data.

In this blog post, we will see how to enable the data validation feature in the Syncfusion [JavaScript Spreadsheet](https://www.syncfusion.com/javascript-ui-controls/js-spreadsheet "Link to JavaScript Spreadsheet"). By doing so, you can require users to enter only a specific type of data or value and keep them from entering invalid data in the cells.

## Where can you find data validation options?

On the **Data** tab of the ribbon, you’ll find the data validation dropdown button.

![Data Validation DropDown Button in JavaScript Spreadsheet](https://www.syncfusion.com/blogs/wp-content/uploads/2021/09/Data-Validation-DropDown-Button-in-JavaScript-Spreadsheet.png)<figcaption>Data Validation DropDown Button in JavaScript Spreadsheet</figcaption>

The Data Validation dropdown button lists the following options:

- [Data Validation](#data-validation)
- [Highlight Invalid Data](#highlight-invalid-data)
- [Clear Highlight](#clear-highlight)
- [Clear Validation](#clear-validation)

### Data validation

The JavaScript Spreadsheet supports the following data validation options:

- **Whole Number** : Restricts the cell to accept only whole numbers.
- **Decimal** : Restricts the cell to accept only decimal numbers.
- **List** : Picks data from the dropdown list only.
- **Date** : Restricts the cell to accept only date values.
- **Time** : Restricts the cell to accept only time values.
- **Text Length** : Limits the text length.

With this feature, you can set the validation rules in the Data Validation dialog like in the following screenshot.

![Data Validation Dialog](https://www.syncfusion.com/blogs/wp-content/uploads/2021/09/Data-Validation-Dialog.png)<figcaption>Data Validation Dialog</figcaption>

### Highlight invalid data

This feature highlights the cells that contain invalid data with a yellow background. The cells that failed to meet the data validation criteria are highlighted, including the typed and pasted values.

### Clear highlight

This feature clears the highlight from the invalid cells. You can also highlight the cells once again if you wish.

### Clear validation

This feature enables you to clear the applied validation from the specified range of cells or the whole worksheet.

## Adding data validation in JavaScript Spreadsheet through code

Also, you can add data validation in the JavaScript Spreadsheet through code using the [addDataValidation](https://ej2.syncfusion.com/documentation/api/spreadsheet/#allowdatavalidation "Link to the allowDataValidation property of JavaScript Spreadsheet") method and pass the required validation options with this method.

The following code example showcases the data validation feature with a gross pay calculation.

```js
/**
  * Data validation
  */

    //Initialize the Spreadsheet component.
    let sheet: SheetModel[] = [
        {
            ranges: [{
                dataSource: (dataSource as any).grossPay,
                startCell: 'A3'
                },
            ],
            name: 'Gross Pay',
            rows: [{
                        cells: [{ value: 'Gross Pay Calculation',
                        style: { fontSize: '20pt', fontWeight : 'bold', textAlign: 'center', backgroundColor: '#B3FFB3',
                        verticalAlign: 'middle'} }]
                    },
                    {
                        index: 13,
                        cells: [{
                            index: 7, value: 'Total Gross',
                            style: { border: '1px solid #A6A6A6', textAlign: 'center', verticalAlign: 'middle', fontWeight: 'bold'} },
                        {
                            index: 8, formula: '=Sum(I4:I13)', format: '$#,##0.00',
                            style: { border: '1px solid #A6A6A6', textAlign: 'center', verticalAlign: 'middle', fontWeight: 'bold'}
                        }]
                    }
            ],
            columns: [
                { width: 88, }, { width: 120 }, { width: 106 }, { width: 98 }, { width: 110 },
                { width: 110 }, { width: 110 }, { width: 98}, { width: 130}
            ]
        } ];

    //Initialize the SpreadSheet control.
    let spreadsheet: Spreadsheet = new Spreadsheet({
        sheets: sheet,
        created: () => {
            spreadsheet.merge('A1:I2');
            spreadsheet.setBorder({ border: '1px solid #A6A6A6' }, 'A1:I13');
            spreadsheet.cellFormat({ textAlign: 'center', verticalAlign: 'middle'}, 'A3:I13');
            spreadsheet.cellFormat({ backgroundColor: '#B3FFB3', fontWeight : 'bold'}, 'A3:I3');
            spreadsheet.numberFormat('$#,##0.00', 'H4:I13');
            spreadsheet.wrap('H3:I3');
            //Add Data validation to range.
            spreadsheet.addDataValidation({ type: 'WholeNumber', operator: 'LessThan', value1: '9', ignoreBlank: false }, 'G4:G13');
            spreadsheet.addDataValidation({ type: 'TextLength', operator: 'GreaterThan', value1: '3', ignoreBlank: false }, 'B4:B13');
            spreadsheet.addDataValidation({ type: 'Date', operator: 'GreaterThan', value1: '4/5/2021’, ignoreBlank: false }, 'C4:C13');
            spreadsheet.addDataValidation({ type: 'Time', operator: 'LessThan', value1: '6:00:00 PM', ignoreBlank: false }, 'F4:F13');
            spreadsheet.addDataValidation({ type: 'List', value1: 'Mon, Tue, Wed, Thu, Fri', ignoreBlank: false }, 'D4:D13');
            //Highlight Invalid Data.
            spreadsheet.addInvalidHighlight('G4:G13');
        }
    });

    spreadsheet.appendTo('#spreadsheet');
```

After executing the previous code example, we will get output like in the following screenshot.

![Validating Data in JavaScript Spreadsheet](https://www.syncfusion.com/blogs/wp-content/uploads/2021/09/Validating-Data-in-JavaScript-Spreadsheet.png)<figcaption>Validating Data in JavaScript Spreadsheet</figcaption>

## Reference

For more information, refer to the [Data Validation in JavaScript Spreadsheet demo](https://ej2.syncfusion.com/demos/#/material/spreadsheet/data-validation.html "Link to Data Validation in JavaScript Spreadsheet demo on web").

## Conclusion

Thanks for reading! I hope you now have a better understanding of the data validation feature in the Syncfusion [JavaScript Spreadsheet](https://www.syncfusion.com/javascript-ui-controls/js-spreadsheet "Link to JavaScript Spreadsheet") control. With this feature, you can easily keep users from entering invalid data and ensure data accuracy. Also, refer to the [Data Validation in JavaScript Spreadsheet documentation ](https://ej2.syncfusion.com/documentation/spreadsheet/cell-range/#data-validation "Link to Data Validation in JavaScript Spreadsheet documentation")for more information.

In the upcoming blogs, we will discuss other features. What else do you expect from a spreadsheet? Please share your thoughts in the comments section of this blog post.

If you’re already a Syncfusion user, you can download Essential Studio for JavaScript [product setup](https://www.syncfusion.com/account/downloads "Link to license and download page of Essential Studio") to try out this control. Otherwise, you can download a free [30-day trial](https://www.syncfusion.com/downloads/fileformats/verify "Link to the free evaluation of the Syncfusion Essential Studio").

If you have any questions about these features, please contact us through our [support forum](https://www.syncfusion.com/forums "Link to the Syncfusion support forum"), [Direct-Trac](https://www.syncfusion.com/support/directtrac/ "Link to the Syncfusion support system Direct Trac"), or [feedback portal](https://www.syncfusion.com/feedback/javascript "Link to Syncfusion Feedback Portal"). We are happy to assist you!

## Related blogs

- [Exploring Essential JavaScript Spreadsheet Freeze Panes](https://dev.to/karthickramasamy08/exploring-essential-javascript-spreadsheet-freeze-panes-36oc-temp-slug-3517347 "Link to Exploring Essential JavaScript Spreadsheet Freeze Panes blog")
- [JavaScript Spreadsheet Chart Feature Overview](https://dev.to/karthickramasamy08/javascript-spreadsheet-chart-feature-overview-503h-temp-slug-180120 "Link to JavaScript Spreadsheet Chart Feature Overview blog")
- [Formatting Essential JavaScript Spreadsheet: Conditional Formatting](https://dev.to/syncfusion/formatting-essential-javascript-spreadsheet-conditional-formatting-dg6 "Link to Formatting Essential JavaScript Spreadsheet: Conditional Formatting blog")
- [Easily Configure Syncfusion JavaScript UI Controls in Salesforce](https://dev.to/syncfusion/easily-configure-syncfusion-javascript-ui-controls-in-salesforce-4o32 "Link to Easily Configure Syncfusion JavaScript UI Controls in Salesforce blog")