How to Create a Data Validation Form

When inputting data into a spreadsheet, often you will find yourself having to type the same data into cells. That's where Data Validation comes in handy. Instead of typing the same thing over and over again, you can turn the cells into drop-down lists. That way, you could just quickly select an item from the list and move on to the next entry.

For example, suppose you had a column heading called "Student Grade". Even though there are only two grades available, Pass and More Work Needed, it can become quite laborious having to type either one or the other. Spelling mistakes will become increasingly more likely the more times you have to enter the grades. Much better to have a drop down list where you could select the grade. And no more spelling mistakes!

Data Validation

We'll now construct a spreadsheet with drop-down lists. The one we're going to construct takes us back to the classroom and our students from previous section.

So start a new spreadsheet, and format it to match the one below:

Before we can turn the cells in an entire column into drop down lists, we need some data to go in the lists. So starting at cell F2, add the following to your spreadsheet:

The data in columns F, G and H will be going into our lists. We can then hide this data so that it's not messing up our spreadsheet. You'll see how to do this later. But we can now turn Columns A, B and C into lists.

To turn the cells in an entire Column into a list, do the following:

• 
  
  Highlight the whole of Column A by clicking on the letter A at the top of the column


• 
  
  With the whole of Column A highlighted, click on Data from the Excel menu bar


• 
  
  From the drop down menu, click Validation


• 
  
  The following dialogue box appears:

• 
  
  Make sure the Settings tab strip is selected


• 
  
  Click the black down arrow just to the right of “Allow: Any Value”


• 
  
  A drop down list appears


• 
  
  Choose List


• 
  
  A Source box appears on the dialogue box

The Source is the data that is going into your list. So you need to select the cells with the students in them. To select the cells with the students in them, do this:

• 
  
  Click on the icon to the right of the Source text box:

• 
  
  When you click the icon, the dialogue box contracts


• 
  
  Click inside cell F2 on your spreadsheet


• 
  
  Hold down your left mouse button, and drag to cell F9


• 
  
  Then click on the icon again to expand the dialogue box


• 
  
  The images below show the highlighting and expanded process in action

Click the icon and highlight F2 to F9

Click the icon again to expand the dialogue box

• 
  
  If you have done it all correctly, your dialogue box will now look like this one:

So the Validation criteria should be: "Allow List", and the Source should be = $F$2:$F$9.

Click OK when your dialogue box reads the same as the one above. The cells in you entire A column will now be drop down lists. Test it out. Click on cell A3, for example. It should look like this:

If you click the black down arrow, you should see your list of students. Like the one below:

Click on any student in the list. The student appears in cell A3. Click on another cell in column A and try it again.

I'm sure you'll agree that it's much better than having to type out a student's name over and over again.

Except we have a slight problem. If you click inside cell A1 you'll see that this too has a drop down list. Clearly we don't want this to happen for our heading. To turn off the list in cell A1, do the following:

• 
  
  Click inside cell A1


• 
  
  From the Excel menu bar, click on Data


• 
  
  From the drop down menu, click Validation


• 
  
  From the Settings tab strip of the dialogue box, change "Allow List" to "Allow Any Value"


• 
  
  Click OK

Time for you to try it alone. Change the Subject and Grade columns into drop down list, and then turn off the list for the headings cells B1 and C1.

The drop down lists for the Subject and Grade columns should look like these when you're done: