Subscriber only lesson.
Sign up to this course to view this lesson.
Working with Data Validation lists
Where/when to use the technique
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 tab --> 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 comma delimited list
- Provide a list of valid options separated by commas in the Source field
- Source: Dog,Cat,Hamster
- Remember these options are case sensitive. If you’d like people to be able to enter Dog or dog, you’ll need to provide both those options
- Source: Dog,Cat,Hamster,dog,cat,hamster,DOG,CAT,HAMSTER
Providing a list from a range
- Instead of using a comma separated list, you can also provide the range where a list exists
- I.e. if you have a list of valid options in B5:B25, you can provide this address instead
- Source: =$B5:$B25
- We recommend using absolute cell references so that you can copy your data validation rules without issue
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
- And 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 --> Define Name --> +
- Enter the name and select the range
- Use this name in your Data Validation list Source field
Lesson notes are only available for subscribers.