Working with Data Validation lists.
When to use
Use to restrict users ability to enter invalid data in cells by providing them with a drop down list of valid options.
- Select the cell you’d like to be validated
- Go To Data --> Data Tools --> Data Validation
- Choose to allow a List
- Make sure “Ignore Blank” is checked if you’d like to allow users to clear the input cell
Providing a list
- To use a hard coded list, enter the values separated by commas. Eg: Dog, Cat, Hamster
- To use a list contained in a range, enter the address of the list. Eg: =$B5:$B25
More robust list sources using named ranges
- It is a good practice to name your input lists instead of using cell referencing. This allows
- Use of table elements in data validation
- An easy way to jump to the validation list (via the Name box)
- To do this:
- Select the range of valid items you want to show in your validation list
- Go to Formulas --> Name Manager --> New
- Enter the name and select the range
- Use this name in your Data Validation list Source field
Hints & tips
- When creating a list against a range, use absolute cell references so that you can copy your data validation rules without issue
- Customize the Error Alert tab to provide useful feedback if someone enters a restricted value
- List items are case sensitive. If you want to allow Dog or dog, those items both need to be contained in the list
Lesson notes are only available for subscribers.