Data Validation In Excel

Data validation is a feature in Excel used to control what a fanatic can enter into a cell. For example, you could use data validation to make unlimited a value is a number together plus 1 and 6, create sure a date occurs in the neighboring 30 days, or make sure a text right to use is less than 25 characters.
Data validation can handily display a declaration to a user telling them what is allowed as shown asleep

Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like this:

data validation error alert invalid product code example

In addition, data validation can be used to present the user with a predefined choice in a dropdown menu:

This can be a convenient way to give a user exactly the values that meet requirements.

Data validation controls

Data validation is implemented via rules defined in Excel’s user interface on the Data tab of the ribbon.

Data validation controls on the data tab of the ribbon

Important limitation

It is important to take taking place that data validation can be easily defeated. If a devotee copies data from a cell without validation to a cell behind data validation, the validation is destroyed (or replaced). Data validation is a fine habit to tolerate users know what is allowed or usual, but it is not a foolproof habit to guarantee input.

Defining data validation rules

Data validation is defined in a window with 3 tabs: Settings, Input Message, and Error Alert:

Data validation window has three main tabs

The settings tab is where you enter validation criteria. There are a number of built-in validation rules with various options, or you can select Custom, and use your own formula to validate input as seen below:

Data validation settings tab example

The Input Message credit defines a message to display in addition to a cell taking into consideration validation rules is selected. This Input Message is very optional. If no input message is set, no notice appears once a adherent selects a cell subsequent to data validation applied. The input statement has no effect regarding what the fan can enter it favorably displays a message to permit the user know what is allowed or customary.
Data validation settings tab
The Error Alert Tab controls how validation is enforced. For example, when style is set to “Stop”, invalid data triggers a window with a message, and the input is not allowed.
The user sees a message like this:
Example data validation error alert message
When style is set to Information or Warning, a swap icon is displayed as soon as a custom message, but the devotee can ignore the message and enter values that don’t appendix validation. The table out cold summarizes behavior for each error lithe choice.
Share This Post

One comment

Leave a Reply

Your email address will not be published.