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.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Data Validation Lists.xlsx26.2 KB Data Validation Lists - Completed.xlsx
26.2 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.