Subscriber only lesson.
Sign up to this course to view this lesson.
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.