Saving Time (and Paper) with Automated Assessment using Excel – Three Rivers TESOL Fall Conference 2019

Hello, Three Rivers TESOL!

My presentation is about two spreadsheet templates I have developed to save me time in assessing student work.

Link to spreadsheets (ZIP file)

Automatic Grader and Error Analysis.xltx

Think of this spreadsheet like a Scantron/bubble-sheet grader. It enables you to very quickly score and generate reports on tests. It’s specialized for multiple choice and true/false questions, but it is compatible with other question types as well (see tips below).

What the instructor does:

  1. Enter the scoring key for the exam in row 4.
  2. Enter any subscore categories you want to calculate in row 39 (optional).
  3. Enter students’ names in column A (optional).
  4. Enter students’ answers across rows 5-24.

The spreadsheet calculates:

  • Whether any specific answer is right or wrong (color-coded green = correct, red = incorrect)
  • Each student’s raw score and percentage for the full exam
  • An error analysis of each item showing the percentage of students who answered each item correctly and breaking down which distractors were chosen the most often
  • A subscore breakdown (if you entered subscore categories in step 2 above)

Extra tips and notes:

  • When I look at the error analysis table (Table 2), I check for any questions with an accuracy rate of 50% or less. Then, I check to see if the errors are distributed randomly or if one distractor got the majority of responses. I use this information to help me decide if the question and/or distractors should be revised.
  • If some of your answers aren’t multiple-choice or true/false, no problem! You can type numbers and words into the key and into student answer cells, and the spreadsheet will still match them and count them for credit correctly.
  • For questions where answers may vary (such as short answer), I suggest putting them in the spreadsheet as T/F questions with the correct answer as T. If the student gave an acceptable answer, put T in the spreadsheet so it counts for credit. If a student gives a bad answer, record their answer as F so their score does not count for credit.
  • The spreadsheet assumes that every question is worth exactly one point. If you have a question worth two or more points, the spreadsheet will not calculate the correct final score. You can work around this by entering the question twice into the spreadsheet. (For example, if question 10 is worth 2 points, you can have two columns called “10”, each with the same key and the same student answers, to ensure it’s counted for a total of 2 points.)

Rubric to Email Grader.xltx

This spreadsheet is used for speaking and writing activities, i.e. performance assessments with multiple rubric categories. It vastly streamlines creating feedback reports, calculating grades, and returning everything to students.

What the instructor does:

  1. Enter the instructor’s own name (A1) and the name of the activity (B1).
  2. Enter the rubric categories (C1-H1) and how many points the categories are worth (C2-H2).
  3. Enter students’ names and email addresses (columns A and B).
  4. For each student, enter their subscores according to the rubric and type any additional comments you have (columns C-I).
  5. Click the “Create Email” link next to one of the students (column J)
  6. Copy and paste in your automatically-generated feedback email (column K)

What the spreadsheet does:

  • Performs all of the math for you to generate:
    • total raw score
    • total percentage
    • subscore percentages
  • Generates a simple feedback email, pre-addressed to the student and with subject line pre-filled in, which provides all of the student’s score information as well as your prose feedback

Extra tips and notes:

  • For the Additional Comments column, it’s a little clunky to write long paragraph-sized messages directly into a spreadsheet cell. For greater convenience, you may want to type your comments in a separate text editor (like Word, Notepad, or TextEdit) and then paste your paragraph into the cell when you’re done typing it.
  • I suggest also attaching the student’s original work (written work or audio recording of a speech) to the email.
  • It may also be helpful to attach a blank version of the “paper” rubric that you otherwise would have filled out by hand.
  • The cells in column K generate the email message via a very complicated formula which is easy to break, so if you want to add anything or change the wording, it’s safest to copy and paste the message somewhere else first, then make any necessary changes to the text.

Using Socrative for classroom comprehension checks and formative assessments

Socrative is a free(mium) student response system service that enables students to respond to questions using their phones or any other internet-connected device. I’ve been using the free version in my language classrooms for three years, and for the past few months, I’ve also been trying out the paid premium version, Socrative Pro, thanks to a small grant from the Robert Henderson Language Media Center.

I use Socrative in three main ways:

  1. To collect short written work that will be shared and discussed with the class– especially for sentence-level writing practice!
  2. To track the class’s overall accuracy on specific skills being taught
  3. To convert “paper” activities (including items in the textbook or on handouts) into interactive digital activities

Socrative offers three question types: multiple choice, true/false, and short answer. Here’s an example of a short answer question as viewed from a student’s perspective:

Teachers see students’ responses immediately as they come in. Here are two examples of “results” screen: one for a short-answer activity and one for a multiple-choice activity.

For short-answer quizzes, teachers can click into specific questions to see a list of student answers:

Multiple-choice and true/false questions consolidate answers into an attractive bar-graph display:

In addition, teachers can choose correct answers for multiple-choice and true/false questions. For example, here is what the teacher sees in the quiz-building interface:

And this is what a student sees before and after submitting an answer for the above question. Note the immediate corrective feedback:

The way I implement activities in my classes is that I have a lecture slide template that I can quickly copy and paste into my slide deck for the day. I then make any necessary changes to the slide. Here is an example of one of my slides:

Here’s an example of a sentence-writing task. First, I display the slide to my students to help them join the activity:

Then, after all the students have joined the activity, I put up a timer and monitor student progress on the Socrative dashboard:

And finally, once time is up, we discuss answers as a class, just as you would if students had written sentences on the board:

All of the features shown above are available for free. However, Socrative also has a premium paid service called Socrative Pro. The details for Socrative Pro differ slightly for K-12 vs. Higher Ed contexts, but the differences and features are summarized below:

Source: Socrative

I was awarded a small grant from the Robert Henderson Language Media Center to try out Socrative Pro and report back on its suitability for language teaching.

I’ve found Socrative’s basic (non-Pro) features to be an excellent fit for occasional use in the language classroom. In most language courses I teach, I use Socrative about once or twice per week. On curriculum evaluations, students have consistently rated Socrative activities as being one of the most helpful and useful things we do in the classroom, and it’s not hard to see why. For multiple-choice questions, students can get immediate right/wrong feedback along with an explanation of the correct answer; and for open-ended written activities, students enjoy all the benefits of sharing their work with the class and getting formative feedback, but without the anxiety many feel in personally going up to the board to write out their response by hand.

So to sum up, Socrative’s free version works wonderfully in the language classroom.

How about the paid version? To be honest, although there are some nice quality-of-life improvements in the Pro version, Socrative Pro doesn’t have any “must-have” features for my needs. Perhaps more importantly, I don’t think the Pro version offers my students any added value versus the basic version. However, of the various Pro features I’ve tried, I’ve most enjoyed using the “multiple rooms” feature, which enables me to have a separate “room” for each of my classes. Rooms act sort of like folders: they keep activities and results from one class separate from those of other classes. If you’re a pretty heavy user like me, keeping your classes separated from each other can help prevent clutter and confusion when you use the website. Plus, the feature enables you (in theory) to run multiple activities at the same time (one activity per “room”). This could be useful if you want to use Socrative to collect homework activities in your classes– something I’ve never done, but which is a plausible use case.

Full-class review games using students’ smartphones (TiLT Forum 2018)

Hello, TiLT Forum!

My presentation is about two free websites that enable teachers to run full-class review games: Quizlet and Kahoot.


Quizlet.com is a free website that hosts millions of sets of student- and teacher-created flash cards.

Quizlet supports creating flash cards in virtually any language, including those with non-Latin alphabets (e.g., Chinese) or right-to-left writing direction (e.g., Arabic and Hebrew). In addition, Quizlet has text-to-speech functionality in 17 languages, including English, Spanish, French, German, Chinese (Simplified and Traditional), Portuguese, Arabic, Italian, Japanese, Korean, Russian, Turkish, Dutch, Greek, Swedish, Finnish, and Romanian.

In addition to studying your material as flash cards, you can play various games and do other types of learning exercises.

Here is a study set that I created for one of my classes recently:

https://quizlet.com/216332939/real-reading-1-chapters-1-12-flash-cards/

Quizlet Live is a new game mode offered by Quizlet.com. It’s a team game designed to be played in the classroom. Students work in groups of 3 or 4 and are incentivized to answer carefully and to discuss and collaborate with each other. Slow and steady wins the race.

Try out a demo of the Quizlet Live game here to see how it works: https://quizlet.com/livedemo

Setting up a Quizlet Live activity:

  1. Log into Quizlet.com. (This is a mandatory step!)
  2. Go to your Account Settings page and make sure you are in the system as a Teacher, not as a Student (you only need to do this one time):
  3. Open any Quizlet card set (or create your own).
  4. Click on the “LIVE” button on the card set you wish to play Quizlet Live with.
  5. Click the “Create Game” button.
  6. Instruct students to take out their phones (or other internet-connected devices) and go to quizlet.live (note that the address is .live and is NOT the same as quizlet.com).
  7. Instruct students to enter the six-digit join code and enter their names.
  8. After all students have joined the game, click the buttons on the screen to run the game.

Kahoot! is a review game somewhat similar to Quizlet Live, except that Kahoot! is a highly competitive game that incentivizes students to answer as quickly as possible.

Here is an example game I created: https://play.kahoot.it/#/k/68b27a0f-776b-42ba-b896-e8672646a0d2

Setting up a Kahoot! activity:

  1. Create an account at kahoot.com
  2. Under “Create new Kahoot!”, choose “Quiz”
  3. Complete the form (title, description, etc.) and click the “Ok, go” button
  4. Click the “Add question” button to create your first question.
  5. Type your question and set the time limit / points options.
  6. Type up to four answer choices in the boxes near the bottom of the screen.
  7. Click on the checkmark next to an answer to mark it as correct. (You may mark more than one answer correct.)
  8. Click “Next” at the top right of the screen to go back to your quiz.
  9. Either…
    1. Click the “Add question” button to add another question
    2. Click the green “Save” button at the top right of the screen to save your quiz
  10. After you click “Save”, you are given options to edit, preview, play, or share your new Kahoot quiz.

Running a Kahoot! activity:

  1. Click on the “My Kahoots” button near the top-left of the page. (If you don’t see the “My Kahoots” button, try clicking the purple button with three lines near the top-right of the screen.
  2. Click the “Play” button next to the Kahoot! quiz you want to run in class.
  3. Choose either “Classic” or “Team mode”, and set any other game options you wish to use. (One popular game option is “Randomize order of answers”, for example.)
  4. Make sure students can see your computer screen via the classroom projector/monitor.
  5. Instruct your students to go to kahoot.it (not .com) on their phones, mobile devices, or laptops. Instruct them to type in the game-pin which has appeared on the classroom screen.
  6. Click “Start” when everyone is ready.
  7. Enjoy! After the activity, you can download all students’ answers from the “My results” section of the website (click on your username at the top right of the webpage).