Data Validation

.. 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.

How?

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.
From the menu, click on Data > Validation
You will be presented with the following dialog box.

First, click on the Allow dropdown and select List.
As you can see, there are other choices you can make, but for this demonstration we'll restrict ourselves to the List option.

Notice that the "Data" box has changed to "Source".
Click on the Source box, which allows us to select a list.

We can either type in the list's address or "point and shoot" with the range selector.
The range we've selected is bounded by the striped border.
Finally, click on "OK".

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.
Excel recognises that the value is not in the list and retains the current value.

For further examples of Data > Validation, see: