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

Quizlet Live is a new game mode offered by 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:

Setting up a Quizlet Live activity:

  1. Log into (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 (note that the address is .live and is NOT the same as
  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:

Setting up a Kahoot! activity:

  1. Create an account at
  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 (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).

Mac computer lab headaches! A script to handle keychain errors, docks, and users logged in in the background

I’m primarily a university-level ESL instructor. However, one of my responsibilities is to manage our English Language Institute’s computer lab, which has 20 iMacs. Here are some of the issues I’ve struggled with:

  • Keychain errors. My university uses Active Directory for usernames and passwords, and Macs don’t play nicely with Active Directory. Every time that students and faculty are forced to change their passwords through the university’s web interface, it breaks their keychains on all campus Macs, since the login password is no longer the same as the keychain password. Dealing with keychain errors is annoying enough under one-device-per-person circumstances, but when people use multiple computers over the course of multiple semesters and change their password multiple times, the problems snowball out of control.
  • Messing up the dock. It’s pretty straightforward to set up a default dock for new users. However, most of our students lack familiarity or comfort with Macs, so if a student accidentally drags an important icon (like, say, Microsoft Word) out of the dock, they may have difficulty finding the application again. Under these circumstances, it’s desirable to “reset” the dock to a known good state every time a user logs out so that any mistakes they made will be wiped away.
  • Users not logging out. This is a big one. If a user fails to log out properly and ends up with their whole session still stored in memory in the background, system performance plummets for anyone else who logs into that computer. This is a huge problem in a lab setting because it’s difficult to train users to always properly log out, and each computer is logged into so many times every day that it only takes a couple days for most computers to have at least one user still accidentally logged in in the background.

One solution to these kinds of issues is to have a very tightly controlled computer lab where no user data is stored and where the computers constantly restart and reset themselves to known good states. But I’m not comfortable doing that; I want students to be able to log back into a computer to retrieve a file if they accidentally saved it to the lab computer instead of their personal flash drive, for example.

So instead, I wrote a logoff script to do some basic maintenance to help me solve these issues. Macs don’t provide an easy built-in way to run logoff scripts, so I use Offset to run the script for me.

My script does the following:

  1. Delete the user’s keychain every time they log out (step 1 of script). This prevents keychain errors and improves security, since it ensures we aren’t storing people’s passwords on our public computers.
  2. Replaces the user’s dock with a known good configuration (step 3 of script). That way, if the user accidentally messed up the dock, it will be back to normal the next time they log in. Yes, this does mean that users cannot customize their docks– but in a lab setting, what a student customizes on one computer won’t carry over to another computer anyway, so why give them the illusion of choice? Better to keep things standardized.
  3. Kicks off anyone who’s logged into the computer in the background (step 5 of script). This dramatically improves system performance.
  4. Restarts the computer if it’s been on for more than a day (step 5 of script). This is a necessary step to fully purge keychain files.

Here is the script I created. I am not a bash expert or a MacOS expert, and I make no warranty for the functionality or safety of this script; I’m presenting it only for example purposes to help others in similar positions. If you want to adapt this script, you will likely need to make changes.

echo "$(date) - Script execution beginning" >> /Library/Logs/lab_logout_debug.log

### Step 1: Delete the last user's keychain; we always want to do this upon logout no matter what
# Credit to
echo "$(date) - Entering step 1" >> /Library/Logs/lab_logout_debug.log
# Get the last user's username
lastUserName=$(defaults read /Library/Preferences/ lastUserName)
# Remove the keychains for that user
sudo rm -rf /Users/"$lastUserName"/Library/Keychains/*
sudo rm -rf /Users/"$lastUserName"/Library/Keychains/.f*
# Add to log file
sudo echo "$(date) - Keychain deleted for $lastUserName" >> /Library/Logs/lab_logout.log

### Step 2: Decide whether we need to run the rest of the script.
#           If we're at the login window, and if the script has not run
#           in the last 120 seconds, then we want to run it now.
#           Note: Checking when the script last ran prevents the script from 
#           killing the login window over and over and over again in a loop!
#           I chose 120 seconds because it would be unusual for someone to be
#           logged in for less than 2 minutes.

echo "$(date) - Entering step 2" >> /Library/Logs/lab_logout_debug.log
echo "$(date) - initializing shouldrun to false" >> /Library/Logs/lab_logout_debug.log
echo "$(date) - trying to find the last time run log file" >> /Library/Logs/lab_logout_debug.log
# Can we find the log file that stores the last time the script was run?
if [ -f /Library/Logs/lab_logout_last_time.log ]
    # We found the file!
    echo "$(date) - found last time run log file" >> /Library/Logs/lab_logout_debug.log
    # Store the current Unix time in seconds
    currenttime=$(date +%s)
    # Read the Unix time in seconds that the script last ran
    lasttime=$(cat /Library/Logs/lab_logout_last_time.log)
    # Calculate the difference between the times: How long ago did the script last run?
    delta=$(expr $currenttime - $lasttime)
    echo "$(date) - Current time: " $currenttime >> /Library/Logs/lab_logout_debug.log
    echo "$(date) - Last time: " $lasttime >> /Library/Logs/lab_logout_debug.log
    echo "$(date) - Delta: " $delta >> /Library/Logs/lab_logout_debug.log
    # Did the script last run MORE than 120 seconds ago?
    if [ "$delta" -gt 120 ]
        # Yes? We should run the rest of this script.
        echo "$(date) - set shouldrun to true (delta large enough)" >> /Library/Logs/lab_logout_debug.log
        # No? We should not run the rest of this script.
        echo "$(date) - keep shouldrun at false (delta too small)" >> /Library/Logs/lab_logout_debug.log
    # We didn't find the log file that stores the last time the script was run, so let's assume we need to run the script.
    echo "$(date) - didn't find the last time run file" >> /Library/Logs/lab_logout_debug.log
    echo "$(date) - set shouldrun to true (file not found)" >> /Library/Logs/lab_logout_debug.log
# Should we run the script?
if [ "$shouldrun" = "true" ]
    # Yes, we should run the script!
    echo "$(date) - shouldrun evaluated as true - executing remainder of script" >> /Library/Logs/lab_logout_debug.log
    echo "$(date) - Executing lab logout script." >> /Library/Logs/lab_logout.log
    ### Step 3: Copy the dock for the user
    echo "$(date) - Entering step 3" >> /Library/Logs/lab_logout_debug.log
    cp /Library/lab/ /Users/$lastUserName/Library/Preferences/
    echo "$(date) - Dock copied for this user:" $lastUserName >> /Library/Logs/lab_logout.log
    ### Step 4: Record the current time as the last time the script ran
    echo "$(date) - Entering step 4" >> /Library/Logs/lab_logout_debug.log
    # Record the current time to the lab_logout_last_time file, overwriting any previous file content
    echo $(date +%s) > /Library/Logs/lab_logout_last_time.log
    echo "$(date) - Current time recorded" >> /Library/Logs/lab_logout_debug.log

    ### Step 5: If the computer has been up for at least 24 hours, then restart;
    #           otherwise, just kill loginwindow

    echo "$(date) - Entering step 5" >> /Library/Logs/lab_logout_debug.log
    echo "$(date) - Current uptime: $(uptime)" >> /Library/Logs/lab_logout_debug.log
    # Has the computer been on for at least one full day? 
    # (i.e. does the word "day" or "days" appear in the output of the "uptime" terminal command?)
    if [[ $(uptime) =~ .*day.* ]] 
        # Uptime is at least one day, so let's force a restart. This will fully clear keychains and (obviously) log out all users.
        echo "$(date) - Uptime greater than a day; attempting a restart" >> /Library/Logs/lab_logout.log
        sudo shutdown -r now
        # Uptime is less than one day, so let's just kill the loginwindow process to make sure no one's
        # logged in in the background.
        echo "$(date) - Uptime less than a day; killing loginwindow." >> /Library/Logs/lab_logout.log
        # Kill loginwindow to log out any users who are logged in in the background
        sudo pkill loginwindow

    # No, we shouldn't run the script!
    echo "$(date) - shouldrun DID NOT evaluate as true, so script did not run" >> /Library/Logs/lab_logout_debug.log

Basic installation instructions:

  1. Install Offset.
  2. Paste my script into a new file with a .sh extension. (the specific filename I personally use is “”)
  3. Make any necessary changes to the script. Note that it won’t run as-is; at the very least, you need to put a dock.plist file in the /Library/lab/ directory for step 3 of the script, or else remove that part of the script.
  4. Move the script file to /usr/local/offset/logout-every so that Offset will run it for you.
  5. Make the script file executable. I run this command in Terminal, and it seems to work, but I’m not an expert, so ymmv: sudo chmod 755 /usr/local/offset/logout-every/ ; sudo chown root:wheel /usr/local/offset/logout-every/

That should do it!