Data validation is a feature in Excel 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. Data validation can simply display a message to a user telling them what is allowed as shown below:
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 is implemented via rules defined in Excel’s user interface on the Data tab of the ribbon.
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 tab defines a message to display when a cell with validation rules is selected. This Input Message is completely optional. If no input message is set, no message appears when a user selects a cell with data validation applied. 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. When style is set to Information or Warning, a different icon is displayed with a custom message, but the user can ignore the message and enter values that don’t pass validation. The table below summarizes behavior for each error alert option.
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.
Whole Number – only whole numbers are allowed. 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.
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. 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. 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 validation of the cell(s) being edited.
Simple drop-down menu
You can provide a dropdown menu of options by hardcoding values into the settings box, or selecting a range on the worksheet. For example, to restrict entries to the actions “BUY”, “HOLD”, or “SELL” you can enter these values separated with
Data validation to circle invalid entries
Once data validation is applied, you can ask Excel to circle previously entered invalid values. On the Data tab of the ribbon, click Data Validation and select “Circle Invalid Data”:
For example, the screen below shows values circled that fail validation with this custom formula: