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:
- Enter the scoring key for the exam in row 4.
- Enter any subscore categories you want to calculate in row 39 (optional).
- Enter students’ names in column A (optional).
- 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:
- Enter the instructor’s own name (A1) and the name of the activity (B1).
- Enter the rubric categories (C1-H1) and how many points the categories are worth (C2-H2).
- Enter students’ names and email addresses (columns A and B).
- For each student, enter their subscores according to the rubric and type any additional comments you have (columns C-I).
- Click the “Create Email” link next to one of the students (column J)
- 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.
An exercise on 