Excel: DATA VALIDATION Function (Part 1)

Data Validation is a feature in Excel which is used to control what a user can enter into a cell. For example, you could use data validation to make sure a value is a number between 1 and 6, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters. Excel Data Validation allows you to limit data entries to a selection from a dropdown list and to restrict certain data entries, such as dates or numbers outside of a predetermined range.

Data Validation Options:

When a data validation rule is created, there are eight options available to validate user input.

Any Value - No validation is performed. Note that, if data validation was previously applied with a set Input Message, the message will still display when the cell is selected, even when Any Value is selected.

Whole Number - Only whole numbers are allowed. Once the whole number option is selected, other options become available to further limit input. For example, you can require a whole number between 1 and 10.

Decimal - Works like the whole number option, but allows decimal values. For example, with the Decimal option configured to allow values between 0 and 3, values like .5, 2.5, and 3.1 are all allowed.

List - Only values from a predefined list are allowed. The values are presented to the user as a dropdown menu control. Allowed values can be hardcoded directly into the Settings tab, or specified as a range on the worksheet.

Date - Only dates are allowed. For example, you can require a date between January 1, 2018 and December 31 2021, or a date after June 1, 2018.

Time - Only times are allowed. For example, you can require a time between 9:00 AM and 5:00 PM, or only allow times after 12:00 PM.

Text Length - Validates input based on number of characters or digits. For example, you could require code that contains 5 digits.

Custom - Validates user input using a custom formula. In other words, you can write your own formula to validate input. Custom formulas greatly extend the options for data validation. For example, you could use a formula to ensure a value is uppercase, a value contains "xyz", or a date is a weekday in the next 45 days. The settings tab also includes two checkboxes. Ignore blank - tells Excel to not validate cells that contain no value. In practice, this setting seems to affect only the command "circle invalid data". When enabled, blank cells are not circled even if they fail validation. Apply these changes to other cells with the same settings - this setting will update validation applied to other cells when it matches the (original) validation of the cell(s) being edited.

Alert Style and Behavior in DATA VALIDATION:

Stop

Stops users from entering invalid data in a cell. Users can retry, but must enter a value that passes data validation. The Stop alert window has two options, Retry and Cancel. Warning- Warns users that data is invalid. The warning does nothing to stop invalid data. The Warning alert window has three options: Yes (to accept invalid data), No (to edit invalid data) and Cancel (to remove the invalid data). Information- Informs users that data is invalid. This message does nothing to stop invalid data. The Information alert window has 2 options: OK to accept invalid data, and Cancel to remove it. Data validation with a custom formula:- Data validation formulas must be logical formulas that return TRUE when input is valid and FALSE when input is invalid. For example, to allow any number as input in D6 cell, you could use the ISNUMBER function in a formula like =ISNUMBER(D6)