Free computer Tutorials
|HOME||Stay at Home and Learn|
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!
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:
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 the icon and highlight F2 to F9
Click the icon again to expand the dialogue box
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:
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:
In the next part, you'll learn how to display error messages. That way, you can control what data your users are allowed to enter.