I’m always surprised how little teachers use Google Sheets (or Excel) because it has so many features that help make all the little tasks we need to do as teachers less time consuming. One of these is Data Validation.
Data validation is a neat little feature that adds a drop down menu to your cell so that you have some pre-set choices. You set the choices. Highlight the cells in which you want the drop down menu to appear, then in the tool bar choose Data>Validation. From there, I generally choose “List of Items” and list what I want my choices to be. (You can also require that the cell be filled in with a number or a certain text, but I never use those.) Click to view a video that walks you through the steps.
Applications for Education
When I give an assignment, I have a general idea of all of the items I may need to comment on. For example, I know I want to give at least one positive comment, at least one “work-on” comment and then tell them what to do next. I’ve used data validation to give feedback faster, by setting up data validation with the most common comments I think I’ll use. Then I don’t have to type them in one by one, over and over for each student. It makes the mechanics of giving feedback much faster. I can still type in a unique comment if needed, but I don’t waste time typing in the same thing multiple times. I use a Google Sheets Add-On like Autocrat or FormMule to send or share the feedback with the students with a few clicks.
Students who can’t produce their own language just yet, can create stories using data validation. Here’s an example I made with vocabulary from the first hours of French. Click on the image to make your own copy and to see all of the choices. It’s like structured sentence creation.
I use data validation to set the choices for my homework choices sheet. Students click on the arrow and it gives them a list of approved choices. I’ve also used it for Flipgrid review.
You probably keep attendance in your classes in your school’s student information system (SIS), but data validation can be a quick way to keep track of attendance for clubs or extra curricular activities or anything else you might need to “check-off” over a period of time.
I use a spreadsheet to grade my end of unit assessments. I have four columns for each of the four sections. I use data validation to put in the possible scores and then as I’m listening to students or reading what they wrote I use the drop down menu to input their score. I set the spreadsheet up to automatically add up the points. It’s much easier to click as I’m walking around with the iPad listening than to type.
Go ahead, open a Google Sheet and see how Data Validation can save you time!
Grading has never been ____.
Yesterday Google quietly announced a new Forms feature that allows you to grade by question and allows for streamlined short answer grading. Now you can create short answer/fill in the blank questions and grade them in Forms itself. You provide an answer key (currently case sensitive) and if the student puts that exact answer, Forms grades it automatically. All other answers can be graded quickly with clicks.
With this new update you can easily add short answer and fill in the blank questions to your Forms quizzes.
Feedback is best when it is ____.
The new forms update also allows for individual feedback for questions and even allows you to post a link and test in your feedback. For example you could link to a video or screencast re-teaching the skill. Alternately if the student showed mastery to a video that would challenge them to do more.
I’ve been taking advantage of our “no school because of smoke” days to re-do my college syllabus for French 101. I’ve had a few ideas in my head since the end of last semester that I’m finally processing. I’ve taught this class at least seven semesters and never the same way twice. While I haven’t found that teaching community college French is that different from teaching high school French, there are some significant considerations as I update and tweak.
- There ain’t no time for nothing. I’m “supposed” to cover 15 lessons in 16 weeks. And if I have a Monday/Wednesday day class, I always lose at least one day due to holidays. Yes, I know the whole bit about the difference between “covering” and “mastering.” (For more on that see my post on why I’m not ready to ditch my textbook.) Nevertheless, the pacing is fast. I’ve reduced the number of lessons we cover and I’ve paired those down to what I feel is essential for communication. I am constantly trying to come up with ways to maximize class time (more TL, duh) and increase authentic, engaging and meaningful activities outside of class.
- In any class, I will have students who have never heard a word of French and students who have 2, 3 even AP level French at the high school. I’ve been moving towards a proficiency grading model for several semesters, but this poses the question- if Novice High is the goal, the students who had French in high school could easily be at that level on day one. I don’t want to give them the impression that they don’t have to do anything because I will call BS that for any one of them, their French would not improve sitting in a class and hearing and speaking more French, even if it isn’t at an advance level.
- There are students for whom this is their first class back to school after 25 years. Their affective filter is off the charts. Tell a high school student to do something new and they are like OK, whatever and go back to their phone. Tell the student back to school for the first time in 25 years the same thing and you can visually see the panic taking over.
- A mix of students. Some of these students were accepted at major universities, but couldn’t go because of financial reasons. Some of them are just out of high school and will drop out of community college in a semester. Some of them are only there until they get their financial aid. (That one boggles me, but it’s true. There’s a certain amount of attrition after financial aid is released.) In the night classes, most of them have full time jobs and families. And some of them are finally grown up and ready to learn. I want to have a class that is mindful of the fact that sometimes French class isn’t the most important thing going on in their life, while respecting the students who are committed to being there every session.
Here’s what I’ve decided to do this semester:
Grading: 50% Proficiency – based on three assessments at the end of each unit. I am comfortable with the percentage because then the student who had two years of high school French will have to work on writing (homework) and come to class in order to earn a passing grade. At the end of the first unit I’m going to forego what would normally be a formal assessment and instead, make appointments with the students so we can talk about where they would score and what they could do to make it better. I will give them personalized “tasks.” For the student who had French before I will tell them what they need to do to go to the next level for them. This is where I plan on combating the student with two years of high school French who insists on pronouncing the “s” in “est” and the -“ent” in “parlent.”
I’m also giving up quizzes. They just weren’t worth the time it took up in class. Instead I’m going to “grade” their homework. (Well, let’s be clear, the textbook website will grade their homework.) They can redo any homework exercise as many times as they want until they get 100%. It’s about getting better, right? The time I gain back from quizzes, I’ll save for the appointments and more TPRS.
I’m going to use the new quiz feature in Google Forms to do an end of the the lesson informal assessment. I’ve had students do a weekly reflection for a couple of semesters, asking them to rate their confidence on their ability to complete the can do statements for the lesson and then asking them to do something that I would give feedback on. It’ll be a short, ungraded (not in the gradebook) assessment that will give me an idea of what we need to still work on and I’ll use the feedback option in the quiz to lead students to what they might need to review. I’m going to continue to ask them to do something so I can give feedback.
Last semester I started working in some TPRS. Or just S because I didn’t have college students stand up and do gestures. I want to continue to do this and add more because of course, it’s effective. Feedback I got last year was that some of the students were less comfortable (i.e. on the edge of panic) because they didn’t have anything to “review” for those lessons. This semester I’m going to use screencasting to record the stories and EdPuzzle for checking for understanding for the students who want more practice.