.. or, how to restrict input to items in a list.
Have you ever had the situation where you need to select one item from a pre-defined list? Or, have you found that when typing in a regular value that you misspell it?
Excel provides a facility called Data > Validation that can help you avoid these problems.
Here's an example:
When you make the cell, containing Data > Validation, the active cell, a dropdown box appear on the right-hand side. When you click on the dropdown, a list appears. Excel only allows you to select/input a value that's in the list.
This page will show you how to set up and use Data > Validation.
First we need a list. I've set up one that has four entries - the countries that make up the United Kingdom. I've also given it a range name - UK. This isn't essential but is good practise.
Highlight the cell, or cells, where you want to restrict the entry to one of
the values in your list.
First, click on the Allow dropdown and select List.
Notice that the "Data" box has changed to "Source".
We can either type in the list's address or "point and shoot" with the range
Alternatively you can type in the range name preceded by an "=".
You will notice that the cell now has a dropdown on the right-hand side.
Click on the dropdown and you will see the list that we highlighted.
Now, you can select a value from the list. here we've selected Scotland.
Try typing in a value that's not in the list.
For further examples of Data > Validation, see: