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:
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.
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:
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:
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.
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:
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.