Sometimes when you are creating a spreadsheet that is going to be updated by more than one user, you only want a specific set of values to be available for a cell. One good example of this is a status spreadsheet, where your team members will update the status of their work daily. However, you only want to accept the values of “Not Started”, “In Progress”, and “Complete”. You could just tell them that those are the values to use, but there is always the chance that they will forget and input something else. What you need to do is give them a drop down list of those available choices. The way to do this is by using List Validation on that cell.

Validation on a cell allows you to specify what kind of values are allowed to populate that cell. In particular, List Validation will allow you to specify a range of cells from which to provide input values that the user can select from via a drop down list when the user clicks on the cell. To do this, follow the steps below:

1. Create your list of input values and give it a Name. Let’s start with our example list, shown below:

   

   As you can see, we have our input values in cells A1-A3. To give them a Name, simply highlight the cells (1) and then type the name you want in the Name input box (2).

   

2. Now that the input values are set, we can add the validation to the cell. To accomplish this, first click on the cell you want to put the validation in. Then, go to Data and then Validation. This menu will be displayed:

   

   Under the Allow: drop down, select List (1). The Source: text box will now be enabled. In this field, type “=Status” (2). Click “Ok” (3). Basically, this is setting the list of possible input values to the range of cells defined by “Status” that we set previously. These steps are illustrated below:

   

3. The Validation List is now setup on the cell. The sample cell should now have a drop down arrow on it. If you click on it, the following screen should be displayed:

   

   The user will only be able to input a value into this cell that is on the list shown.

Usually when setting up validation like this on cells, I’ll put the input values on a separate spreadsheet that I will hide and lock. This way only I can change the set of input values.

This is only a basic lesson in setting up Validation Lists. In the Data Validation dialog box, there are options to configure error messages, ect. that respond to user input. Play around with it until you find something that works for you. There are also other types of validation available that will be covered in future articles.