Locked lesson.
About this lesson
Use to restrict users' ability to enter invalid data in cells by providing them with a drop-down list of valid options.
Exercise files
Download this lesson’s related exercise files.
Data Validation Lists - Begin.xlsx29.5 KB Data Validation Lists - Complete.xlsx
26.9 KB
Quick reference
Data Validation Lists
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.
Instructions
Basic setup
- 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 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 --> Name Manager --> New
- Enter the name and select the range
- Use this name in your Data Validation list Source field
Lesson notes are only available for subscribers.