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.

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).

Recorded Speaking Activity (RSA): Pedagogy, Implementation, Evaluation and Creation

I had the pleasure this past weekend to co-present about the Recorded Speaking Activity we do at my institution. Check it out below!

I presented earlier this year at TESOL International about the benefits of using Google Drive for collaborative activities, and incidentally, this is a demonstration of those benefits, as my colleagues and I used Google Slides to create this presentation together. I don’t think I posted my TESOL International presentation on this blog, but I gave a similar presentation at Three Rivers TESOL 2016.

4/3/2 in the 21st Century: Formative Assessment of Fluency through Digital Recording

Hello, Three Rivers TESOL! See below for my presentation and related resources.


Abstract

Maurice (1983) pioneered a 4/3/2 fluency activity for intermediate to advanced learners in which each student speaks on the same topic three times in shrinking time frames: four minutes, three minutes, and finally two minutes. Shrinking the time frame places pressure on students to use time economically by avoiding hesitations and increasing fluency of speech. While Maurice’s original activity had students speaking with partners and involved little teacher or student assessment of performance, the ubiquity of cheap digital recording technology (computers, tablets, smartphones, etc.) enables teachers of the 21st century to reconstruct the activity as a formative self-assessment with teacher feedback.

In my version of the activity, students watch a humorous short film full of concrete, reportable events two times (Eggleston, 2000). Students then record themselves narrating the events of the film for two minutes, after which they listen to the recording to notice their hesitations and self-assess their fluency. Students then record the same narration in one minute thirty seconds, followed by listening and self-assessment. Finally, students record the narration in one minute and again listen and self-assess. Students discuss and reflect upon their experiences, especially on the extent of self-perceived improvement. The teacher collects the final recording in order to review it and provide written feedback targeting hesitations, word linking, or other fluency-related performance targets.

Works cited:


Here are some more videos appropriate for this activity:

Four short videos to help your language students improve their fluency

Enjoy!