This is the fourth article in a series on helping teachers get started using spreadsheets in the classroom.
Drop down menus
Have you ever been filling out a form online and a menu pops up giving you options such as a date or country name? You can actually do a similar thing in spreadsheets. It’s called Data Validation. As a teacher, you might want to use this when you are putting in grades, doing attendance, or maybe grouping students for small group instruction. Data Validation also helps speed up your data entry and reduces human error.
Popular uses for teachers
I will show you some of the fastest and easiest ways to get started. You can apply these ideas to your current work right away. Some possible suggestions for data validation for teachers are:
1. Lists: attendance, yes/no
2. Numbers for grades or scores
3. Rubric comments number categories or descriptions
4. Categories or groups (red, green, blue) (high, mid, low)
Drop downs for attendance
When taking attendance I have my roster names in the first column and I have some dates going across in the top row. If you know what you want to type in, like P for present, A for absent and so on, you could just type these in each day for each student. What if you have a situation where you have someone else taking attendance for you (say an assistant teacher or student) and you don’t want them to enter information incorrectly. We can use a drop down.
1. Highlight the cells where you want your dropdown to go.
2. Go to the data menu and choose data validation.
3. In the “criteria” section, select list of items.
4. In the box to the right, type in your items with a comma between each one.
5. Click “save” and then you’ll see the dropdown arrows in each cell.
Show warning or reject input
There’s also an option to show warning or reject input. If you select reject input it will only allow you to put in information that you put into your list of items. A good thing about this is that it will not allow for any different information to be entered. Also, if you type a lowercase p, but your list calls for an uppercase P, the spreadsheet will update this automatically.
The down side to reject input is that you cannot add any new items to your list unless you go back to the data validation menu and add. If you choose “show warning” anything can be typed in the cell. You will get a small red triangle in the corner of the cell if the item typed doesn’t match your list criteria.
If you click on “show validation help text” you can create a pop up menu for the user as well. Type in whatever text you would like to show up.
Grades and test scores
You might have a grading system where you give letter grades. Here, you can create a list of the grades such as A,B,C. You might have a rubric where you assign points and half points for scores. You can put these scores (0, 0.5, 1, 1.5, 2, etc) in a list. If you select “reject input” this will eliminate accidental scores in your data. Choosing a list of numbers will not create a dropdown, but you can limit the type of information to be input.
Notes & comments
I often find when grading student work and writing comments, I will write similar comments for some students, depending on the topic and how they did. Instead of writing the same comment over and over, or trying to remember what I said, I will use data validation. For this method, I will choose to enter data from a range of cells. Before I can set the data validation, I need to get my comments into the spreadsheet. I create a new tab in my spreadsheet and name it DV for data validation. In here I will choose a column to write in my comments.
Now, I’ll go back to the sheet where I want to put the comments. Highlight the cells and navigate to data validation. In the Criteria section, choose “list from a range.” Click on the small grid in the box that says “enter range or formula.” Then navigate to the DV tab and highlight the comments. This will be your dropdown menu. If you want to be able to add more comments and have them show up in your dropdown, you can do this.
When you highlight the range for your comments, the address will be something like DV!A1:A21. This range is from row 1 to row 21. If you put another comment in row 22, it will not show up in your dropdown. Easily change this by adjusting the range to say DV!A1:A. Taking off the last number will make it so that the entire row shows up in the dropdown. Therefore, you can continue to add comments to you DV tab as needed.
Select Date as a data validation. There won’t be a dropdown menu, but when you double-click on the cell, a calendar will pop up. You can always go to the format menu to change the way the date is displayed.
There are so many more uses for Data Validation. These are just a few ideas to get you started playing with the idea. In my next article, I will be talking about something that can go hand in hand with data validation: conditional formulas. This is when you can make the color of text or the color of the cell change depending on the data. You can use conditional formatting to quickly see what is happening with your data. I look forward to sharing this tip with you soon!